開發、文檔生成、自動化工具常需讀取表結構完整定義:資料類型、長度、精度、小數位、身分識別、主鍵、預設值、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;
| 輸出欄位 | 來源 | 說明 |
|---|---|---|
| TableName | sys.objects | Schema.Table,僅首行顯示 |
| TableDescription | extended_properties (minor_id=0) | 表級 MS_Description |
| ColumnOrder | sys.columns.column_id | 欄位順序 (1-based) |
| ColumnName | sys.columns.name | |
| IsIdentity | COLUMNPROPERTY(..., 'IsIdentity') | ☑ = Identity 欄位 |
| IsPK | sys.indexes + index_columns | ☑ = 主鍵欄位 |
| DataType | sys.types.name | 系統/使用者定義型別名 |
| MaxBytes/MaxChars | max_length | 位元組/字元長度 (nvarchar 除 2) |
| Precision/Scale | COLUMNPROPERTY | numeric/decimal/時間精度 |
| IsNullable | sys.columns.is_nullable | ☑ = 允許 NULL |
| DefaultValue | sys.default_constraints.definition | 含括號,如 `((getdate()))` |
| ColumnDescription | extended_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);
將「結構定義」與「業務語義 (註釋)」統一存儲於資料庫元資料中,透過標準系統視圖查詢,實現**文檔與代碼同源、零維護成本**的自動化數據字典。