Thursday, March 17, 2011

SQL Server: Cascade Delete


To delete a record, with all of its reference records, in other tables is not an easy task, especially when database has dozens of tables and you have not designed your tables with cascade delete option. Then cascade delete in SQL Server is only possible by writing your own script.
Here is a script, which I like to use for such cascade deletes. Script is originally written by Danial Crowther. I just made few changes as script was created for older versions.

Download Script

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

7 comments:

  1. If you have access to modify the database schema (some dba's won't give a developer access, with good reason), check out the relationships dialog for your related tables. Under the non-obvious heading of "INSERT and Update Specifications", there is a "Delete Rule" option. Select "Cascade".

    ReplyDelete
  2. Actually SQL Server has 'ON DELETE CASCADE'. Which works fairly well for cascade deletes as long as you set your database up correctly. Then again, maybe I'm missing the point of the post (I admit that I didn't go into too much depth on the code).

    ReplyDelete
  3. Cant you set the cascade delete in studio manager when defining your relationships?

    ReplyDelete
  4. Hi,
    I've tried to to run the above function and got the following error:

    Msg 217, Level 16, State 1, Procedure uspCascadeDelete, Line 42
    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

    ReplyDelete
  5. This is good stuff !
    Better than existing SO answers, in fact:
    http://stackoverflow.com/questions/485581/generate-delete-statement-from-foreign-key-relationships-in-sql-2008

    ReplyDelete
  6. Hi,

    ---Your SPs output
    DELETE Top(100000) [dbo].[InputDevice_PracticeEvent]
    FROM [dbo].[Practice]
    INNER JOIN [dbo].[InputDevice_Practice] ON [Practice].[PracticeID] = [InputDevice_Practice].[PracticeID]
    INNER JOIN [dbo].[InputDevice_PracticeEvent] ON [InputDevice_Practice].[InputDevice_PracticeID] = [InputDevice_PracticeEvent].[InputDevice_PracticeID]
    WHERE [Practice].PracticeID = '55';

    ---What I want
    DELETE TOP ( XXXXX ) [dbo].[InputDevice_PracticeEvent]
    FROM [dbo].[InputDevice_PracticeEvent]
    INNER JOIN [dbo].[InputDevice_Practice] on [dbo].[InputDevice_Practice].inputDevice_practiceID = [InputDevice_PracticeEvent].inputdevice_practiceID
    INNER JOIN [dbo].[practice] on [practice].practiceID = [InputDevice_Practice].practiceID
    WHERE [Practice].PracticeID = '55';

    How do I modify the SP?

    ReplyDelete
  7. hi thks for this post. I'm has a problem: "in the FROM clause have the same exposed names. Use correlation names to distinguish them." how include 'alias' in the procedure? thks for help

    ReplyDelete

All suggestions are welcome