SQL Server 表結構完整獲取指南：字段、主鍵、外鍵、自增、描述

核心系統視圖介紹

SQL Server 2005+ 推薦使用新式目錄視圖（sys.*），比舊式 syscolumns 更規範、功能更豐富。

四大核心查詢場景

1. 基礎字段屬性查詢

SELECT 
    c.name AS column_name,
    t.name AS data_type,
    c.max_length,
    c.precision,
    c.scale,
    c.is_nullable,
    c.is_identity
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('你的表名')
ORDER BY c.column_id;

2. 含描述信息的完整字段查詢

DECLARE @table_name VARCHAR(128) = '你的表名';

SELECT 
    c.name AS column_name,
    t.name AS data_type,
    c.max_length,
    c.is_nullable,
    -- 自增標識
    CASE WHEN ic.column_id IS NOT NULL THEN 1 ELSE 0 END AS is_identity,
    -- 擴展屬性描述
    ep.value AS description
FROM sys.columns c
JOIN sys.tables tb ON c.object_id = tb.object_id
JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT JOIN sys.identity_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_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
WHERE tb.name = @table_name
ORDER BY c.column_id;

3. 單獨查詢自增字段

SELECT name 
FROM sys.columns 
WHERE object_id = OBJECT_ID('你的表名') 
  AND is_identity = 1;

4. 獲取主鍵與外鍵約束

-- 方法一：系統存儲過程（簡單快速）
EXEC sp_helpconstraint '你的表名';

-- 方法二：詳細查詢（可程序化處理）
-- 主鍵
SELECT 
    kc.name AS constraint_name,
    c.name AS column_name,
    'PRIMARY KEY' AS constraint_type
FROM sys.key_constraints kc
JOIN sys.index_columns ic ON kc.parent_object_id = ic.object_id AND kc.unique_index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE kc.parent_object_id = OBJECT_ID('你的表名') AND kc.type = 'PK';

-- 外鍵
SELECT 
    fk.name AS constraint_name,
    OBJECT_NAME(fk.parent_object_id) AS table_name,
    COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS column_name,
    OBJECT_NAME(fk.referenced_object_id) AS referenced_table,
    COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS referenced_column
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE fk.parent_object_id = OBJECT_ID('你的表名') OR fk.referenced_object_id = OBJECT_ID('你的表名');

結果示例說明

查詢結果包含：字段名、數據類型、長度、是否為空、是否自增、描述信息、約束類型、引用表/字段等完整元數據，可直接用於文檔生成、代碼生成、異構遷移等場景。