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.

Answer 3

Using sys.objects more effective that sys.tables. Possible this be helpful for you -

SELECT 
      table_name = s.name + '.' + o.name
    , [columns] = STUFF((
        SELECT ', ' + c.name
        FROM sys.columns c WITH (NOWAIT)
        WHERE c.[object_id] = o.[object_id]
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
FROM (
    SELECT 
          o.[object_id]
        , o.name
        , o.[schema_id]
    FROM sys.objects o WITH (NOWAIT)
    WHERE o.[type] = 'U'
        AND o.is_ms_shipped = 0
) o
INNER JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id] 
ORDER BY 
      s.name
    , o.name

Answer 4

This will do it (although there may be another way):

;with cte as
(
    Select  TableName = t.TABLE_NAME
            ,ColumnList = Substring((Select ', ' + c2.COLUMN_NAME
                                    From    INFORMATION_SCHEMA.COLUMNS c2
                                    Where   c.TABLE_NAME = c2.TABLE_NAME
                                    For XML Path('')
                                    ), 3, 8000)
            ,ROW_NUMBER() OVER(Partition By t.TABLE_NAME Order By t.TABLE_NAME) RowNumber
    From    INFORMATION_SCHEMA.TABLES t
    join    INFORMATION_SCHEMA.COLUMNS c
        on  t.TABLE_NAME = c.TABLE_NAME
)
Select  TableName
        ,ColumnList
From    cte
Where   RowNumber = 1

Answer 5

I think you want use this Query for making class files of all the tables in your DB. Hence the answer

if object_id('tempdb..#t') is not null
    drop table #t

SELECT 
T.name Table_name
,ltrim(rtrim(C.name)) Column_name
,TYPE1.name AS [type_name]
,C.max_length length1
into #t
FROM sys.tables T WITH(NOLOCK)
INNER JOIN sys.columns C WITH(NOLOCK) ON T.OBJECT_ID=C.OBJECT_ID
INNER JOIN sys.types AS TYPE1 with(nolock) ON c.user_type_id=TYPE1.user_type_id
ORDER BY T.name

SELECT 
  #t.Table_name,
  STUFF((
    SELECT ', ' + t2.Column_name+' as ' +t2.type_name + '('+ cast(t2.length1 as varchar) + ')'
    FROM #t t2
    WHERE t2.Table_name = #t.Table_name
    FOR XML PATH (''))
,1,2,'') AS Names
FROM #t
GROUP BY #t.Table_name;

Specific answer to your Question

if object_id('tempdb..#t') is not null
    drop table #t

SELECT 
T.name Table_name
,ltrim(rtrim(C.name)) Column_name
into #t
FROM sys.tables T WITH(NOLOCK)
INNER JOIN sys.columns C WITH(NOLOCK) ON T.OBJECT_ID=C.OBJECT_ID
where t.name in ('[Table A]','[Table B]')
ORDER BY T.name

SELECT 
  #t.Table_name,
  STUFF((
    SELECT ', ' + t2.Column_name
    FROM #t t2
    WHERE t2.Table_name = #t.Table_name
    FOR XML PATH (''))
,1,2,'') AS Names
FROM #t
GROUP BY #t.Table_name;