Casecade Delete in SQL Server

CasecadeDelete.sql

USE AdventureWorks

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 --OBJECT_ID('FK_ProductArrearsMe_ProductArrears')
    RETURN LEFT(@str, LEN(@str) - LEN(' AND '))
   END
GO
--=========== CASECADE 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, 
--'N' IF YOU NEED DELETE SCRIPT
    @FromClause VARCHAR(8000) = '', -- IF LEVEL 0, THEN KEEP DEFAULT
    @Level INTEGER = 0
AS -- writen by Daniel Crowther 16 Dec 2004 - handles composite primary keys

    SET NOCOUNT ON

/* Set up debug */

    DECLARE @DebugMsg VARCHAR(4000),
        @DebugIndent VARCHAR(50)

    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 
        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 ***'

        END

    DECLARE @CRLF CHAR(2)

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

    DECLARE @strSQL VARCHAR(4000)

    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 --, @cColId, @pTableId, @pColId

    DECLARE @strFromClause VARCHAR(1000)

    DECLARE @nLevel INTEGER

    IF @Level = 0 
        BEGIN

            SET @FromClause = 'FROM ' + dbo.udfGetFullQualName(@ParentTableId)

        END

    WHILE @@FETCH_STATUS = 0

        BEGIN

            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

            FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId 

--, @cColId, @pTableId, @pColId

        END

    IF @Level = 0 
        BEGIN

            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

        END

    CLOSE curs_children

    DEALLOCATE curs_children

GO

/*

-- Example 1

EXEC uspCascadeDelete

@ParentTableId = 'Production.Location',

@WhereClause = 'Location.LocationID = 2'

--    ,@ExecuteDelete = 'Y'

-- Example 2

EXEC uspCascadeDelete

@ParentTableId = 'dbo.brand',

@WhereClause = 'brand.brand_name <> ''Apple'''

--    ,@ExecuteDelete = 'Y'

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'

*/