SQL Server 擴展屬性完整指南：添加、查詢、修改、刪除表/列描述元數據

擴展屬性 (MS_Description) 是 SQL Server 官方推薦的**數據字典**存儲機制，將業務含義直接掛在表/列/索引等對象上，隨庫遷移、版本控制、工具讀取零成本。

核心系統存儲過程

標準調用範例：為表列添加描述

-- 添加列描述
EXEC sys.sp_addextendedproperty 
    @name = N'MS_Description',
    @value = N'客戶郵箱地址，唯一索引',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE',  @level1name = N'Customers',
    @level2type = N'COLUMN', @level2name = N'Email';
GO

-- 修改描述
EXEC sys.sp_updateextendedproperty 
    @name = N'MS_Description',
    @value = N'客戶郵箱，登錄帳號，唯一',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE',  @level1name = N'Customers',
    @level2type = N'COLUMN', @level2name = N'Email';
GO

-- 刪除描述
EXEC sys.sp_dropextendedproperty 
    @name = N'MS_Description',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE',  @level1name = N'Customers',
    @level2type = N'COLUMN', @level2name = N'Email';
GO

查詢擴展屬性：三種方式

1. 官方函數 fn_listextendedproperty (舊版相容)

SELECT * 
FROM fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'TABLE', 'Customers', 'COLUMN', DEFAULT)
WHERE objname = 'Email';

2. 系統視圖 sys.extended_properties (推薦，集合查詢)

SELECT 
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    o.name AS TableName,
    c.name AS ColumnName,
    ep.value AS Description
FROM sys.extended_properties ep
INNER JOIN sys.objects o ON ep.major_id = o.object_id
INNER JOIN sys.columns c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
WHERE ep.class = 1  -- 列級
  AND ep.name = 'MS_Description'
ORDER BY SchemaName, TableName, ColumnName;

3. 一鍵生成全庫數據字典 (含表/列描述)

SELECT 
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    t.type_desc AS TableType,
    ISNULL(tep.value, '') AS TableDescription,
    c.column_id AS ColOrder,
    c.name AS ColumnName,
    ty.name AS DataType,
    c.max_length AS MaxLen,
    c.is_nullable AS Nullable,
    c.is_identity AS Identity,
    ISNULL(cep.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 tep 
    ON tep.major_id = t.object_id AND tep.minor_id = 0 AND tep.class = 1 AND tep.name = 'MS_Description'
LEFT JOIN sys.extended_properties cep 
    ON cep.major_id = c.object_id AND cep.minor_id = c.column_id AND cep.class = 1 AND cep.name = 'MS_Description'
WHERE t.is_ms_shipped = 0
ORDER BY SchemaName, TableName, ColOrder;

層級體系說明

最多 3 層 (0/1/2)，覆蓋所有主要對象。

最佳實踐建議

1. 開發規範：建表/加列**同步編寫擴展屬性**，納入 Code Review 清單。
2. 統一名稱：嚴格使用 MS_Description，工具 (SSMS/Redgate/Dataedo/自研) 均識別此名。
3. 版本控制：將擴展屬性腳本化納入遷移腳本，隨 Schema 版本演進。
4. 權限控制：僅 DBA/架構師可寫入，開發人員只讀。
5. 多語言：值可存 JSON {"zh-CN":"...","en-US":"..."}，前端按語系解析。