| Author |
 Topic  |
|
NareshMittal
Starting Member
Australia
1 Posts |
 Posted - 09/20/2010 : 21:24:19
|
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
|
 |
|
|
lmoody
Starting Member
1 Posts |
Posted - 09/30/2010 : 13:40:29
|
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? |
|
|
|
foobar11
Starting Member
2 Posts |
Posted - 01/25/2011 : 19:00:36
|
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. |
|
|
|
lionofdezert
Aged Yak Warrior
Pakistan
864 Posts |
Posted - 03/17/2011 : 08:44:03
|
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/ |
|
|
|
jaanazam
Starting Member
India
5 Posts |
Posted - 07/27/2011 : 10:55:12
|
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. |
|
|
|
tomero
Starting Member
4 Posts |
Posted - 04/05/2012 : 11:20:36
|
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.
|
|
|
|
Topic |
|
|
|
Article: Performing a Cascade Delete in SQL Server 7
