一鍵產出 SQL Server 完整資料字典：欄位、主鍵、型別、長度、說明全包

在查詢分析器執行以下查詢，即可產生所有使用者資料表的完整結構文件，可匯出 Excel 整理：

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 EXISTS (
        SELECT 1 FROM sysobjects so
        JOIN sysindexes si ON so.name = si.name AND so.xtype = 'PK'
        JOIN sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid
        JOIN syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid
        WHERE sc.id = a.id AND sc.colid = a.colid
    ) 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 sysproperties g ON a.id = g.id AND a.colid = g.smallid AND g.name = 'MS_Description'
ORDER BY a.id, a.colorder

輸出欄位說明

進階用法

• 加上 WHERE d.name = '目標表名' 只匯出單一表
• 搭配 sp_helptext 可同時輸出檢視表/預存程序定義
• 結果複製到 Excel，利用篩選與樞紐分析快速掌握資料庫全貌

此查詢相容 SQL Server 2000/2005/2008+，是 DBA 文件化資料庫結構的利器。