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.

SQL Challenge - Find second highest salary by department

Hello All,

I am starting this new thread called SQL Challenges, which will have SQL Challenge for beginners. I hope your all like that and participate also.

Jacob Sebastian, had already started posting challenges, I would like you to participate into challenge, may be you can get gift or rewards.

There are some rules and regulation which you have to read before posting any reply, it's very simple not a big deal. So first challenge is out there, to finding out second highest salary by department, please visit this link for more details. You can find scripts to generate data too.

Hope you will have great time to solving and most important thing is learning; you will learn lot not only from question also from the best answer posted for particular challenge.