Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-02-05 : 12:59:21
|
Dear Experts,Below query lists out all the tables and their respective column names as field list.how can i achieve the same output without using cursor,loops and temp objects.declare @ColName sysname,@TableName sysname,@FieldList varchar(MAX),@LastTableName sysnameselect @LastTableName = '',@FieldList = ''declare @tb table (TableName sysname null, FieldList varchar(MAX) null)declare curs cursor forselect c.name, object_name(c.object_id)from sys.columns c INNER JOIN sys.objects o on c.object_id = o.object_idwhere o.type = 'U'order by o.object_idopen cursfetch curs into @ColName, @TableNameset @LastTableName = @TableNamewhile @@FETCH_STATUS = 0BEGINif @LastTableName<> @TableNameBEGINinsert into @tb values (@LastTableName,@FieldList)set @FieldList = ''set @LastTableName = @TableNameENDset @FieldList = case when @FieldList = '' then @ColName else + @FieldList + ',' + @ColName endfetch curs into @ColName, @TableNameENDdeallocate cursinsert into @tb values (@LastTableName,@FieldList)select * from @tbThanks,Javeed |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-05 : 13:11:43
|
looks like this to meSELECT t.TABLE_NAME,STUFF((SELECT ',' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = t.TABLE_NAME FOR XML PATH('')),1,1,'')FROM INFORMATION_SCHEMA.TABLES tWHERE t.TABLE_TYPE='BASE TABLE' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-02-05 : 13:25:01
|
Visakh,Thanks a lot.quote: Originally posted by visakh16 looks like this to meSELECT t.TABLE_NAME,STUFF((SELECT ',' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = t.TABLE_NAME FOR XML PATH('')),1,1,'')FROM INFORMATION_SCHEMA.TABLES tWHERE t.TABLE_TYPE='BASE TABLE' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-06 : 00:56:33
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|