Cascade Delete in SQL Server

CascadeDelete.sql

/*

Originally written by Daniel Crowther 16 Dec 2004.

Addresses the recursive deletion of child table entries, even those not directly related to the parent table, before ultimately attempting to delete from the parent table.

Passing 'Y' to @ExecuteDelete will run the deletion & wrap everything in a transaction 

Passing 'Y' to @TrialRun rolls back the transaction after executing all the delete statements. 

Prints out how long each delete statement took to execute in seconds

-- Example 1

EXEC uspCascadeDelete
@ParentTableId = 'Product',
@WhereClause = 'Product.Id = ''3ddc5962-84b8-4ca4-85df-3ddd17aa538d''',
@ExecuteDelete = 'Y',
@TrialRun = 'Y'

-- Example 2
EXEC uspCascadeDelete
@ParentTableId = 'dbo.brand',
@WhereClause = 'brand.brand_name <> ''Apple''',
@ExecuteDelete = 'N'

-- Example 3
exec uspCascadeDelete
@ParentTableId = 'dbo.product_type',
@WhereClause = 'product_type.product_type_id NOT IN 
(SELECT bpt.product_type_id FROM dbo.brand_product_type bpt)',
@ExecuteDelete = 'Y'

*/

GO

--============== Supporting function dbo.udfGetFullQualName
IF OBJECT_ID('dbo.udfGetFullQualName') IS NOT NULL 
    DROP FUNCTION dbo.udfGetFullQualName
GO
CREATE FUNCTION dbo.udfGetFullQualName ( @ObjectId INTEGER )
RETURNS VARCHAR(300)
AS BEGIN 
    DECLARE @schema_id BIGINT
    SELECT  @schema_id = schema_id
    FROM    sys.tables
    WHERE   object_id = @ObjectId
    RETURN '[' + SCHEMA_NAME(@schema_id) + '].[' + OBJECT_NAME(@ObjectId) + ']' 
   END 
GO
--============ Supporting Function dbo.udfGetOnJoinClause
IF OBJECT_ID('dbo.udfGetOnJoinClause') IS NOT NULL 
    DROP FUNCTION dbo.udfGetOnJoinClause
GO
CREATE FUNCTION dbo.udfGetOnJoinClause ( @fkNameId INTEGER )
RETURNS VARCHAR(1000)
AS BEGIN
    DECLARE @OnClauseTemplate VARCHAR(1000)
    SET @OnClauseTemplate = '[<@pTable>].[<@pCol>] = [<@cTable>].[<@cCol>] AND '
    DECLARE @str VARCHAR(1000)
    SET @str = ''
    SELECT  @str = @str + REPLACE(REPLACE(REPLACE(REPLACE(@OnClauseTemplate,
                                                          '<@pTable>',
                                                          OBJECT_NAME(rkeyid)),
                                                  '<@pCol>',
                                                  COL_NAME(rkeyid, rkey)),
                                          '<@cTable>', OBJECT_NAME(fkeyid)),
                                  '<@cCol>', COL_NAME(fkeyid, fkey))
    FROM    dbo.sysforeignkeys fk
    WHERE   fk.constid = @fkNameId
    RETURN LEFT(@str, LEN(@str) - LEN(' AND '))
   END
GO
--=========== CASCADE DELETE STORED PROCEDURE dbo.uspCascadeDelete
IF OBJECT_ID('dbo.uspCascadeDelete') IS NOT NULL 
    DROP PROCEDURE dbo.uspCascadeDelete
GO
CREATE PROCEDURE dbo.uspCascadeDelete
    @ParentTableId VARCHAR(300), -- TABLE NAME OR OBJECT (TABLE) ID (Production.Location)
    @WhereClause VARCHAR(2000), -- WHERE CLAUSE (Location.LocationID = 7)
    @ExecuteDelete CHAR(1) = 'N', -- 'Y' IF WANT TO DELETE DIRECTLY FROM SP
    @TrialRun CHAR(1) = 'N', -- 'Y' IF WANT TO EXECUTE A TRAIL RUN AND GET THE OUTPUT WITHOUT MAKING ANY CHANGES. 'N' IF YOU NEED DELETE SCRIPT
    @FromClause VARCHAR(8000) = '', -- IF LEVEL 0, THEN KEEP DEFAULT
    @Level INTEGER = 0
