SQL Server 2005 查詢所有資料表、欄位、索引、鍵值與屬性完整腳本

前言

在進行資料庫文件化、遷移評估、Schema 比對或逆向工程時，需要快速取得完整的資料表結構中繼資料。本文提供一支詳細的 T-SQL 查詢，可一次輸出所有使用者資料表的欄位名稱、資料類型、長度、精度、小數位、預設值、是否允許 NULL、是否為主鍵/外鍵/索引欄位、索引名稱等資訊，並附上常用的簡易查詢腳本供參考。

一、完整中繼資料查詢 (適用 SQL Server 2005+)

SELECT
    '' AS RowNum,
    t.name AS TableName,
    c.name AS ColumnName,
    ty.name AS DataType,
    -- 資料類型 + 長度/精度/小數位 顯示
    ty.name +
    CASE
        WHEN ty.name IN ('text','ntext','image','xml','hierarchyid','geometry','geography') THEN ''
        WHEN c.max_length = -1 THEN '(MAX)'
        WHEN ty.name IN ('nchar','nvarchar','sysname') THEN '(' + CAST(c.max_length/2 AS VARCHAR(10)) + ')'
        WHEN ty.name IN ('char','varchar','binary','varbinary') THEN '(' + CAST(c.max_length AS VARCHAR(10)) + ')'
        WHEN ty.name IN ('decimal','numeric') THEN '(' + CAST(c.precision AS VARCHAR(10)) + ',' + CAST(c.scale AS VARCHAR(10)) + ')'
        ELSE ''
    END AS DataTypeFull,
    -- 預設值
    ISNULL(dc.definition, '') AS DefaultValue,
    -- 是否允許 NULL
    CASE c.is_nullable WHEN 1 THEN 'Y' ELSE 'N' END AS IsNullable,
    -- 主鍵
    CASE WHEN pk.is_primary_key = 1 THEN 'Y' ELSE 'N' END AS IsPrimaryKey,
    pk.name AS PK_Name,
    -- 外鍵
    CASE WHEN fk.parent_object_id IS NOT NULL THEN 'Y' ELSE 'N' END AS IsForeignKey,
    fk.name AS FK_Name,
    -- 索引 (非主鍵/非唯一)
    CASE WHEN ix.is_primary_key = 0 AND ix.is_unique = 0 AND ix.index_id > 1 THEN 'Y' ELSE 'N' END AS IsIndexed,
    ix.name AS IndexName
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.default_constraints dc ON c.default_object_id = dc.object_id
-- 主鍵
LEFT JOIN (
    SELECT ic.object_id, ic.column_id, i.name, i.is_primary_key
    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 c.object_id = pk.object_id AND c.column_id = pk.column_id
-- 外鍵
LEFT JOIN (
    SELECT fkc.parent_object_id, fkc.parent_column_id, fk.name
    FROM sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
) fk ON c.object_id = fk.parent_object_id AND c.column_id = fk.parent_column_id
-- 索引 (排除主鍵、唯一索引、堆叠)
LEFT JOIN (
    SELECT ic.object_id, ic.column_id, i.name, i.is_primary_key, i.is_unique, i.index_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 = 0 AND i.is_unique = 0 AND i.index_id > 1
) ix ON c.object_id = ix.object_id AND c.column_id = ix.column_id
WHERE t.type = 'U'  -- 僅使用者資料表
ORDER BY t.name, c.column_id;

二、輸出欄位說明

三、常用簡易查詢腳本速查

1. 查詢資料表所有索引 (含類型、構成欄位)

SELECT
    ic.index_id,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    i.is_primary_key,
    i.is_unique,
    STUFF((SELECT ', ' + c.name
           FROM sys.index_columns ic2
           JOIN sys.columns c ON ic2.object_id = c.object_id AND ic2.column_id = c.column_id
           WHERE ic2.object_id = ic.object_id AND ic2.index_id = ic.index_id
           ORDER BY ic2.key_ordinal
           FOR XML PATH('')), 1, 2, '') AS KeyColumns
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.object_id = OBJECT_ID('YourTable')
GROUP BY ic.object_id, ic.index_id, i.name, i.type_desc, i.is_primary_key, i.is_unique
ORDER BY i.index_id;

2. 查詢主鍵 (名稱、構成欄位)

SELECT
    cu.constraint_name AS PK_Name,
    cu.column_name,
    cu.ordinal_position
FROM information_schema.key_column_usage cu
JOIN information_schema.table_constraints tc
  ON cu.constraint_name = tc.constraint_name
 AND cu.table_name = tc.table_name
WHERE tc.constraint_type = 'PRIMARY KEY'
  AND tc.table_name = 'YourTable'
ORDER BY cu.ordinal_position;

3. 查詢唯一約束

SELECT cu.constraint_name, cu.column_name
FROM information_schema.key_column_usage cu
JOIN information_schema.table_constraints tc
  ON cu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'UNIQUE'
  AND tc.table_name = 'YourTable';

4. 查詢外鍵 (含參考資料表/欄位)

SELECT
    fk.name AS FK_Name,
    OBJECT_NAME(fk.parent_object_id) AS TableName,
    pc.name AS ColumnName,
    OBJECT_NAME(fk.referenced_object_id) AS RefTableName,
    rc.name AS RefColumnName
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns pc ON fkc.parent_object_id = pc.object_id AND fkc.parent_column_id = pc.column_id
JOIN sys.columns rc ON fkc.referenced_object_id = rc.object_id AND fkc.referenced_column_id = rc.column_id
WHERE fk.parent_object_id = OBJECT_ID('YourTable');

5. 查詢資料表所有欄位與擴充屬性 (含說明)

SELECT
    c.name AS ColumnName,
    ty.name AS DataType,
    c.max_length,
    c.precision,
    c.scale,
    c.is_nullable,
    ISNULL(dc.definition, '') AS DefaultValue,
    ep.value AS Description
FROM sys.columns c
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
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.name = 'MS_Description'
WHERE c.object_id = OBJECT_ID('YourTable')
ORDER BY c.column_id;

四、匯出建議

• SSMS 查詢結果 → 右鍵「另存結果為」→ CSV/Tab 分隔文字檔。
• 或使用 bcp / sqlcmd / PowerShell Invoke-Sqlcmd 自動化匯出。
• 定期排程匯出至版控庫 (Git)，作為 Schema 變更歷史追蹤。

結語

掌握 sys.tables、sys.columns、sys.indexes、sys.foreign_keys、sys.extended_properties 等目錄檢視的關聯查詢，即能隨時產出完整、精確的資料字典，支援文件化、遷移、審計與開發協作等多種場景需求。