如何修改SQL Server级联删除存储过程以支持自引用表？

以下是适配两种场景的修改后存储过程，保留原多列指向父表单列的支持，同时新增自引用表的级联删除逻辑：

ALTER PROCEDURE dbo.uspCascadeDelete
    @ParentTable NVARCHAR(128),
    @ParentIdColumn NVARCHAR(128),
    @ParentIdValue SQL_VARIANT,
    @DeleteScript NVARCHAR(MAX) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @DeleteScript = N'';

    -- 记录已处理的表，避免循环引用重复生成语句
    DECLARE @ProcessedTables TABLE (TableName NVARCHAR(128) PRIMARY KEY);

    -- 递归遍历所有外键关系（含自引用）
    WITH ForeignKeys AS (
        SELECT 
            fk.name AS ForeignKeyName,
            OBJECT_SCHEMA_NAME(fk.parent_object_id) + '.' + OBJECT_NAME(fk.parent_object_id) AS ChildTable,
            c.name AS ChildColumn,
            OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '.' + OBJECT_NAME(fk.referenced_object_id) AS ParentTable,
            rc.name AS ParentColumn,
            fk.is_disabled,
            fk.is_not_for_replication
        FROM 
            sys.foreign_keys fk
        INNER JOIN 
            sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
        INNER JOIN 
            sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
        INNER JOIN 
            sys.columns rc ON fkc.referenced_object_id = rc.object_id AND fkc.referenced_column_id = rc.column_id
        WHERE 
            OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '.' + OBJECT_NAME(fk.referenced_object_id) = @ParentTable
            AND rc.name = @ParentIdColumn
        UNION ALL
        SELECT 
            fk.name AS ForeignKeyName,
            OBJECT_SCHEMA_NAME(fk.parent_object_id) + '.' + OBJECT_NAME(fk.parent_object_id) AS ChildTable,
            c.name AS ChildColumn,
            OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '.' + OBJECT_NAME(fk.referenced_object_id) AS ParentTable,
            rc.name AS ParentColumn,
            fk.is_disabled,
            fk.is_not_for_replication
        FROM 
            sys.foreign_keys fk
        INNER JOIN 
            sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
        INNER JOIN 
            sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
        INNER JOIN 
            sys.columns rc ON fkc.referenced_object_id = rc.object_id AND fkc.referenced_column_id = rc.column_id
        INNER JOIN 
            ForeignKeys f ON OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '.' + OBJECT_NAME(fk.referenced_object_id) = f.ChildTable
        WHERE 
            OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '.' + OBJECT_NAME(fk.referenced_object_id) NOT IN (SELECT TableName FROM @ProcessedTables)
    )
    -- 递归处理自引用表的层级关系
    , SelfReferencedCTE AS (
        SELECT 
            ChildTable,
            ChildColumn,
            ParentColumn,
            CAST(@ParentIdValue AS NVARCHAR(MAX)) AS IdValue,
            1 AS Level
        FROM 
            ForeignKeys
        WHERE 
            ChildTable = ParentTable -- 筛选出自引用外键
        UNION ALL
        SELECT 
            f.ChildTable,
            f.ChildColumn,
            f.ParentColumn,
            CAST(e.IdValue AS NVARCHAR(MAX)),
            e.Level + 1
        FROM 
            ForeignKeys f
        INNER JOIN 
            SelfReferencedCTE e ON f.ParentTable = e.ChildTable AND f.ParentColumn = e.ParentColumn
        WHERE 
            f.ChildTable = f.ParentTable
    )
    -- 先处理自引用表：从最底层子节点开始删除（按层级倒序）
    INSERT INTO @ProcessedTables (TableName)
    SELECT DISTINCT ChildTable FROM SelfReferencedCTE;

    SELECT @DeleteScript += N'DELETE FROM ' + ChildTable + N' WHERE ' + ChildColumn + N' = ' + QUOTENAME(IdValue, '''') + N';' + CHAR(13) + CHAR(10)
    FROM SelfReferencedCTE
    ORDER BY Level DESC;

    -- 处理非自引用的子表关系（原逻辑保留）
    INSERT INTO @ProcessedTables (TableName)
    SELECT DISTINCT ChildTable FROM ForeignKeys WHERE ChildTable != ParentTable AND ChildTable NOT IN (SELECT TableName FROM @ProcessedTables);

    SELECT @DeleteScript += N'DELETE FROM ' + ChildTable + N' WHERE ' + ChildColumn + N' = ' + QUOTENAME(@ParentIdValue, '''') + N';' + CHAR(13) + CHAR(10)
    FROM ForeignKeys
    WHERE ChildTable != ParentTable AND ChildTable NOT IN (SELECT TableName FROM @ProcessedTables)
    GROUP BY ChildTable, ChildColumn;

    -- 最后删除父表目标记录
    SET @DeleteScript += N'DELETE FROM ' + @ParentTable + N' WHERE ' + @ParentIdColumn + N' = ' + QUOTENAME(@ParentIdValue, '''') + N';';
END

关键修改说明 #

• 自引用层级处理：通过SelfReferencedCTE递归遍历自引用表的所有关联子节点，标记层级确保删除顺序从叶子到根，避免外键约束冲突。
• 重复处理防护：用@ProcessedTables临时表记录已生成删除语句的表，防止自引用循环导致重复操作。
• 兼容原有逻辑：完整保留了原存储过程处理子表多列指向父表单列的多对一关系的逻辑。

使用示例 #

以删除Employee表中Id=1的员工（含其所有下属）为例：

DECLARE @DeleteScript NVARCHAR(MAX);
EXEC dbo.uspCascadeDelete 
    @ParentTable = N'dbo.Employee',
    @ParentIdColumn = N'Id',
    @ParentIdValue = 1,
    @DeleteScript = @DeleteScript OUTPUT;

-- 打印生成的删除语句
PRINT @DeleteScript;

内容的提问来源于stack exchange，提问作者Anand Mohan Singh