專案交付、審計、文件化需求常需要資料字典。一條關聯系統目錄檢視的查詢即可產出含表/欄位說明、型別、長度、精度、主鍵、預設值、識別欄的完整字典。
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;
| 欄位 | 來源 | 說明 |
|---|---|---|
| SchemaName | SCHEMA_NAME() | 結構描述名稱 |
| TableName | sys.tables.name | 資料表名稱 |
| TableDescription | sys.extended_properties (class=1, minor_id=0) | 表層級 MS_Description |
| ColumnName | sys.columns.name | 欄位名稱 |
| ColumnDescription | sys.extended_properties (class=1, minor_id=column_id) | 欄位層級 MS_Description |
| DataType | sys.types.name | 系統/使用者定義型別 |
| max_length/precision/scale | sys.columns | 長度/精度/小數位 |
| is_nullable | sys.columns | 1=允許 NULL |
| DefaultDefinition | sys.default_constraints.definition | 預設值運算式 (含括號) |
| IsIdentity | sys.columns.is_identity | 識別欄標記 |
| KeyType | sys.indexes (is_primary_key) | PK 標記 |
| FKType | sys.foreign_key_columns | FK 標記 |
| UQType | sys.indexes (is_unique, not PK) | Unique Constraint 標記 |
-- 新增/更新表說明
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;
Invoke-Sqlcmd → 匯出 CSV/Excel/Markdown/HTMLSELECT
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';
一條查詢、零工具依賴、版本相容,掌握系統目錄查詢即掌握資料字典生成主動權。