SQL Server 結構中繼資料查詢：資料表清單與欄位詳細屬性

透過系統目錄檢視 (sys.*) 查詢資料庫結構資訊，適用於文件產生、程式碼產生器、異動比對等自動化場景。

1. 使用者資料表清單 (含擁有者)

SELECT
    SCHEMA_NAME(o.schema_id) AS schema_name,
    o.name AS table_name,
    o.object_id,
    o.create_date,
    o.modify_date
FROM sys.objects o
WHERE o.type = 'U'  -- User Table
ORDER BY schema_name, table_name

2. 欄位詳細屬性 (含身分識別、預設值、允許 NULL、擴充屬性)

SELECT
    c.column_id AS colid,
    c.name AS column_name,
    t.name AS type_name,
    c.max_length AS length,
    c.precision AS xprec,
    c.scale AS xscale,
    c.is_nullable AS is_null,
    c.is_identity AS is_identity,
    c.is_computed AS is_computed,
    dc.definition AS default_definition,
    ep.value AS description  -- 擴充屬性 MS_Description
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
LEFT JOIN sys.extended_properties ep
    ON ep.major_id = c.object_id
    AND ep.minor_id = c.column_id
    AND ep.name = 'MS_Description'
WHERE c.object_id = OBJECT_ID('dbo.YourTable')
ORDER BY c.column_id

關鍵欄位說明

3. 索引與外鍵查詢範例

-- 索引
SELECT i.name, i.type_desc, i.is_unique, i.is_primary_key,
       STUFF((SELECT ', ' + c.name
              FROM sys.index_columns ic
              JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
              WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
              ORDER BY ic.key_ordinal
              FOR XML PATH('')), 1, 2, '') AS key_columns
FROM sys.indexes i
WHERE i.object_id = OBJECT_ID('dbo.YourTable') AND i.type > 0

-- 外鍵
SELECT fk.name AS fk_name,
       OBJECT_NAME(fk.parent_object_id) AS parent_table,
       COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS parent_column,
       OBJECT_NAME(fk.referenced_object_id) AS ref_table,
       COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ref_column
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE fk.parent_object_id = OBJECT_ID('dbo.YourTable')
   OR fk.referenced_object_id = OBJECT_ID('dbo.YourTable')

建議統一使用 sys.* 目錄檢視，取代舊版 sysobjects/syscolumns 系統表，相容性與效能更佳。