SQL Server 一鍵查詢所有資料表欄位完整資訊（含主鍵、描述、型別、長度）

開發資料字典、產生程式碼、匯出 Excel 時，常需取得資料庫中所有使用者資料表的欄位中繼資料。本文提供一支強化版查詢，輸出：資料表、欄位、資料型別、長度、精確度/小數位、是否允許 NULL、預設值、是否主鍵、身分識別欄位、擴充屬性描述，並相容 SQL Server 2008 至 2022。

完整查詢腳本

SELECT 
    SCHEMA_NAME(t.schema_id)            AS SchemaName,
    t.name                                AS TableName,
    c.name                                AS ColumnName,
    ty.name                               AS DataType,
    CASE 
        WHEN ty.name IN ('varchar','nvarchar','char','nchar','varbinary','binary') 
             AND c.max_length = -1 THEN 'MAX'
        WHEN ty.name IN ('nvarchar','nchar') THEN CAST(c.max_length / 2 AS VARCHAR(10))
        ELSE CAST(c.max_length AS VARCHAR(10))
    END                                   AS MaxLength,
    c.precision                           AS Precision,
    c.scale                               AS Scale,
    c.is_nullable                         AS IsNullable,
    ISNULL(dc.definition, '')             AS DefaultConstraint,
    CASE WHEN ic.object_id IS NOT NULL THEN 1 ELSE 0 END AS IsIdentity,
    CASE WHEN pk.column_id IS NOT NULL THEN 1 ELSE 0 END AS IsPrimaryKey,
    pk.key_ordinal                        AS PK_Ordinal,
    ep.value                              AS Description
FROM sys.tables t
INNER JOIN sys.columns c ON c.object_id = t.object_id
INNER JOIN sys.types ty ON ty.user_type_id = c.user_type_id
LEFT JOIN sys.default_constraints dc ON dc.parent_object_id = c.object_id 
    AND dc.parent_column_id = c.column_id
LEFT JOIN sys.identity_columns ic ON ic.object_id = c.object_id 
    AND ic.column_id = c.column_id
LEFT JOIN (
    SELECT ic.object_id, ic.column_id, 1 AS column_id, ROW_NUMBER() OVER (PARTITION BY ic.object_id ORDER BY ic.key_ordinal) AS key_ordinal
    FROM sys.indexes i
    INNER JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.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 sys.extended_properties ep ON ep.major_id = t.object_id 
    AND ep.minor_id = c.column_id 
    AND ep.name = 'MS_Description'
WHERE t.type = 'U'  -- 僅使用者資料表
ORDER BY SchemaName, TableName, c.column_id;

輸出欄位說明

進階用法

1. 直接匯出 CSV (sqlcmd / PowerShell)

sqlcmd -S . -d MyDB -i GetColumns.sql -o Columns.csv -s "," -W -h-1

2. 產生 Markdown 資料字典

SELECT 
    '| ' + SchemaName + ' | ' + TableName + ' | ' + ColumnName + ' | ' + DataType + ' | ' + 
    MaxLength + ' | ' + CAST(IsNullable AS CHAR(1)) + ' | ' + 
    ISNULL(Description, '') + ' |'
FROM (/* 上述查詢 */) q
ORDER BY SchemaName, TableName, ColumnName;

3. 僅檢視特定資料表

WHERE t.name IN ('Customers','Orders','OrderDetails')

4. 找出缺少描述的欄位 (治理用)

WHERE ep.value IS NULL 
  AND c.name NOT IN ('CreatedDate','ModifiedDate','RowVersion') -- 排除系統維護欄位

效能與權限提示

• 查詢僅讀取系統目錄檢視，不掃描使用者資料，毫秒級完成
• 需具備 VIEW DEFINITION 權限或 db_datareader 角色
• 若資料庫含大量資料表 (＞ 10,000)，建議加上 WHERE t.schema_id = SCHEMA_ID('dbo') 限制範圍

常見問題 FAQ

Q: 為何不直接用 INFORMATION_SCHEMA.COLUMNS？

A: 它無法取得 Identity、擴充屬性、預設值定義、主鍵順序等關鍵資訊，且效能較差。

Q: 如何取得外鍵關聯資訊？

A: 需額外 JOIN sys.foreign_keys、sys.foreign_key_columns，建議另寫一支專用查詢。

Q: MAX 型別長度顯示 -1 怎麼辦？

A: 本查詢已處理：max_length = -1 → 'MAX'。

Q: 系統資料表 (sys.*, INFORMATION_SCHEMA.*) 會被列出嗎？

A: 不會，WHERE t.type = 'U' 僅保留使用者建立的資料表。

總結

善用 sys.* 目錄檢視組合查詢，能一次取得完整、結構化的欄位中繼資料，支援文件產生、程式碼生成、治理稽核等多種場景。將此腳本加入工具箱，下次需求來時直接 Ctrl+Shift+P 執行即可。