How Can I Efficiently Extract Table Metadata (Including Descriptions) from SQL Server?

Extracting Table Metadata from SQL Server

In Microsoft SQL Server, retrieving detailed metadata about tables, including descriptions, field names, and data types, can be accomplished through various techniques. This article discusses a method for efficiently extracting such information.

Solution

To extract the comprehensive metadata for tables in SQL Server 2008 and above, you can utilize a combination of system tables and extended properties. The following query provides the required data:

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

• sysobjects (t): This table contains general information about tables.
• sysusers (u): Provides ownership information for tables.
• sys.extended_properties (td): Stores extended properties, including descriptions for tables (for MS_Description) and fields (MS_Description).
• syscolumns (c): Lists table columns.
• sys.extended_properties (cd): Stores extended properties for fields.

The query retrieves the following metadata:

• Table name (concatenation of schema and table name)
• Table description
• Field name
• Field description

Usage

Execute the query in your SQL Server query window to retrieve the metadata for the desired tables. You can modify the query to filter tables based on specific criteria, such as schema or name.

The above is the detailed content of How Can I Efficiently Extract Table Metadata (Including Descriptions) from SQL Server?. For more information, please follow other related articles on the PHP Chinese website!