SQL Server 2005 遞迴 CTE 實戰：輕鬆查詢外鍵引用樹

場景痛點

系統新舊切換清空資料表時，常因多層外鍵引用導致刪除順序極難梳理。手動追蹤「Table2 參考 Table1、Table3 參考 Table2...」不僅耗時，極易遺漏導致刪除失敗。

解決方案：遞迴 CTE 一次查全引用鏈

利用 SQL Server 2005 引入的 Common Table Expression (CTE) 遞迴特性，配合系統目錄視圖，單一腳本即可輸出完整外鍵引用樹。

核心腳本解析

DECLARE @tbname NVARCHAR(256);
SET @tbname = N'dbo.aspnet_Applications';  -- 目標根表

WITH fkids AS (
    -- 取得所有外鍵與對應主鍵的物件 ID
    SELECT 
        OBJECT_ID(CONSTRAINT_NAME) AS FkId,
        OBJECT_ID(UNIQUE_CONSTRAINT_NAME) AS PkId
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
),
relations AS (
    -- 解析父/子表物件 ID
    SELECT 
        p.parent_object_id AS pktableId,
        f.parent_object_id AS fktableid,
        i.pkid, i.fkid
    FROM fkids i
    INNER JOIN sys.objects p ON i.pkid = p.[object_id]
    INNER JOIN sys.objects f ON i.fkid = f.[object_id]
),
cte AS (
    -- 遞迴錨點：根表的直接子表
    SELECT * FROM relations WHERE pktableid = OBJECT_ID(@tbname)
    UNION ALL
    -- 遞迴成員：逐層向下尋找子表的子表
    SELECT r.* FROM cte c
    JOIN relations r ON r.pktableid = c.fktableid
)
-- 最終輸出：父表、子表、主鍵名、外鍵名
SELECT 
    OBJECT_NAME(pktableid) AS pktable,
    OBJECT_NAME(fktableid) AS fktable,
    OBJECT_NAME(pkid) AS pk,
    OBJECT_NAME(fkid) AS fk
FROM cte;

關鍵點說明

• INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS：標準視圖，跨版本相容性佳。
• sys.objects：取得物件名稱與父子關係所需的 parent_object_id。
• 遞迴終止條件：當某層無子表時自然結束，無需額外 MAXRECURSION 限制（預設 100 層足夠絕多數場景）。
• 輸出欄位：pktable/fktable 直觀展示引用鏈，pk/fk 便於直接生成 DROP CONSTRAINT 腳本。

實戰延伸

1. 生成刪除順序腳本：將查詢結果倒序，配合動態 SQL 批量產生 ALTER TABLE ... DROP CONSTRAINT 與 DELETE/TRUNCATE 語句。
2. 視覺化依賴圖：匯出至 GraphViz (DOT) 或 Mermaid 語法，快速繪製 ER 依賴拓撲。
3. 參數化封裝：封裝為預存程序 usp_GetFKTree @RootTable，供部署管線自動呼叫。

總結

遞迴 CTE 將複雜的層級查詢轉化為集合運算，配合系統目錄視圖，讓外鍵引用樹查詢從「人工梳理」升級為「秒級自動化」，是 DBA 與開發人員處理資料遷移、清理、重構的必備利器。