AS 
	SET NOCOUNT ON

	/* Set up debug */

    DECLARE @DebugMsg VARCHAR(4000),
        @DebugIndent VARCHAR(50),
	@start_time DATETIME,
        @end_time DATETIME;

    SET @DebugIndent = REPLICATE('---', @@NESTLEVEL) + '> '

    IF ISNUMERIC(@ParentTableId) = 0 
        BEGIN -- assume owner is dbo and calculate id

            IF CHARINDEX('.', @ParentTableId) = 0 
                SET @ParentTableId = OBJECT_ID('[dbo].[' + @ParentTableId + ']')
				
            ELSE 
                SET @ParentTableId = OBJECT_ID(@ParentTableId)
				
        END

    IF @Level = 0 AND @ExecuteDelete = 'Y'
        BEGIN TRANSACTION

    IF @Level = 0 
        BEGIN

            PRINT @DebugIndent
                + ' **************************************************************************'

            PRINT @DebugIndent + ' *** Cascade delete ALL data from '
                + dbo.udfGetFullQualName(@ParentTableId)

            IF @ExecuteDelete = 'Y' 
                PRINT @DebugIndent
                    + ' *** @ExecuteDelete = Y *** deleting data'										
            ELSE 
                PRINT @DebugIndent
                    + ' *** Cut and paste output into another window and execute ***'

            IF @TrialRun = 'Y' 
                PRINT @DebugIndent
                    + ' *** @TrialRun = Y *** TRIAL RUN ONLY'

        END

    DECLARE @CRLF CHAR(2)

    SET @CRLF = CHAR(13) + CHAR(10)

    DECLARE @strSQL VARCHAR(8000)

    IF @Level = 0 
        SET @strSQL = 'SET NOCOUNT ON' + @CRLF

    ELSE 
        SET @strSQL = ''

    SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent
        + dbo.udfGetFullQualName(@ParentTableId) + ' Level='
        + CAST(@@NESTLEVEL AS VARCHAR) + ''''

    IF @ExecuteDelete = 'Y' 
        EXEC ( @strSQL )

    ELSE 
        PRINT @strSQL

    DECLARE curs_children CURSOR LOCAL FORWARD_ONLY
        FOR SELECT DISTINCT
                    fkNameId = constid,   -- constraint name
                    cTableId = fkeyid     -- child table
            FROM    dbo.sysforeignkeys fk
            WHERE   fk.rkeyid <> fk.fkeyid -- WE DO NOT HANDLE self referencing tables!!!
                    AND fk.rkeyid = @ParentTableId

    OPEN curs_children

    DECLARE @fkNameId INTEGER,
        @cTableId INTEGER,
        @cColId INTEGER,
        @pTableId INTEGER,
        @pColId INTEGER

    FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId

    DECLARE @strFromClause VARCHAR(1000)

    DECLARE @nLevel INTEGER

    IF @Level = 0 
        BEGIN
            SET @FromClause = 'FROM ' + dbo.udfGetFullQualName(@ParentTableId)
        END

    WHILE @@FETCH_STATUS = 0

        BEGIN
		
			SET @start_time = GETDATE();

            SELECT  @strFromClause = @FromClause + @CRLF + '      INNER JOIN '
                    + dbo.udfGetFullQualName(@cTableId) + @CRLF + '       ON '
                    + dbo.udfGetOnJoinClause(@fkNameId)

            SET @nLevel = @Level + 1

            EXEC dbo.uspCascadeDelete @ParentTableId = @cTableId,
                @WhereClause = @WhereClause, @ExecuteDelete = @ExecuteDelete,
                @FromClause = @strFromClause, @Level = @nLevel

            SET @strSQL = 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId)
                + @CRLF + @strFromClause + @CRLF + 'WHERE   ' + @WhereClause
                + @CRLF

            SET @strSQL = @strSQL + 'PRINT ''---' + @DebugIndent
                + 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId)
                + '     Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)'
                + @CRLF + @CRLF

            IF @ExecuteDelete = 'Y' 
                EXEC ( @strSQL )
					
            ELSE 
                PRINT @strSQL
				
			SET @end_time = GETDATE();
   			PRINT '------> Time taken: ' + CAST(DATEDIFF(SECOND, @start_time, @end_time) AS VARCHAR) + ' s';

            FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId 

        END

    IF @Level = 0 
        BEGIN
		
			SET @start_time = GETDATE();

            SET @strSQL = @CRLF + 'PRINT ''' + @DebugIndent
                + dbo.udfGetFullQualName(@ParentTableId) + ' Level='
                + CAST(@@NESTLEVEL AS VARCHAR) + ' TOP LEVEL PARENT TABLE'''
                + @CRLF

            SET @strSQL = @strSQL + 'DELETE FROM '
                + dbo.udfGetFullQualName(@ParentTableId) + ' WHERE '
                + @WhereClause + @CRLF

            SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + 'DELETE FROM '
                + dbo.udfGetFullQualName(@ParentTableId)
                + ' Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF

            IF @ExecuteDelete = 'Y' 
                EXEC ( @strSQL )

            ELSE 
                PRINT @strSQL
				
			SET @end_time = GETDATE();
    		PRINT '------> Time taken: ' + CAST(DATEDIFF(SECOND, @start_time, @end_time) AS VARCHAR) + ' s';
		
		    IF @Level = 0 AND @ExecuteDelete = 'Y' AND @TrialRun = 'N'
		        COMMIT TRANSACTION
		    ELSE IF @Level = 0 AND @ExecuteDelete = 'Y' AND @TrialRun = 'Y'
		    	ROLLBACK TRANSACTION

        END

    CLOSE curs_children

    DEALLOCATE curs_children
	
GO