How to Extract Table Metadata (Description and Field Information) in SQL Server?

Extracting Table Meta-Data in SQL Server

In SQL Server, extracting vital information about database tables is crucial for various administrative and development tasks. This involves retrieving details such as table descriptions, field names, and corresponding data types.

Table Description and Field Information Retrieval

To fulfill this need, a combination of system tables and extended properties must be employed. The following query utilizes the sysobjects, sysusers, syscolumns, and sys.extended_properties tables to fetch the table description, field names, and column descriptions:

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

This query retrieves the following information for each table:

• Full table name ([table])
• Table description ([table_desc])
• Field name ([column])
• Field description ([column_desc])

By executing this query, you can obtain a comprehensive overview of your SQL Server database tables, including their descriptions and field characteristics. This information is invaluable for managing, documenting, and validating database structures.

The above is the detailed content of How to Extract Table Metadata (Description and Field Information) in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!