CODEWITHSUNDEEP
sqlsql-server
AI52487963
AI52487963

Reputation: 1273

All tables and all columns in SQL?

I'm looking to list each table in my database and the corresponding column headers with it. Is there a way for SQL to do this and alphabetize the column names in the process?

Something along the formatting lines of:

Table1    Col1name    Col2name    Col3name ...
Table2    Col1name    Col2name    Col3name ...
...

Thanks!

Upvotes: 2

Views: 3961

Answers (5)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

SELECT 
  [schema] = s.name,
  [table]  = t.name, 
  [column] = c.name
FROM 
  sys.tables AS t
INNER JOIN
  sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
INNER JOIN 
  sys.columns AS c
  ON t.[object_id] = c.[object_id]
ORDER BY 
  t.name, 
  c.name;

And here is why I wouldn't use INFORMATION_SCHEMA for anything in SQL Server 2005+:

Upvotes: 5

Frederic
Frederic

Reputation: 1028

doing a pivot trick...?

    declare @max_cols int
    declare @i int
    declare @query as varchar(max)
    declare @query2 as varchar(max)

    select @max_cols = MAX(n)
    from(   select COUNT(COLUMN_NAME) as n
        from INFORMATION_SCHEMA.COLUMNS
        group by table_name
    )V


    select @i = 1, @query2 = ''
    while @i <= @max_cols
    begin
        set @query2 = @query2 +', Col_'+CAST(@i as varchar(max))
        set @i = @i+1
    end
    select @query2 = STUFF(@query2,1,1,'')

    select @query =
    'select * 
    from (
            select T.TABLE_NAME,T.COLUMN_NAME,''Col_''+cast(T.ORDINAL_POSITION as varchar(max)) as pos
            from INFORMATION_SCHEMA.COLUMNS T
    )V
    PIVOT (max(V.COLUMN_NAME) for pos in ('+@query2+'))U'

    exec(@query)

resultset achieved was the same as the xml method

TABLE_NAME              Col_1       Col_2       Col_3
Veixxxxx                cd_exxx     cd_veixxx   nr_apxxxxxx
Verxxxx                 cd_verxxx   de_verxxx   de_muxxxx

Upvotes: 0

Frederic
Frederic

Reputation: 1028

try this 

    declare @xml as xml
    declare @max_cols int
    declare @i int
    declare @query as varchar(max)
    declare @query_OA2 as varchar(max)

    select @max_cols = MAX(n)
    from(   select COUNT(name) as n
            from sys.columns
            group by object_id
    )V


    select @i = 1, @query_OA2 = ''
    while @i <= @max_cols
    begin
        set @query_OA2 = @query_OA2 +', C.elements.value(''./name['+CAST(@i as varchar(max))+']'',''varchar(max)'') as [Column '+CAST(@i as varchar(max))+']'
        set @i = @i+1
    end
    select @query_OA2 = STUFF(@query_OA2,1,1,'')

    select @query = 'select t.name as [table]
                    ,OA2.*
                    from sys.tables t
                    outer apply (select (select (select c.name from sys.columns c
                                         where c.object_id = t.object_id
                                         order by c.column_id
                                         for xml path(''''),type) for xml path(''columns''),type) as cols
                    )OA
                    outer apply (select '+@query_OA2+'
                                 from OA.cols.nodes(''columns'')C(elements)
                    )OA2
                    order by t.name'
    exec (@query)

part of the resultset I achieved

table                   Column 1    Column 2    Column 3
Veixxxxx                cd_exxx     cd_veixxx   nr_apxxxxxx
Verxxxx                 cd_verxxx   de_verxxx   de_muxxxx

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

This should get you going in the right direction. Really depends on how you want your output:

SELECT t.table_name, STUFF((SELECT distinct ',' + c.COLUMN_NAME 
                  FROM DATABASENAME.INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME =t.table_name
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'') as Columns
FROM  DATABASENAME.INFORMATION_SCHEMA.TABLES t 
ORDER BY t.TABLE_NAME, Columns

If using sys.tables and sys.columns as @Aaron points out, then this should work as well:

SELECT t.name, STUFF((SELECT distinct ', ' + c.name
                  FROM sys.COLUMNS c WHERE t.object_id = c.object_id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'') as Columns
FROM sys.TABLES t 
ORDER BY t.name, Columns

Good luck.

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146409

SQL Server has an undocumented Stored procedure called sp_MSforeachtable which you can use to iterate through all the tables in a database

Once you know the tablename, you can use Information schema views or info schema view for Columns to see all the columns in the table

Upvotes: 1

Related Questions