Deleting Parent and its child record without setting ON CASCADE DELETE

Hello All,

Very recent I found requirement for removing parent record which is having tons of relation as they have foreign key constrains; you can’t delete them unless you have ON CASCADE DELETE

I found solution on Stack Overflow and base main article from sqlteam. I have modified procedure to fix some minor issue and here is the procedure which take table name and a query to filter row which we need to delete from table along with its reference.

-- ================================================
-- Expects the name of a table, and a conditional for selecting rows
-- within that table that you want deleted.
-- Produces SQL that, when run, deletes all table rows referencing the ones
-- you initially selected, cascading into any number of tables,
-- without the need for "ON DELETE CASCADE".
-- Does not appear to work with self-referencing tables, but it will
-- delete everything beneath them.
-- To make it easy on the server, put a "GO" statement between each line.
-- ================================================
CREATE PROCEDURE DeleteCascade (
     @BaseTableName VARCHAR(200)
    ,@BaseCriteria VARCHAR(1000)
)
AS BEGIN

    DECLARE @ToDelete TABLE 
    (
        Id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
        Criteria VARCHAR(5000) NOT NULL,
        TableName VARCHAR(200) NOT NULL,
        Processed BIT NOT NULL,
        DeleteSql VARCHAR(5000)
    )

    SET NOCOUNT ON

    INSERT INTO @ToDelete (Criteria,TableName , Processed)
    VALUES (@BaseCriteria, @BaseTableName, 0)

    DECLARE @Id INT
    DECLARE @Criteria VARCHAR(5000)
    DECLARE @TableName VARCHAR(5000)

    WHILE EXISTS(SELECT 1 FROM @ToDelete WHERE Processed = 0) 
    BEGIN
    
        SELECT TOP 1 
            @Id = Id
            ,@Criteria = Criteria
            ,@TableName = TableName 
        FROM @ToDelete 
        WHERE Processed = 0 
        ORDER BY Id DESC

        INSERT INTO @ToDelete (Criteria, TableName, Processed)
        SELECT 
            ReferencingColumn.name + ' IN (SELECT [' + ReferencedColumn.name + '] FROM [' 
+ @TableName +'] WHERE ' + @Criteria + ')',
            ReferencingTable.name,0
        FROM sys.foreign_key_columns fk
        INNER JOIN sys.columns ReferencingColumn 
            ON fk.parent_object_id = ReferencingColumn.object_id 
            AND fk.parent_column_id = ReferencingColumn.column_id 
        INNER JOIN sys.columns ReferencedColumn 
            ON fk.referenced_object_id = ReferencedColumn.object_id 
            AND fk.referenced_column_id = ReferencedColumn.column_id 
        INNER JOIN sys.objects ReferencingTable 
            ON fk.parent_object_id = ReferencingTable.object_id 
        INNER JOIN sys.objects ReferencedTable 
            ON fk.referenced_object_id = ReferencedTable.object_id 
        INNER JOIN sys.objects constraint_object 
            ON fk.constraint_object_id = constraint_object.object_id
        WHERE ReferencedTable.name = @TableName
        AND ReferencingTable.name != ReferencedTable.name

        UPDATE @ToDelete 
        SET Processed = 1
        WHERE Id = @Id
        
    END
    
    SELECT 
        'PRINT ''Deleting from ' + TableName + '...''; DELETE FROM [' + TableName + '] WHERE ' 
+ Criteria 
    FROM @ToDelete 
    ORDER BY Id DESC
    
END

When you execute this, you will see list of DELETE statement, which you need to run.