SQL Server：提取表元数据（描述、字段及其数据类型）

SQL Server: Extract Table Meta-Data (description, fields and their data types)

提问by Andreas Grech

I am trying to find a way to extract information about my tables in SQL Server (2008).
The data I need needs to include the description of the table(filled from the Description property in the Properties Window), a list of fieldsof that table and their respective data types.

我试图找到一种方法来提取有关我在 SQL Server (2008) 中的表的信息。
我需要的数据包括表的描述（从属性窗口中的描述属性填充）、该表的字段列表及其各自的数据类型。

Is there any way I can extract such meta-data? I presume I have to use some syssp but I'n not sure which one.

有什么办法可以提取这样的元数据吗？我想我必须使用一些syssp 但我不确定是哪一个。

回答by Marc Gravell

To get the description data, you unfortunately have to use sysobjects/syscolumns to get the ids:

不幸的是，要获取描述数据，您必须使用 sysobjects/syscolumns 来获取 ID：

SELECT      u.name + '.' + t.name AS [table],
            td.value AS [table_desc],
            c.name AS [column],
            cd.value AS [column_desc]
FROM        sysobjects t
INNER JOIN  sysusers u
    ON      u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON      td.major_id = t.id
    AND     td.minor_id = 0
    AND     td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON      c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON      cd.major_id = c.id
    AND     cd.minor_id = c.colid
    AND     cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY    t.name, c.colorder

You can do it with info-schema, but you'd have to concatenate etc to call OBJECT_ID() - so what would be the point?

你可以用 info-schema 来做，但你必须连接等来调用 OBJECT_ID() - 那么有什么意义呢？

回答by Andomar

Generic information about tables and columns can be found in these tables:

可以在这些表中找到有关表和列的一般信息：

select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.COLUMNS

The table description is an extended property, you can query them from sys.extended_properties:

表描述是一个扩展属性，你可以从 sys.extended_properties 查询它们：

select 
    TableName = tbl.table_schema + '.' + tbl.table_name, 
    TableDescription = prop.value,
    ColumnName = col.column_name, 
    ColumnDataType = col.data_type
FROM information_schema.tables tbl
INNER JOIN information_schema.columns col 
    ON col.table_name = tbl.table_name
    AND col.table_schema = tbl.table_schema
LEFT JOIN sys.extended_properties prop 
    ON prop.major_id = object_id(tbl.table_schema + '.' + tbl.table_name) 
    AND prop.minor_id = 0
    AND prop.name = 'MS_Description' 
WHERE tbl.table_type = 'base table'

回答by Brandon Montgomery

You could try sp_help <Name of object>

你可以试试 sp_help <Name of object>

回答by Tobias J

I liked @Andomar's answer best, but I needed the column descriptions also. Here is his query modified to include those also. (Uncomment the last part of the WHERE clause to return only rows where either description is non-null).

我最喜欢@Andomar 的回答，但我也需要列描述。这是他的查询修改后也包括那些。（取消注释 WHERE 子句的最后一部分以仅返回其中任一描述为非空的行）。

SELECT
    TableName = tbl.table_schema + '.' + tbl.table_name, 
    TableDescription = tableProp.value,
    ColumnName = col.column_name, 
    ColumnDataType = col.data_type,
    ColumnDescription = colDesc.ColumnDescription
FROM information_schema.tables tbl
INNER JOIN information_schema.columns col 
    ON col.table_name = tbl.table_name
LEFT JOIN sys.extended_properties tableProp 
    ON tableProp.major_id = object_id(tbl.table_schema + '.' + tbl.table_name) 
        AND tableProp.minor_id = 0
        AND tableProp.name = 'MS_Description' 
LEFT JOIN (
    SELECT sc.object_id, sc.column_id, sc.name, colProp.[value] AS ColumnDescription
    FROM sys.columns sc
    INNER JOIN sys.extended_properties colProp
        ON colProp.major_id = sc.object_id
            AND colProp.minor_id = sc.column_id
            AND colProp.name = 'MS_Description' 
) colDesc
    ON colDesc.object_id = object_id(tbl.table_schema + '.' + tbl.table_name)
        AND colDesc.name = col.COLUMN_NAME
WHERE tbl.table_type = 'base table'
--AND tableProp.[value] IS NOT NULL OR colDesc.ColumnDescription IS NOT null

