用 SQL 語句產生 SQL Server 資料字典：欄位定義、型別、說明一鍵匯出

資料字典是資料庫設計文件、開發溝通、審計合規的核心產出。本文提供一條查詢語句，從系統目錄檢視提取所有使用者資料表的完整欄位定義，含名稱、型別、長度、精度、小數位、允許 NULL、預設值、擴充屬性說明，可直接匯出 Excel/Markdown 作為正式文件。

核心查詢 (SQL Server 2005+)

SELECT 
    TableName     = CASE WHEN c.column_id = 1 THEN t.name ELSE '' END,
    ColumnId      = c.column_id,
    ColumnName    = c.name,
    IsIdentity    = CASE WHEN COLUMNPROPERTY(c.object_id, c.name, 'IsIdentity') = 1 THEN N'√' ELSE N'' END,
    IsPrimaryKey  = CASE WHEN EXISTS (
                            SELECT 1 FROM sys.indexes i
                            JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
                            WHERE i.is_primary_key = 1
                              AND ic.object_id = c.object_id
                              AND ic.column_id = c.column_id
                        ) THEN N'√' ELSE N'' END,
    DataType      = ty.name,
    MaxLength     = CASE 
                        WHEN ty.name IN (N'varchar', N'nvarchar', N'varbinary', N'char', N'nchar', N'binary')
                        THEN CASE WHEN c.max_length = -1 THEN N'MAX' 
                                  ELSE CAST(c.max_length / CASE WHEN ty.name LIKE N'n%' THEN 2 ELSE 1 END AS NVARCHAR(10)) 
                             END
                        WHEN ty.name IN (N'text', N'ntext', N'image') THEN N'MAX'
                        ELSE CAST(c.max_length AS NVARCHAR(10))
                      END,
    Precision     = COLUMNPROPERTY(c.object_id, c.name, 'Precision'),
    Scale         = ISNULL(COLUMNPROPERTY(c.object_id, c.name, 'Scale'), 0),
    IsNullable    = CASE WHEN c.is_nullable = 1 THEN N'√' ELSE N'' END,
    DefaultValue  = ISNULL(dc.definition, N''),
    Description   = ISNULL(ep.value, N'')
FROM sys.columns c
INNER JOIN sys.tables t ON t.object_id = c.object_id
INNER JOIN sys.types ty ON ty.user_type_id = c.user_type_id
LEFT JOIN sys.default_constraints dc ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
LEFT JOIN sys.extended_properties ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id AND ep.name = N'MS_Description'
WHERE t.is_ms_shipped = 0
ORDER BY t.name, c.column_id;

欄位說明

• TableName：僅在資料表第一欄顯示名稱，利於 Excel 篩選/分組。
• IsIdentity / IsPrimaryKey：以 √ 標記，直觀易讀。
• MaxLength：自動換算字元長度（Unicode 型別除以 2），MAX 顯示為 MAX。
• Precision/Scale：數值型別的精度與小數位。
• DefaultValue：預設約束定義文字（含括號）。
• Description：擴充屬性 MS_Description，由 sp_addextendedproperty 設定。

加入擴充屬性說明範例

EXEC sys.sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'客戶唯一識別碼，對應 CRM 系統 ClientID', 
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE',  @level1name = N'Customers',
    @level2type = N'COLUMN', @level2name = N'CustomerID';

匯出為 Markdown 表格 (SQLCMD / PowerShell)

# PowerShell 一鍵匯出
$server = 'localhost'; $db = 'MyDB'
$query = Get-Content .\DataDictionary.sql -Raw
Invoke-Sqlcmd -ServerInstance $server -Database $db -Query $query |
Export-Csv -Path .\DataDictionary.csv -NoTypeInformation -Encoding UTF8
# 再用 Excel 開啟 CSV 另存為 .xlsx，或用模組轉 Markdown

進階：含索引、外鍵、檢查約束的完整字典

若需更完整文件，可依序查詢並合併：

1. 欄位定義（上述查詢）
2. 索引定義：sys.indexes, sys.index_columns, INDEXPROPERTY
3. 外鍵關聯：sys.foreign_keys, sys.foreign_key_columns
4. 檢查約束：sys.check_constraints
5. 觸發器：sys.triggers

建議撰寫 PowerShell/Python 腳本一次產生多工作表 Excel，作為專案標準交付物。

自動化建議

• 將查詢封裝為預存程序 sp_GenerateDataDictionary，排程每日跑一次存入文件庫。
• CI/CD 流程中加入步驟：比對當前字典與版控基準，偵測未文件化的結構變更。
• 配合 sp_help, sp_helptext 產生預存程序/函數/視圖定義附錄。

一鍵產出資料字典，讓資料庫結構透明化，是專業 DBA 與開發團隊的基本功。