SQL Server 2005 取得欄位說明 (Extended Properties) 完整查詢腳本

SQL Server 2005 使用擴充屬性 (MS_Description) 儲存欄位說明。本文提供直接可用的查詢腳本，支援指定資料表名稱。

核心查詢腳本

DECLARE @tblName NVARCHAR(1000) = N'你的資料表名稱'; -- 修改此處
DECLARE @TblID INT;

SELECT @TblID = [object_id] 
FROM sys.all_objects 
WHERE [type] = 'U' 
  AND [name] <> 'dtproperties' 
  AND [name] = @tblName;

SELECT 
    sc.[name] AS ColumnName,
    st.[name] AS ColumnType,
    sc.[max_length] AS ColumnLength,
    ep.[value] AS ColumnDescription
FROM sys.columns sc
LEFT JOIN sys.types st 
    ON st.user_type_id = sc.user_type_id
LEFT JOIN sys.extended_properties ep
    ON ep.major_id = sc.object_id
   AND ep.minor_id = sc.column_id
   AND ep.name = 'MS_Description'
   AND ep.class = 1 -- 1 = Object/Column
WHERE sc.object_id = @TblID
ORDER BY sc.column_id;

關鍵系統視圖說明

新增/修改欄位說明

' 新增
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'CustomerID';

' 修改 (先刪後加或用 sp_updateextendedproperty)
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'CustomerID';

批次匯出全庫欄位說明 (文件化用)

SELECT 
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    o.name AS TableName,
    c.name AS ColumnName,
    t.name AS DataType,
    c.max_length AS MaxLength,
    ep.value AS Description
FROM sys.objects o
JOIN sys.columns c ON c.object_id = o.object_id
JOIN sys.types t ON t.user_type_id = c.user_type_id
LEFT JOIN sys.extended_properties ep
    ON ep.major_id = c.object_id
   AND ep.minor_id = c.column_id
   AND ep.name = 'MS_Description'
   AND ep.class = 1
WHERE o.type = 'U' -- User Table
ORDER BY SchemaName, TableName, c.column_id;

版本差異提醒

• SQL Server 2000：使用 syscolumns、systypes、::fn_listextendedproperty (舊語法)
• SQL Server 2005+：統一使用 sys.columns、sys.types、sys.extended_properties
• syscomments、sysobjects 等舊視圖已棄用，僅為相容保留

善用擴充屬性與標準查詢，讓資料字典隨資料庫演進自動同步，是資料治理的基礎建設。