萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 2008 獲取表欄位屬性與擴展屬性 (註釋) 完整查詢腳本

開發、文檔生成、自動化工具常需讀取表結構完整定義:資料類型、長度、精度、小數位、身分識別、主鍵、預設值、NULL 屬性、**擴展屬性 (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.class = 1  -- 1 = Object/Column
   AND ep.name = 'MS_Description'
INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.name = 'YourTableName' AND c.name = 'YourColumnName';

腳本二:全庫/單表完整結構文檔查詢 (含所有屬性)

SELECT 
    -- 表級資訊
    CASE WHEN c.column_id = 1 THEN OBJECT_SCHEMA_NAME(c.object_id) + '.' + OBJECT_NAME(c.object_id) END AS TableName,
    CASE WHEN c.column_id = 1 THEN ep_table.[value] END AS TableDescription,
    
    -- 欄位級資訊
    c.column_id AS ColumnOrder,
    c.name AS ColumnName,
    CASE WHEN COLUMNPROPERTY(c.object_id, c.name, 'IsIdentity') = 1 THEN N'☑' ELSE N'' END AS IsIdentity,
    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 N'☑' ELSE N'' END AS IsPK,
    t.name AS DataType,
    c.max_length AS MaxBytes,
    CASE WHEN t.name IN (N'nchar',N'nvarchar',N'sysname') THEN c.max_length/2 ELSE c.max_length END AS MaxChars,
    COLUMNPROPERTY(c.object_id, c.name, 'Precision') AS Precision,
    ISNULL(COLUMNPROPERTY(c.object_id, c.name, 'Scale'), 0) AS Scale,
    CASE WHEN c.is_nullable = 1 THEN N'☑' ELSE N'' END AS IsNullable,
    dc.definition AS DefaultValue,
    ep_col.[value] AS ColumnDescription
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
INNER 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 ep_col 
    ON c.object_id = ep_col.major_id AND c.column_id = ep_col.minor_id AND ep_col.class = 1 AND ep_col.name = 'MS_Description'
LEFT JOIN sys.extended_properties ep_table 
    ON o.object_id = ep_table.major_id AND ep_table.minor_id = 0 AND ep_table.class = 1 AND ep_table.name = 'MS_Description'
-- WHERE o.name = 'YourTable'  -- 指定單表取消註釋
ORDER BY o.object_id, c.column_id;

輸出欄位說明

輸出欄位來源說明
TableNamesys.objectsSchema.Table,僅首行顯示
TableDescriptionextended_properties (minor_id=0)表級 MS_Description
ColumnOrdersys.columns.column_id欄位順序 (1-based)
ColumnNamesys.columns.name
IsIdentityCOLUMNPROPERTY(..., 'IsIdentity')☑ = Identity 欄位
IsPKsys.indexes + index_columns☑ = 主鍵欄位
DataTypesys.types.name系統/使用者定義型別名
MaxBytes/MaxCharsmax_length位元組/字元長度 (nvarchar 除 2)
Precision/ScaleCOLUMNPROPERTYnumeric/decimal/時間精度
IsNullablesys.columns.is_nullable☑ = 允許 NULL
DefaultValuesys.default_constraints.definition含括號,如 `((getdate()))`
ColumnDescriptionextended_properties (class=1, minor_id=column_id)欄位級 MS_Description

擴展屬性寫入/更新範例

-- 表級
EXEC sys.sp_addextendedproperty 
    @name = N'MS_Description', @value = N'客戶主檔',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE', @level1name = N'Customer';

-- 欄位級
EXEC sys.sp_addextendedproperty 
    @name = N'MS_Description', @value = N'客戶唯一識別碼',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE', @level1name = N'Customer',
    @level2type = N'COLUMN', @level2name = N'CustomerID';

-- 更新 (若已存在)
EXEC sys.sp_updateextendedproperty ... (參數同 sp_addextendedproperty);

自動化文檔生成管線

  1. 排程執行上述查詢 → 匯出 CSV/Excel/Markdown
  2. 配合 `dbt docs generate` / `SchemaSpy` / `DataDictionary` 工具產出互動式文檔站
  3. CI/CD 階段強制檢查:核心表欄位 **必須有 MS_Description**,否則 Pipeline 失敗

將「結構定義」與「業務語義 (註釋)」統一存儲於資料庫元資料中,透過標準系統視圖查詢,實現**文檔與代碼同源、零維護成本**的自動化數據字典。

copyright © 萬盛學電腦網 all rights reserved