SQL Server 2008 獲取表欄位屬性與擴充屬性說明完整查詢腳本

開發文件產生、代碼生成器、資料字典工具常需查詢表結構詳細資訊。本文提供兩個實用查詢：簡易版 (單表單欄位) 與完整版 (資料字典標準輸出)。

簡易版：查詢單一欄位的擴充屬性 (MS_Description)

SELECT ep.value AS ColumnDescription
FROM sys.columns c
LEFT JOIN sys.extended_properties ep
    ON c.object_id = ep.major_id
   AND c.column_id = ep.minor_id
   AND ep.name = 'MS_Description'  -- 標準說明屬性名
INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.name = 'TableName' AND c.name = 'ColumnName';

完整版：標準資料字典查詢 (含所有屬性)

SELECT
    TableName = CASE WHEN c.column_id = 1 THEN OBJECT_SCHEMA_NAME(c.object_id) + '.' + OBJECT_NAME(c.object_id) ELSE '' END,
    TableDescription = CASE WHEN c.column_id = 1 THEN ISNULL(tep.value, '') ELSE '' END,
    ColumnOrder = c.column_id,
    ColumnName = c.name,
    IsIdentity = CASE WHEN COLUMNPROPERTY(c.object_id, c.name, 'IsIdentity') = 1 THEN '✓' ELSE '' END,
    IsPrimaryKey = CASE WHEN EXISTS (
        SELECT 1 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 AND ic.object_id = c.object_id AND ic.column_id = c.column_id
    ) THEN '✓' ELSE '' END,
    DataType = t.name,
    MaxLength = c.max_length,
    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 '✓' ELSE '' END,
    DefaultValue = ISNULL(dc.definition, ''),
    ColumnDescription = ISNULL(cep.value, '')
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
JOIN sys.objects o ON c.object_id = o.object_id AND o.type = 'U' AND o.name <> 'dtproperties'
LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
LEFT JOIN sys.extended_properties cep ON c.object_id = cep.major_id AND c.column_id = cep.minor_id AND cep.name = 'MS_Description'
LEFT JOIN sys.extended_properties tep ON o.object_id = tep.major_id AND tep.minor_id = 0 AND tep.name = 'MS_Description'
-- WHERE o.name = 'YourTableName'  -- 取消註解查詢單表
ORDER BY o.object_id, c.column_id;

輸出欄位說明

擴充屬性管理腳本

-- 新增/更新表說明
EXEC sys.sp_addextendedproperty 'MS_Description', '訂單主檔', 'SCHEMA', 'dbo', 'TABLE', 'Orders';
-- 或更新
EXEC sys.sp_updateextendedproperty 'MS_Description', '訂單主檔 v2', 'SCHEMA', 'dbo', 'TABLE', 'Orders';

-- 新增/更新欄位說明
EXEC sys.sp_addextendedproperty 'MS_Description', '訂單狀態: 0=新單,1=處理中,2=完成', 'SCHEMA', 'dbo', 'TABLE', 'Orders', 'COLUMN', 'Status';

-- 刪除屬性
EXEC sys.sp_dropextendedproperty 'MS_Description', 'SCHEMA', 'dbo', 'TABLE', 'Orders', 'COLUMN', 'Status';

-- 批次產生所有表欄位的 sp_addextendedproperty 腳本 (供文件導入)
SELECT 'EXEC sys.sp_addextendedproperty N''MS_Description'', N''' + REPLACE(ISNULL(cep.value,''), '''', '''''') + ''', N''SCHEMA'', N''' + SCHEMA_NAME(o.schema_id) + ''', N''TABLE'', N''' + o.name + ''', N''COLUMN'', N''' + c.name + ''';'
FROM sys.columns c
JOIN sys.objects o ON c.object_id = o.object_id AND o.type = 'U'
LEFT JOIN sys.extended_properties cep ON c.object_id = cep.major_id AND c.column_id = cep.minor_id AND cep.name = 'MS_Description'
WHERE cep.value IS NOT NULL;

現代工具替代方案

• SQL Server Management Studio： 右鍵資料表 → 「產生腳本」→ 選擇「擴充屬性」→ 匯出含說明的 CREATE 腳本。
• dbForge Documenter / ApexSQL Doc / Dataedo： 專業資料字典自動化文件工具。
• SQL Server Data Tools (SSDT)： 專案導入資料庫，欄位說明可在模型中編輯並部署。

掌握系統目錄檢視與擴充屬性查詢，即可建立自動化資料字典管道，確保文件與資料庫結構同步。