Multiple records for comma separated list result

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple records for comma separated list result

Author

Topic

Stephen
Starting Member

2 Posts

Posted - 2008-09-30 : 23:41:49

Hi,

I'm trying to generate a list tablenames and the columns in the table without using a cursor.


declare @cols varchar (4000)
declare @table varchar (255)
set @table = 'USERS'
Select @cols = COALESCE(@cols +',','')+ c.name 
from sys.columns c , sys.objects o
where o.type = 'U' and c.object_id = o.object_id
and o.name = @table
order by o.name, column_id
Select @table,@cols

I get the results i want and in the proper format, but for only one table.
Is there a way to make it dynamic enough to return all of the user tables and thier column names(comma separated) in different rows, without using a cursor.

i.e.
col001 | col002
users | userid,username
products | productid,userid,productname

Thanks
Stephen

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 23:49:03

[code]SELECT m.TABLE_NAME,LEFT(cl.collist,LEN(cl.collist)-1)
FROM (SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS) m
CROSS APPLY (SELECT COLUMN_NAME +',' AS [text()]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = m.TABLE_NAME
FOR XML PATH(''))cl(collist)[/code]

Stephen
Starting Member

2 Posts

Posted - 2008-10-01 : 07:57:45

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources