SQL Server 資料字典一鍵生成：經典系統表聯查腳本深度解析

核心查詢腳本 (相容 SQL 2000/2005/2008/2012+)

SELECT
    so.name AS [TableName],
    sep.value AS [TableDescription],
    sc.name AS [ColumnName],
    cep.value AS [ColumnDescription],
    st.name AS [DataType],
    sc.length AS [Length],
    ISNULL(COLUMNPROPERTY(sc.id, sc.name, 'Scale'), 0) AS [DecimalPlaces],
    sc.isnullable AS [AllowNull],
    CASE WHEN scom.text IS NULL THEN '' ELSE scom.text END AS [DefaultValue],
    CASE WHEN COLUMNPROPERTY(sc.id, sc.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS [IsIdentity],
    CASE WHEN EXISTS (
        SELECT 1 FROM sysobjects so_pk
        WHERE so_pk.xtype = 'PK'
          AND so_pk.name IN (
            SELECT si.name FROM sysindexes si
            JOIN sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid
            WHERE sik.id = sc.id AND sik.colid = sc.colid
          )
    ) THEN '√' ELSE '' END AS [IsPrimaryKey]
FROM syscolumns sc
INNER JOIN sysobjects so ON so.id = sc.id
INNER JOIN systypes st ON sc.xtype = st.xtype
LEFT JOIN sysproperties sep ON so.id = sep.id AND sep.smallid = 0
LEFT JOIN sysproperties cep ON sc.id = cep.id AND sc.colid = cep.smallid
LEFT JOIN syscomments scom ON sc.cdefault = scom.id
WHERE so.xtype = 'U'  -- 僅使用者資料表
ORDER BY so.name, sc.colid;

關鍵邏輯解析

進階增強版 (SQL 2005+ 推薦：使用目錄檢視)

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    ep.value AS TableDescription,
    c.name AS ColumnName,
    c.column_id AS ColOrder,
    ty.name AS DataType,
    c.max_length AS MaxLength,
    c.precision, c.scale,
    c.is_nullable AS AllowNull,
    dc.definition AS DefaultDefinition,
    c.is_identity AS IsIdentity,
    CASE WHEN pk.column_id IS NOT NULL THEN '√' END AS IsPrimaryKey,
    ex.value AS ColumnDescription
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
LEFT JOIN sys.extended_properties ep ON t.object_id = ep.major_id AND ep.minor_id = 0 AND ep.name = 'MS_Description'
LEFT JOIN sys.extended_properties ex ON c.object_id = ex.major_id AND c.column_id = ex.minor_id AND ex.name = 'MS_Description'
LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
LEFT JOIN (
    SELECT ic.object_id, ic.column_id
    FROM sys.indexes i
    INNER 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.object_id = pk.object_id AND c.column_id = pk.column_id
WHERE t.is_ms_shipped = 0
ORDER BY SchemaName, TableName, c.column_id;

輸出應用建議

• 另存為 `.csv` → Excel 整理 → 產出專案資料字典文件。
• 整合至自動化文件產生流程 (PowerShell + dbatools / C# + SMO)。
• 定期比對兩環境 (Dev/Prod) 結構差異，作為變更管理依據。