SQL Server 2005 查詢資料表結構完整 SQL 語句（含主鍵、身分識別、預設值、說明）

查詢目的

單一查詢取得資料表完整結構定義：欄位順序、名稱、型別、長度、精度、小數位、是否允許 NULL、是否身分識別、是否主鍵、預設值、欄位說明（擴充屬性）。適用於文件產生、程式碼產生器、異動比對。

完整查詢語句

SELECT
    (CASE WHEN a.colorder = 1 THEN d.name ELSE '' END) AS 表名,
    a.colorder AS 欄位序號,
    a.name AS 欄位名,
    (CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END) AS 識別欄,
    (CASE WHEN (
        SELECT COUNT(*) FROM sysobjects
        WHERE name IN (
            SELECT name FROM sysindexes
            WHERE id = a.id AND indid IN (
                SELECT indid FROM sysindexkeys
                WHERE id = a.id AND colid IN (
                    SELECT colid FROM syscolumns WHERE id = a.id AND name = a.name
                )
            )
        ) AND xtype = 'PK'
    ) > 0 THEN '√' ELSE '' END) AS 主鍵,
    b.name AS 型別,
    a.length AS 佔用位元組,
    COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 長度,
    ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小數位數,
    (CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END) AS 允許空值,
    ISNULL(e.text, '') AS 預設值,
    ISNULL(g.[value], '') AS 欄位說明
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype = b.xusertype
INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties'
LEFT JOIN syscomments e ON a.cdefault = e.id
LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id AND g.class = 1
WHERE d.name = 'systempara'  -- 此處替換目標資料表名稱
ORDER BY a.id, a.colorder;

關鍵系統表與函數說明

現代化寫法建議 (SQL Server 2005+ 通用)

SELECT
    c.name AS 欄位名,
    t.name AS 型別,
    c.max_length AS 最大長度,
    c.precision AS 精度,
    c.scale AS 小數位數,
    c.is_nullable AS 允許NULL,
    c.is_identity AS 識別欄,
    ic.seed_value AS 識別種子,
    ic.increment_value AS 識別增量,
    dc.definition AS 預設值,
    ep.value AS 欄位說明,
    CASE WHEN pk.column_id IS NOT NULL THEN '√' ELSE '' END AS 主鍵
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
JOIN sys.tables tb ON c.object_id = tb.object_id
LEFT JOIN sys.identity_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_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.class = 1
LEFT JOIN (
    SELECT ic.column_id
    FROM sys.indexes i
    JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    WHERE i.is_primary_key = 1
) pk ON c.column_id = pk.column_id
WHERE tb.name = 'systempara'
ORDER BY c.column_id;

優點：使用新版目錄檢視（sys.tables、sys.columns、sys.indexes），語意更清晰、效能更好、支援新資料型別（max、xml 等）。

應用場景

• 自動產生資料字典文件（Word/Excel/Markdown）。
• 程式碼產生器（Entity Class、DTO、Mapping 配置）。
• 跨環境結構比對（開發/測試/生產）。
• 異動審計觸發器記錄結構變更歷史。