回答by MarlonRibunal

Using Object Catalog Views:

使用对象目录视图：

SELECT  T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE],   CAST(P.PRECISION AS VARCHAR) +‘/'+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
FROM ADVENTUREWORKS.SYS.OBJECTS AS T
JOIN ADVENTUREWORKS.SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN ADVENTUREWORKS.SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC=‘USER_TABLE';

Using Information Schema Views

使用信息架构视图

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
       COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
       DATETIME_PRECISION
FROM ADVENTUREWORKS.INFORMATION_SCHEMA.COLUMNS

回答by Jeremy Thompson

Check this out:

看一下这个：

SELECT TABLE_SCHEMA ,
       TABLE_NAME ,
       COLUMN_NAME ,
       ORDINAL_POSITION ,
       COLUMN_DEFAULT ,
       DATA_TYPE ,
       CHARACTER_MAXIMUM_LENGTH ,
       NUMERIC_PRECISION ,
       NUMERIC_PRECISION_RADIX ,
       NUMERIC_SCALE ,
       DATETIME_PRECISION
FROM   INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA in ('dbo','meta')
and table_name in (select name from sys.tables)
order by TABLE_SCHEMA ,       TABLE_NAME ,ORDINAL_POSITION

回答by Charlie Affumigato

Depending on how much metadata you want, this is working for me: where could be the whole thing like Northwind.dbo.Products or just Products

取决于你想要多少元数据，这对我有用：像 Northwind.dbo.Products 或只是 Products 这样的整个东西在哪里

SELECT c.name Field, 
    t.name Type,
    c.Precision, 
    c.Scale,
    c.is_nullable,
    c.collation_name 
FROM sys.columns c 
INNER JOIN sys.types t ON t.system_type_id=c.system_type_id
WHERE object_id=object_id('<table to inspect>')
ORDER BY column_id

回答by Dave W

If you are pulling your queries using java code, there is a great class that can be used, ResultSetMetaData, that can retrieve column names and the properties of the columns (type and length).

如果您使用 Java 代码提取查询，则可以使用一个很棒的类 ResultSetMetaData，它可以检索列名和列的属性（类型和长度）。

Example

例子

ResultSet rs = null;

        rs = sql.executeQuery();

        if (rs != null) {
            if (rs.next()) {
                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    System.out.println("column name: "
                            + rsmd.getColumnName(i));
                    System.out.println("column size: "
                            + rsmd.getColumnDisplaySize(i));
                }
            }

回答by Kumait

I use this SQL code to get all the information about a column.

我使用此 SQL 代码来获取有关列的所有信息。

SELECT
COL.COLUMN_NAME,
ORDINAL_POSITION,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX,
NUMERIC_SCALE,
DATETIME_PRECISION,
IS_NULLABLE,
CONSTRAINT_TYPE,
COLUMNPROPERTY(object_id(COL.TABLE_NAME), COL.COLUMN_NAME, 'IsIdentity') IS_IDENTITY,
COLUMNPROPERTY(object_id(COL.TABLE_NAME), COL.COLUMN_NAME, 'IsComputed') IS_COMPUTED

FROM INFORMATION_SCHEMA.COLUMNS COL 
LEFT OUTER JOIN 
(
    SELECT COLUMN_NAME, CONSTRAINT_TYPE 
    FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE A
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B 
    ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
    WHERE A.TABLE_NAME = 'User'
) CONS
ON COL.COLUMN_NAME = CONS.COLUMN_NAME
WHERE COL.TABLE_NAME = 'User'

回答by DarrenW

Easiest way to get basic metadata summary is to use a temp table and then use EXEC function:

获取基本元数据摘要的最简单方法是使用临时表，然后使用 EXEC 函数：

SELECT * INTO #TempTable FROM TableName
EXEC [tempdb].[dbo].[sp_help] N'#TempTable'

For all columns in the table, this will give you Column Name,
Data Type,
Computed Length,
Prec,
Scale, Nullable,
TrimTrailingBlanks, FixedLenNullInSource, Collation Type

对于表中的所有列，这将为您提供列名、
数据类型、
计算长度、
Prec、
Scale、Nullable、
TrimTrailingBlanks、FixedLenNullInSource、Collat​​ion Type