您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

一條 SQL 查詢全庫所有表結構：欄位、鍵、類型、說明一次滿足

編輯：關於SqlServer

開發、文檔、審計、遷移評估時，常需快速掌握資料庫完整結構。下列單一查詢即可輸出所有使用者表的：表名、欄位序號、欄位名、是否 Identity、是否 PK、資料類型、長度、精度、小數位、是否允許 NULL、預設值、擴展屬性說明。直接在 SSMS 執行即可，無需建立物件、無權限要求 (需 `VIEW DEFINITION`)。

完整查詢腳本 (SQL Server 2005+)

SELECT 
    CASE WHEN c.colorder = 1 THEN o.name ELSE '' END AS [表名],
    c.colorder AS [欄位序號],
    c.name AS [欄位名],
    CASE WHEN COLUMNPROPERTY(c.id, c.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS [識別欄],
    CASE WHEN EXISTS (
        SELECT 1 FROM sysindexes i
        JOIN sysindexkeys k ON i.id = k.id AND i.indid = k.indid
        JOIN syscolumns c2 ON c2.id = k.id AND c2.colid = k.colid
        WHERE i.id = c.id AND c2.name = c.name AND (i.status & 0x800) = 0x800
    ) THEN '√' ELSE '' END AS [主鍵],
    t.name AS [資料類型],
    c.length AS [佔用位元組],
    COLUMNPROPERTY(c.id, c.name, 'PRECISION') AS [長度],
    ISNULL(COLUMNPROPERTY(c.id, c.name, 'Scale'), 0) AS [小數位數],
    CASE WHEN c.isnullable = 1 THEN '√' ELSE '' END AS [允許空值],
    ISNULL(com.text, '') AS [預設值],
    ISNULL(ep.value, '') AS [欄位說明]
FROM syscolumns c
LEFT JOIN systypes t ON c.xtype = t.xusertype
INNER JOIN sysobjects o ON c.id = o.id AND o.xtype = 'U' AND o.name <> 'dtproperties'
LEFT JOIN syscomments com ON c.cdefault = com.id
LEFT JOIN sys.extended_properties ep ON ep.major_id = c.id AND ep.minor_id = c.colid AND ep.class = 1
ORDER BY o.name, c.colorder;

關鍵系統表/函數解析

物件	用途	備註
`syscolumns` / `sys.columns` (2005+)	欄位核心中繼資料	相容性視圖，新專案建議用 `sys.columns`
`sysobjects` / `sys.objects`	物件清單 (表、視圖、程序...)	`xtype='U'` = 使用者表
`systypes` / `sys.types`	系統/使用者定義資料類型	`xusertype` 為類型 ID
`syscomments` / `sys.default_constraints`	預設約束定義	`cdefault` 指向 `syscomments.id`
`sys.extended_properties`	擴展屬性 (MS_Description 等)	`class=1` 代表欄位層級，`minor_id=colid`
`COLUMNPROPERTY(id, name, 'IsIdentity')`	判斷 Identity 欄位	回傳 1/0
`sysindexes` + `sysindexkeys`	判斷主鍵 (舊版相容寫法)	新版建議用 `sys.key_constraints` + `sys.index_columns`

現代化版本 (SQL Server 2012+ 推薦)

SELECT 
    OBJECT_SCHEMA_NAME(c.object_id) + '.' + OBJECT_NAME(c.object_id) AS [Table],
    c.name AS [Column],
    c.column_id AS [Ordinal],
    TYPE_NAME(c.user_type_id) AS [DataType],
    c.max_length AS [MaxBytes],
    c.precision, c.scale,
    c.is_identity AS [IsIdentity],
    c.is_nullable AS [Nullable],
    dc.definition AS [DefaultValue],
    ep.value AS [Description],
    CASE WHEN pk.column_id IS NOT NULL THEN 'PK' ELSE '' END AS [Key]
FROM sys.columns c
JOIN sys.objects o ON c.object_id = o.object_id AND o.type = 'U'
LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
LEFT JOIN sys.extended_properties ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id AND ep.class = 1
LEFT JOIN (
    SELECT ic.object_id, ic.column_id
    FROM sys.index_columns ic
    JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    WHERE i.is_primary_key = 1
) pk ON c.object_id = pk.object_id AND c.column_id = pk.column_id
ORDER BY [Table], c.column_id;

輸出應用場景

匯出 Excel/Markdown：SSMS 結果網格右鍵 → 「另存為 CSV」→ 文檔化。
程式碼產生器輸入：Entity Class、Repository、Migration 腳本自動產生。
異構遷移評估：對比來源/目標結構差異 (類型映射、長度截斷、Identity 對應)。
審計/合規：驗證敏感欄位是否有說明、是否加密、是否符合命名規範。

進階：加入索引、外鍵、觸發器資訊

若需完整物件相依圖，可擴展 JOIN `sys.indexes`、`sys.foreign_keys`、`sys.triggers`，但單一查詢會變得極大。建議採用 PowerShell + SMO 或 dbatools 模組 (`Get-DbaDbTableStructure`) 產出結構化物件，再序列化為 JSON/YAML 供下游消費。

一條 SQL，讓資料庫結構「透明化」，是每個 DBA/開發工具箱的必備利器。