How to Extract SQL Server Table Metadata: Descriptions, Fields, and Data Types?

Extracting Table Meta-Data in SQL Server: Description, Fields, and Data Types

Problem Statement:

Database users frequently require detailed information about their SQL Server tables, including table descriptions, field names, and corresponding data types.

Solution:

To extract such meta-data comprehensive, the following query utilizes various system tables and extended properties:

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

Explanation:

• Table Description: The sys.extended_properties table provides 'MS_Description' extended properties, containing table descriptions.
• Field Names and Data Types: The syscolumns system table stores information about table columns, including their names and data types.
• Query Execution: The query retrieves this information and combines it to provide a comprehensive view of table meta-data.

Note:

• To retrieve information-schema, you would need to concatenate and call OBJECT_ID(). It is generally not recommended due to potential complexities.

The above is the detailed content of How to Extract SQL Server Table Metadata: Descriptions, Fields, and Data Types?. For more information, please follow other related articles on the PHP Chinese website!