SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Performing a Cascade Delete in SQL Server 7
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

NareshMittal
Starting Member

Australia
1 Posts

Posted - 09/20/2010 :  21:24:19  Show Profile  Reply with Quote
Here is the update SProc for cascade delete when the database has multiple schemas-

CREATE PROCEDURE [dbo].[spCascadeDeleteLong]
(
-- this cascade deleter uses temporary tables to avoid deep nesting issues
-- requires that tables have one primary key, and its type is int
@cTableName varchar(75), -- name of the table where rows are to be deleted
@cCriteria varchar(255), -- criteria used to delete the rows required
@bDeleteTopHierarchy int, -- delete top hierarchy?
@iLevel int = 0 -- execution level (do not pass this argument)
)
AS
set nocount on
declare
@cTab varchar(255), -- name of the child table
@cCol varchar(255), -- name of the linking field on the child table
@cRefTab varchar(255), -- name of the parent table
@cRefCol varchar(255), -- name of the linking field in the parent table
@cFKName varchar(255), /* name of the foreign key */
@cSQL varchar(255), -- query string passed to the sp_ExecuteSQL procedure
@cChildCriteria varchar(255), -- criteria to be used to delete records from the child table
@iLevelNew int -- new level (for recursive calling)

-- prepare the temporary table holding the pk values of the called level
IF @iLevel = 0
BEGIN
-- build the temporary table
Create Table #tblCascadeDelete (CallLevel int NOT NULL, PKValue int NOT NULL)
END


-- declare the cursor containing the foreign key constraint information
DECLARE cFKey CURSOR LOCAL FOR
select ccu.CONSTRAINT_SCHEMA+'.'+ccu.TABLE_NAME as Tab, ccu.COLUMN_NAME as Col,
tc.CONSTRAINT_SCHEMA+'.'+tc.TABLE_NAME as RefTab, kcu.COLUMN_NAME as RefCol,
rc.CONSTRAINT_NAME as FKName
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on rc.UNIQUE_CONSTRAINT_NAME = tc.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu on ccu.CONSTRAINT_NAME=rc.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
where tc.CONSTRAINT_SCHEMA+'.'+tc.TABLE_NAME=@cTableName

OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName

-- if an initial fetch was successful, then add the appropriate PK values to the temporary table
IF @@FETCH_STATUS = 0
BEGIN
SET @cSQL = 'INSERT INTO #tblCascadeDelete ( CallLevel, PKValue ) SELECT ' + Convert(varchar(3), @iLevel) + ' As CallLevel, ' + @cRefCol + ' As PKValue FROM ' + @cTableName + ' WHERE ' + @cCriteria
EXEC (@cSQL)
END

-- only recurse if rows inserted
IF @@RowCount > 0
BEGIN
WHILE @@FETCH_STATUS = 0
BEGIN
-- build the criteria to pass on for the next table
SET @cChildCriteria = @cCol + ' IN (SELECT PKValue FROM #tblCascadeDelete Where CallLevel = ' + Convert(varchar(3), @iLevel) + ')'

-- call this procedure to delete the child rows
SET @iLevelNew = @iLevel + 1
EXEC dbo.spCascadeDeleteLong @cTab, @cChildCriteria, 1, @iLevelNew

-- return the next values
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
END
END

CLOSE cFKey
DEALLOCATE cFKey

-- delete the rows from this table
IF @bDeleteTopHierarchy <> 0
BEGIN
SET @cSQL = 'DELETE FROM ' + @cTableName + ' WHERE ' + @cCriteria
EXEC (@cSQL)
Print @csql + ' (Rows deleted = ' + CAST(@@ROWCOUNT as varchar) + ')'
END

-- drop the temporary table if top level, otherwise remove values from this level to allow reuse
IF @iLevel = 0
BEGIN
Drop Table #tblCascadeDelete
END
ELSE
BEGIN
SET @cSQL = 'DELETE FROM #tblCascadeDelete WHERE CallLevel = ' + Convert(varchar(3), @iLevel)
EXEC (@cSQL)
END
Go to Top of Page

lmoody
Starting Member

1 Posts

Posted - 09/30/2010 :  13:40:29  Show Profile  Reply with Quote
Can this be modified to exclude tables? I want to delete a Report Header record and all the Report Detail records, but the Report Detail record has an Employee ID foreign key. I don't want to delete all employees :)

Ideas?
Go to Top of Page

foobar11
Starting Member

2 Posts

Posted - 01/25/2011 :  19:00:36  Show Profile  Reply with Quote
quote:
Originally posted by foobar11

Also, by casting the pk value to varchar (instead of int) I was able to mix all kinds of primary key types (guids, etc).



I should add that casting to varchar will prevent index usage, hence is not really recommended on large tables.
Go to Top of Page

lionofdezert
Aged Yak Warrior

Pakistan
885 Posts

Posted - 03/17/2011 :  08:44:03  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote

Daniel here is your code after minor changes, to control schema name problems
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
-- writen by Daniel Crowther 17 Dec 2004 primarily to support uspCascadeDelete (makes it more readable!!!)
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

-- writen by Daniel Crowther 16 Dec 2004 primarily to support uspCascadeDelete (makes it more readable!!!)
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

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





*/







--------------------------
http://connectsql.blogspot.com/
Go to Top of Page

jaanazam
Starting Member

India
5 Posts

Posted - 07/27/2011 :  10:55:12  Show Profile  Reply with Quote
quote:
Originally posted by foobar11

GreySky,

thanks, your solution really got me going (the original one might fail due to too many nested subqueries). I added a "tablename"-column to the temptable, so cycles can be detected when setting the child-criteria (a simple subquery checking whether the current row has already been picked up on a lower call-level). Also, by casting the pk value to varchar (instead of int) I was able to mix all kinds of primary key types (guids, etc).



Hi

i need to delete the records in child tables which references the id of the parent table.
i used the above sp : "spCascadeDeleteLong".
it runs good but giving the following error due to nested levels exceeding 32 could you please paste the new version of code which solves the problem of nested levels as iam using the SQL2005

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).


its very urgent i need to solve this issue.
expecting the quick response from you.

thanks in advance

Azam.
Go to Top of Page

tomero
Starting Member

4 Posts

Posted - 04/05/2012 :  11:20:36  Show Profile  Reply with Quote
Probably your problem was long ago solved but for future viewers:

I had some circular FK references which overflowed the 32 nested level of FK references allowed by SQL server. (obviously)
As I fear this is the case for many users on a big product DB I thought it might be useful to post it here.

What I did was to run the sproc in :
http://blogs.msdn.com/b/sqlazure/archive/2010/07/01/10033575.aspx
which checks for circular FK references.

Than I decided which are the FK in each circle that I need to break (you might check to see the one with the least amount of rows).
Self references are not handled anyway so no point in deleting those.

Then I ran the sproc mentioned in this topic successfully (finally)
And all that remains is to delete the orphaned records due to the FK being deleted and then to enable the FKs again.

This small query did the trick:
SELECT * FROM <foreign_key_table> WHERE <foreign_key_column> NOT IN (SELECT <primary_key_column> FROM <primary_key_table>);

Hope that helps someone and thanks for all the help.
Tomer.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.18 seconds. Powered By: Snitz Forums 2000