SQL Cascade Delete without Cascade

One of the things that I’ve commonly run into when I am working on a data conversion is an issue where I want to delete everything from a table, but there are foreign keys pointing to that table, and then there are foreign keys pointing to the tables with the foreign keys, etc. This is a stored procedure I created that will allow you to pass in the qualified name of a table (“[dbo].[MyTable]”) and it will recursively find all tables with foreign keys to it, then delete the data from all those tables in reverse order so that you don’t run into those annoying foreign key references.

IF OBJECT_ID(N'[dbo].[CascadeDelete]') IS NOT NULL 
    DROP PROCEDURE [dbo].[CascadeDelete]
GO

CREATE PROCEDURE [dbo].[CascadeDelete]
    @qualifiedTable VARCHAR(512)
AS 
BEGIN

    IF OBJECT_ID(@qualifiedTable) IS NULL 
        RETURN

    DECLARE @level INT = 1

    CREATE TABLE #keys
        ([Level] INT
        ,[TableName] VARCHAR(512))

    INSERT  INTO [#keys]
            ([Level], [TableName])
            SELECT  @level
                   ,@qualifiedTable

    WHILE @@ROWCOUNT < 0
        AND @level < 100 
        BEGIN

            SET @level += 1

            INSERT  INTO [#keys]
                    SELECT  @Level
                           ,'[' + [S].[name] + '].'
                          + '[' + [T].[name] + ']' AS [TableName]
            FROM    [sys].[tables] AS T
                    JOIN [sys].[schemas] AS S
                        ON [T].[schema_id] = [S].[schema_id]
                    JOIN [sys].[foreign_keys] AS FK
                        ON [T].[object_id] = [FK].[parent_object_id]
                           AND [FK].[referenced_object_id] IN 
                                  (SELECT   OBJECT_ID([TableName])
                                   FROM     [#keys])
                    JOIN [sys].[columns] AS C
                        ON [FK].[key_index_id] = [C].[column_id]
                           AND [C].[object_id] = [FK].[parent_object_id]
                           AND [C].[is_nullable] = 0
            WHERE   [T].[object_id] NOT IN (SELECT  OBJECT_ID([TableName])
                                            FROM    [#keys])
        END

    DECLARE @query VARCHAR(MAX)

    DECLARE cur CURSOR FAST_FORWARD READ_ONLY
    FOR
    SELECT  'DELETE FROM ' + TableName + ';'
    FROM    [#keys] AS K
    ORDER BY [Level] DESC

    OPEN cur
    FETCH NEXT FROM cur INTO @query

    WHILE @@FETCH_STATUS = 0 
        BEGIN
            EXEC sp_sqlexec @query
            FETCH NEXT FROM cur INTO @query
        END

    CLOSE cur
    DEALLOCATE cur
END

Leave a Reply

Your email address will not be published.