Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Casecade Delete in SQL Server
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'
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment