查詢資料表主鍵、外鍵、唯一約束的 SQL（Oracle / SQL Server）

Oracle 版（查詢 sys 字典視圖）

SELECT o.obj#        AS object_id,
       o.name        AS table_name,
       oc.name       AS constraint_name,
       DECODE(c.type#, 1, 'C', 2, 'P', 3, 'U',
                     4, 'R', 5, 'V', 6, 'O', 7, 'C', '?') AS constraint_type,
       col.name      AS column_name
FROM   sys.con$ oc, sys.con$ rc,
       sys.obj$ ro, sys.obj$ o, sys.obj$ oi,
       sys.cdef$ c,
       sys.col$ col, sys.ccol$ cc, sys.attrcol$ ac
WHERE  oc.con# = c.con#
AND    c.obj# = o.obj#
AND    c.rcon# = rc.con#(+)
AND    c.enabled = oi.obj#(+)
AND    c.robj# = ro.obj#(+)
AND    c.type# NOT IN (8, 12)  -- 排除 log group 等
AND    c.con# = cc.con#
AND    cc.obj# = col.obj#
AND    cc.intcol# = col.intcol#
AND    cc.obj# = o.obj#
AND    col.obj# = ac.obj#(+)
AND    col.intcol# = ac.intcol#(+)
AND    o.name = UPPER('your_table');

SQL Server 版（資訊綱要視圖 + 系統表）

SELECT 
    OBJECT_NAME(parent_object_id) AS table_name,
    name                          AS constraint_name,
    type_desc                     AS constraint_type,  -- PRIMARY_KEY, FOREIGN_KEY, UNIQUE, CHECK, DEFAULT
    COL_NAME(parent_object_id, parent_column_id) AS column_name
FROM sys.foreign_key_columns
UNION ALL
SELECT 
    OBJECT_NAME(parent_object_id) AS table_name,
    name                          AS constraint_name,
    type_desc                     AS constraint_type,
    COL_NAME(parent_object_id, parent_column_id) AS column_name
FROM sys.key_constraints
UNION ALL
SELECT 
    OBJECT_NAME(parent_object_id) AS table_name,
    name                          AS constraint_name,
    'CHECK'                       AS constraint_type,
    COL_NAME(parent_object_id, parent_column_id) AS column_name
FROM sys.check_constraints
ORDER BY table_name, constraint_type, constraint_name;

更簡潔的 SQL Server 2005+ 寫法

SELECT 
    t.name AS table_name,
    k.name AS constraint_name,
    k.type_desc AS constraint_type,
    c.name AS column_name,
    ic.key_ordinal
FROM sys.tables t
JOIN sys.key_constraints k ON k.parent_object_id = t.object_id
JOIN sys.index_columns ic ON ic.object_id = t.object_id AND ic.index_id = k.unique_index_id
JOIN sys.columns c ON c.object_id = t.object_id AND c.column_id = ic.column_id
WHERE t.name = 'your_table'
UNION ALL
SELECT 
    t.name, fk.name, 'FOREIGN_KEY', c.name, fkc.constraint_column_id
FROM sys.tables t
JOIN sys.foreign_keys fk ON fk.parent_object_id = t.object_id
JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
JOIN sys.columns c ON c.object_id = t.object_id AND c.column_id = fkc.parent_column_id
WHERE t.name = 'your_table'
ORDER BY table_name, constraint_type, constraint_name, key_ordinal;

輸出欄位說明

應用場景

• 自動產生資料庫文件、ER 圖。
• 程式碼優先遷移時比對結構差異。
• 異構資料庫同步驗證約束一致性。