t-sql Table columns to String

Question

Is it possible to string a table columns headers dynamically from information_schema recursively

So if I had 2 tables (or however many) in my database with 5 columns each

Could I get the query to find this tables by schema and then string all the table columns hearders into another table ending up with something like

table name ¦ string 
Table A    ¦ id,columnName1,columnName2 ,columnName3 ,columnName4 ,columnName5
Table b    ¦ id,columnName1,columnName2 ,columnName3 ,columnName4 ,columnName5

(THESE ARE TWO ROWS BTW)

Answer 1

It can be simpler, no XML:

declare @columns varchar(1000)
set @columns=''

select @columns = @columns + ',' + name
from sys.columns 
where object_id = object_id('MyTable')

Answer 2

Something using FOR XML PATH('') in combination with the sys.columns and sys.tables could work:

SELECT t.name [TableName], 
(
    SELECT name + ','
    FROM sys.columns c
    WHERE c.object_id = t.object_id
    FOR XML PATH('')
) [String]
FROM sys.tables t

It basically takes the columns returned from a query and formats is as XML, where the element tags are defined in the PATH(''). If you leave it blank, though, it will omit the tags and return a string instead.

The documentation on MSDN also has a lot of different examples with other uses for the FOR XML clause.