使用While循环按子到父层级获取数据库表以生成删除语句

由于数据库存在子表与父表相互引用的循环外键依赖，递归CTE无法正常运行（执行时会抛出错误：The statement terminated. The maximum recursion 100 has been exhausted before statement completion.）。需要生成从子表到父表的顺序列表，用于编写数据删除语句，避免外键约束报错。

我的思路是先获取所有表，定位无依赖的叶子节点存入临时表，再迭代查找未被收录的父表来规避循环，但不知道具体实现方式，求可行方案（无需删除索引）。

附上现有查询外键的SQL代码：

SELECT
    SCHEMA_NAME(pt.schema_id) + '.' + pt.name AS foreign_table
    , fk.name AS foreign_key_name
    , SCHEMA_NAME(pt.schema_id) + '.' + pt.name AS parent_table_name
    , pc.name AS parent_column_name
    , SCHEMA_NAME(ct.schema_id) + '.' + ct.name AS referenced_table_name
    , cc.name AS referenced_column_name --INTO #StateSKForeginTables
    , ct.object_id
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc
    ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.tables pt
    ON pt.object_id = fk.parent_object_id
INNER JOIN sys.columns pc
    ON fkc.parent_column_id = pc.column_id
    AND pc.object_id = pt.object_id
INNER JOIN sys.tables ct
    ON ct.object_id = fk.referenced_object_id
INNER JOIN sys.columns cc
    ON cc.object_id = ct.object_id
    AND fkc.referenced_column_id = cc.column_id
ORDER BY pt.name, pc.name

以及报错的递归CTE代码：

WITH dependencies -- Get object with FK dependencies
AS (
    SELECT FK.TABLE_NAME AS Obj
        , PK.TABLE_NAME AS Depends
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
        ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
        ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
    ), 
no_dependencies -- The first level are objects with no dependencies 
AS (
    SELECT 
        name AS Obj
    FROM sys.objects
    WHERE name NOT IN (SELECT obj FROM dependencies) --we remove objects with dependencies from first CTE
    AND type = 'U' -- Just tables
    ) 

--SELECT * FROM no_dependencies -- znajude mi widoki widokow nie chce.

,recursiv -- recursive CTE to get dependencies
AS (
    SELECT Obj AS [Table]
        , CAST('' AS VARCHAR(max)) AS DependsON
        , 0 AS LVL -- Level 0 indicate tables with no dependencies
    FROM no_dependencies
 
    UNION ALL
 
    SELECT d.Obj AS [Table]
        , CAST(IIF(LVL > 0, r.DependsON + ' > ', '') + d.Depends AS VARCHAR(max)) -- visually reflects hierarchy
        , R.lvl + 1 AS LVL
    FROM dependencies d
    INNER JOIN recursiv r
        ON d.Depends = r.[Table]
    )
-- The final result with each table only shown once
SELECT SCHEMA_NAME(O.schema_id) AS [TableSchema]
    , R.[Table]
    , max(R.LVL) as LVL
FROM recursiv R
INNER JOIN sys.objects O
    ON R.[Table] = O.name
GROUP BY O.schema_id, R.[Table]
ORDER BY LVL
    , R.[Table]

解决方案：迭代式收集删除顺序 #

用临时表迭代的方式替代递归CTE，规避循环依赖问题，具体步骤如下：

1. 创建临时表存储外键依赖关系 #

先把所有外键的子表-父表关系整理出来，包含schema信息避免同名表冲突：

-- 存储外键依赖：子表 -> 父表
CREATE TABLE #FK_Dependencies (
    ChildTable NVARCHAR(128) NOT NULL,
    ParentTable NVARCHAR(128) NOT NULL,
    PRIMARY KEY (ChildTable, ParentTable)
)

INSERT INTO #FK_Dependencies
SELECT
    SCHEMA_NAME(pt.schema_id) + '.' + pt.name AS ChildTable,
    SCHEMA_NAME(ct.schema_id) + '.' + ct.name AS ParentTable
FROM sys.foreign_keys fk
INNER JOIN sys.tables pt ON pt.object_id = fk.parent_object_id
INNER JOIN sys.tables ct ON ct.object_id = fk.referenced_object_id
GROUP BY SCHEMA_NAME(pt.schema_id) + '.' + pt.name, SCHEMA_NAME(ct.schema_id) + '.' + ct.name

2. 创建临时表存储最终删除顺序 #

-- 存储删除顺序，Level越小越先删除（子表在前）
CREATE TABLE #DeleteOrder (
    TableName NVARCHAR(128) NOT NULL PRIMARY KEY,
    [Level] INT NOT NULL
)

3. 初始化：加入无依赖的叶子节点 #

先找到没有子表依赖的表（即没有其他表引用它的表），这些是最底层的子表，优先删除：

INSERT INTO #DeleteOrder
SELECT
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName,
    0 AS [Level]
FROM sys.tables t
WHERE NOT EXISTS (
    SELECT 1 FROM #FK_Dependencies d WHERE d.ParentTable = SCHEMA_NAME(t.schema_id) + '.' + t.name
)
AND SCHEMA_NAME(t.schema_id) + '.' + t.name NOT IN (SELECT TableName FROM #DeleteOrder)

4. 迭代收集父表 #

循环找出所有子表已在删除序列中的父表，逐步加入序列，直到没有新表可以加入：

DECLARE @AddedCount INT = 1
DECLARE @CurrentLevel INT = 0

WHILE @AddedCount > 0
BEGIN
    SET @CurrentLevel = @CurrentLevel + 1
    
    -- 找出所有子表都已在删除序列中的父表
    INSERT INTO #DeleteOrder
    SELECT DISTINCT
        d.ParentTable AS TableName,
        @CurrentLevel AS [Level]
    FROM #FK_Dependencies d
    WHERE NOT EXISTS (
        SELECT 1 FROM #FK_Dependencies d2 
        WHERE d2.ParentTable = d.ChildTable 
        AND d2.ChildTable NOT IN (SELECT TableName FROM #DeleteOrder)
    )
    AND d.ParentTable NOT IN (SELECT TableName FROM #DeleteOrder)
    
    SET @AddedCount = @@ROWCOUNT
END

5. 处理剩余的循环依赖表 #

如果还有表没加入序列，说明这些表存在循环依赖，需要手动指定顺序（可以按表名或自定义规则加入）：

-- 加入剩余的循环依赖表
INSERT INTO #DeleteOrder
SELECT
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName,
    @CurrentLevel + 1 AS [Level]
FROM sys.tables t
WHERE SCHEMA_NAME(t.schema_id) + '.' + t.name NOT IN (SELECT TableName FROM #DeleteOrder)

6. 查看最终删除顺序 #

SELECT * FROM #DeleteOrder ORDER BY [Level], TableName

-- 生成删除语句示例
SELECT 'DELETE FROM ' + TableName + ';' AS DeleteStatement FROM #DeleteOrder ORDER BY [Level], TableName

7. 清理临时表 #

DROP TABLE #FK_Dependencies
DROP TABLE #DeleteOrder

内容的提问来源于stack exchange，提问作者user23389243