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?

我希望列出数据库中的每个表以及相应的列标题。SQL是否有办法做到这一点并按字母顺序排列过程中的列名?

Something along the formatting lines of:

格式行如下:

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

Thanks!

谢谢!

5 个解决方案

#1

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+:

这就是为什么我不会在SQL Server 2005+中使用INFORMATION_SCHEMA的原因:

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/11/03/the-case-against-information-schema-views.aspx

#2

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

SQL Server有一个名为sp_MSforeachtable的无文档存储过程，您可以使用它来遍历数据库中的所有表。

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

一旦知道了tablename，就可以使用Information schema视图或info schema视图来查看表中的所有列

#3

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:

如果使用系统。表和系统。正如@Aaron所指出的，这也应该奏效:

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.

祝你好运。

#4

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

#5

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

所取得的结果与xml方法相同。

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

#1

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;