萬盛學電腦網

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

一條 SQL 產出完整資料字典:SQL Server 系統表關聯查詢範本

專案交付、審計、文件化需求常需要資料字典。一條關聯系統目錄檢視的查詢即可產出含表/欄位說明、型別、長度、精度、主鍵、預設值、識別欄的完整字典。

通用資料字典查詢 (SQL 2005+)

SELECT 
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    ep.value AS TableDescription,
    c.name AS ColumnName,
    col_ep.value AS ColumnDescription,
    ty.name AS DataType,
    c.max_length,
    c.precision,
    c.scale,
    c.is_nullable,
    ISNULL(c.default_object_id, 0) AS HasDefault,
    dc.definition AS DefaultDefinition,
    CASE WHEN c.is_identity = 1 THEN '✓' ELSE '' END AS IsIdentity,
    CASE WHEN pk.column_id IS NOT NULL THEN 'PK' ELSE '' END AS KeyType,
    CASE WHEN fk.parent_column_id IS NOT NULL THEN 'FK' ELSE '' END AS FKType,
    CASE WHEN uq.column_id IS NOT NULL THEN 'UQ' ELSE '' END AS UQType
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 ep 
    ON ep.major_id = t.object_id AND ep.minor_id = 0 AND ep.class = 1
LEFT JOIN sys.extended_properties col_ep 
    ON col_ep.major_id = c.object_id AND col_ep.minor_id = c.column_id AND col_ep.class = 1
LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
LEFT JOIN (
    SELECT ic.object_id, ic.column_id
    FROM sys.indexes i
    INNER 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
) pk ON pk.object_id = c.object_id AND pk.column_id = c.column_id
LEFT JOIN (
    SELECT fkc.parent_object_id, fkc.parent_column_id
    FROM sys.foreign_key_columns fkc
) fk ON fk.parent_object_id = c.object_id AND fk.parent_column_id = c.column_id
LEFT JOIN (
    SELECT ic.object_id, ic.column_id
    FROM sys.indexes i
    INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    WHERE i.is_unique = 1 AND i.is_primary_key = 0
) uq ON uq.object_id = c.object_id AND uq.column_id = c.column_id
WHERE t.is_ms_shipped = 0
ORDER BY SchemaName, TableName, c.column_id;

輸出欄位說明

欄位來源說明
SchemaNameSCHEMA_NAME()結構描述名稱
TableNamesys.tables.name資料表名稱
TableDescriptionsys.extended_properties (class=1, minor_id=0)表層級 MS_Description
ColumnNamesys.columns.name欄位名稱
ColumnDescriptionsys.extended_properties (class=1, minor_id=column_id)欄位層級 MS_Description
DataTypesys.types.name系統/使用者定義型別
max_length/precision/scalesys.columns長度/精度/小數位
is_nullablesys.columns1=允許 NULL
DefaultDefinitionsys.default_constraints.definition預設值運算式 (含括號)
IsIdentitysys.columns.is_identity識別欄標記
KeyTypesys.indexes (is_primary_key)PK 標記
FKTypesys.foreign_key_columnsFK 標記
UQTypesys.indexes (is_unique, not PK)Unique Constraint 標記

擴充屬性 (MS_Description) 管理

-- 新增/更新表說明
EXEC sys.sp_addextendedproperty 
    @name = N'MS_Description', @value = N'客戶主檔',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE', @level1name = N'Customers';

-- 新增/更新欄位說明
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';

-- 讀取說明 (已包含在主查詢中)
SELECT * FROM sys.extended_properties WHERE class = 1;

匯出自動化建議

  • SSMS 「產生指令碼」→ 選擇「擴充屬性」→ 匯出 .sql
  • PowerShell + Invoke-Sqlcmd → 匯出 CSV/Excel/Markdown/HTML
  • CI/CD 管線整合:建置階段自動產出資料字典文件
  • 工具:SchemaSpy, DataDictionaryCreator, ApexSQL Doc, Redgate SQL Doc

相容 SQL 2000 版本 (系統基底表)

SELECT 
    so.name AS [Table], 
    sp.value AS TableDescription,
    sc.name AS Field,
    sep.value AS FieldDescription,
    st.name AS Type,
    sc.length,
    ISNULL(COLUMNPROPERTY(sc.id, sc.name, 'Scale'), 0) AS Scale,
    sc.isnullable AS IsNull,
    CASE WHEN scm.text IS NULL THEN '' ELSE scm.text END AS [Default],
    CASE WHEN COLUMNPROPERTY(sc.id, sc.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS Identity,
    CASE WHEN EXISTS (
        SELECT 1 FROM sysobjects so_pk 
        WHERE so_pk.xtype = 'PK' 
        AND so_pk.name IN (
            SELECT si.name FROM sysindexes si
            INNER JOIN sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid
            WHERE sik.id = sc.id AND sik.colid = sc.colid
        )
    ) THEN '√' ELSE '' END AS PK
FROM syscolumns sc
INNER JOIN sysobjects so ON so.id = sc.id
INNER JOIN systypes st ON sc.xtype = st.xtype
LEFT JOIN sysproperties sep ON sc.id = sep.id AND sc.colid = sep.smallid
LEFT JOIN sysproperties sp ON so.id = sp.id AND sp.smallid = 0
LEFT JOIN syscomments scm ON sc.cdefault = scm.id
WHERE so.xtype = 'U';

一條查詢、零工具依賴、版本相容,掌握系統目錄查詢即掌握資料字典生成主動權。

copyright © 萬盛學電腦網 all rights reserved