SQL Server 字段描述 (MS_Description) 完整增刪改查操作指南：擴展屬性實戰

查詢字段描述

SELECT
    o.name AS TableName,
    c.name AS ColumnName,
    p.value AS Description
FROM sys.extended_properties p
INNER JOIN sys.objects o ON o.object_id = p.major_id
INNER JOIN sys.columns c ON c.object_id = p.major_id AND c.column_id = p.minor_id
WHERE p.name = 'MS_Description'
  AND o.name = 'ip_address'
  AND c.name = 'province'
ORDER BY o.name;

⚠️ 舊版 sysproperties 已棄用，統一使用 sys.extended_properties。

添加描述 (sp_addextendedproperty)

-- 表級描述
EXEC sys.sp_addextendedproperty
    @name = N'MS_Description', @value = N'人員信息表',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE',  @level1name = N'TableName';

-- 列級描述
EXEC sys.sp_addextendedproperty
    @name = N'MS_Description', @value = N'姓名',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE',  @level1name = N'TableName',
    @level2type = N'COLUMN', @level2name = N'ColName';

更新描述 (sp_updateextendedproperty)

EXEC sys.sp_updateextendedproperty
    @name = N'MS_Description', @value = N'新描述',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE',  @level1name = N'TableName',
    @level2type = N'COLUMN', @level2name = N'ColName';

刪除描述 (sp_dropextendedproperty)

EXEC sys.sp_dropextendedproperty
    @name = N'MS_Description',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE',  @level1name = N'TableName',
    @level2type = N'COLUMN', @level2name = N'ColName';

通用視圖：一鍵查看所有表列描述

CREATE VIEW dbo.v_ColumnDescriptions AS
SELECT
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    o.name AS TableName,
    c.name AS ColumnName,
    t.name AS DataType,
    c.max_length,
    c.precision,
    c.scale,
    c.is_nullable,
    c.is_identity,
    ep.value AS Description
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT JOIN sys.extended_properties ep
    ON ep.major_id = o.object_id
   AND ep.minor_id = c.column_id
   AND ep.name = 'MS_Description'
   AND ep.class = 1 -- 列級
WHERE o.type = 'U'; -- 用戶表

-- 使用
SELECT * FROM dbo.v_ColumnDescriptions WHERE TableName = 'YourTable';

最佳實踐

1. 開發規範：每個表/列創建時同步添加 MS_Description，納入 Code Review 清單。
2. 工具生成：使用 SSMS「生成腳本」導出擴展屬性、或 PowerShell/SMO 批量同步數據字典。
3. 多語言：MS_Description 存中文，另加 MS_Description_EN 存英文，前端按語言切換顯示。
4. 避免直接操作系統表：僅通過 sp_addextendedproperty 系列存儲過程操作，保證元數據一致性。