SQL Server 存儲過程：快速查詢表間外鍵關係

系統表關聯查詢外鍵元數據

利用 sysforeignkeys、sysobjects、syscolumns 系統表，編寫腳本快速獲取指定表的「被引用」與「引用他表」的外鍵詳細信息。

場景一：查詢「我引用了哪些表」

DECLARE @id BIGINT;
SELECT @id = id FROM sysobjects WHERE name = 'd_病人資料庫';

SELECT 
    a.name AS 被參照表名,
    b.rkey AS 被參照列位置,
    c.name AS 被參照列名,
    b.fkey AS 參照字段本表位置,
    d.name AS 參照字段名
FROM sysforeignkeys b
JOIN sysobjects a ON a.id = b.rkeyid
JOIN syscolumns c ON c.id = a.id AND c.colid = b.rkey
JOIN (SELECT name, colid FROM syscolumns WHERE id = @id) d ON d.colid = b.fkey
WHERE b.fkeyid = @id;

場景二：查詢「哪些表引用了我」

SELECT 
    a.name AS 參照我表名,
    c.name AS 列名,
    b.fkey AS 參照字段位置,
    b.rkey AS 我被參照字段位置,
    d.name AS 我被參照字段名
FROM sysforeignkeys b
JOIN sysobjects a ON a.id = b.fkeyid
JOIN syscolumns c ON c.id = a.id AND c.colid = b.fkey
JOIN (SELECT name, colid FROM syscolumns WHERE id = @id) d ON d.colid = b.rkey
WHERE b.rkeyid = @id;

結果字段含義

現代版本更簡單的寫法 (SQL Server 2008+)

-- 查詢某表所有外鍵詳情
SELECT 
    OBJECT_NAME(fk.parent_object_id) AS 子表,
    COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS 子表列,
    OBJECT_NAME(fk.referenced_object_id) AS 父表,
    COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS 父表列,
    fk.name AS 外鍵名,
    fk.delete_referential_action_desc AS 刪除動作,
    fk.update_referential_action_desc AS 更新動作
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('d_病人資料庫')
   OR fk.referenced_object_id = OBJECT_ID('d_病人資料庫');

實用場景

• 數據庫文檔自動生成
• 刪除數據前檢查關聯依賴
• 模型重構時評估影響範圍
• ETL 流程中驗證外鍵完整性