Search

Jun 27, 2012

Deleting Parent and its child record without setting ON CASCADE DELETE

Hello All,

Very recent I found requirement for removing parent record which is having tons of relation as they have foreign key constrains; you can’t delete them unless you have ON CASCADE DELETE

I found solution on Stack Overflow and base main article from sqlteam. I have modified procedure to fix some minor issue and here is the procedure which take table name and a query to filter row which we need to delete from table along with its reference.

-- ================================================
-- Expects the name of a table, and a conditional for selecting rows
-- within that table that you want deleted.
-- Produces SQL that, when run, deletes all table rows referencing the ones
-- you initially selected, cascading into any number of tables,
-- without the need for "ON DELETE CASCADE".
-- Does not appear to work with self-referencing tables, but it will
-- delete everything beneath them.
-- To make it easy on the server, put a "GO" statement between each line.
-- ================================================
CREATE PROCEDURE DeleteCascade (
@BaseTableName VARCHAR(200)
,@BaseCriteria VARCHAR(1000)
)
AS BEGIN

DECLARE @ToDelete TABLE
(
Id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
Criteria VARCHAR(5000) NOT NULL,
TableName VARCHAR(200) NOT NULL,
Processed BIT NOT NULL,
DeleteSql VARCHAR(5000)
)

SET NOCOUNT ON

INSERT INTO @ToDelete (Criteria,TableName , Processed)
VALUES (@BaseCriteria, @BaseTableName, 0)

DECLARE @Id INT
DECLARE @Criteria VARCHAR(5000)
DECLARE @TableName VARCHAR(5000)

WHILE EXISTS(SELECT 1 FROM @ToDelete WHERE Processed = 0)
BEGIN

SELECT TOP 1
@Id = Id
,@Criteria = Criteria
,@TableName = TableName
FROM @ToDelete
WHERE Processed = 0
ORDER BY Id DESC

INSERT INTO @ToDelete (Criteria, TableName, Processed)
SELECT
ReferencingColumn.name + ' IN (SELECT [' + ReferencedColumn.name + '] FROM ['
+ @TableName +'] WHERE ' + @Criteria + ')',
ReferencingTable.name,0
FROM sys.foreign_key_columns fk
INNER JOIN sys.columns ReferencingColumn
ON fk.parent_object_id = ReferencingColumn.object_id
AND fk.parent_column_id = ReferencingColumn.column_id
INNER JOIN sys.columns ReferencedColumn
ON fk.referenced_object_id = ReferencedColumn.object_id
AND fk.referenced_column_id = ReferencedColumn.column_id
INNER JOIN sys.objects ReferencingTable
ON fk.parent_object_id = ReferencingTable.object_id
INNER JOIN sys.objects ReferencedTable
ON fk.referenced_object_id = ReferencedTable.object_id
INNER JOIN sys.objects constraint_object
ON fk.constraint_object_id = constraint_object.object_id
WHERE ReferencedTable.name = @TableName
AND ReferencingTable.name != ReferencedTable.name

UPDATE @ToDelete
SET Processed = 1
WHERE Id = @Id

END

SELECT
'PRINT ''Deleting from ' + TableName + '...''; DELETE FROM [' + TableName + '] WHERE '
+ Criteria
FROM @ToDelete
ORDER BY Id DESC

END



When you execute this, you will see list of DELETE statement, which you need to run.

3 comments:

Maulik Dhorajia said...

Great post!

Unknown said...

Well done! This will save me a ton of time. thanks!

Unknown said...

Well done! This will save me a ton of time. Thanks!