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