Simulate a cascading delete before removing rows in SQL Server
T-SQL procedure that generates the DELETE statements for the child tables of a parent row and counts the affected rows, without deleting anything.
When a foreign key has no ON DELETE CASCADE, the child rows must be deleted first. The procedure below builds the DELETE statements that would remove one parent row and its direct child rows, and counts the rows each statement would delete. It does not delete anything: it returns the statements as rows, and you run them yourself.
Procedure
CREATE PROCEDURE dbo.GenerateCascadingDeletes
@ParentTable nvarchar(512),
@ParentColumn sysname,
@DeleteCriteria nvarchar(255)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql nvarchar(max) = N'',
@src nvarchar(max) = N'SELECT ''DELETE $t$ WHERE $c$ $clause$;'' UNION ALL SELECT
''-- This would delete '' + (SELECT RTRIM(COUNT(*)) FROM $t$ WHERE $c$ $clause$)
+ '' rows.'';';
-- generate deletes for child tables:
SELECT @sql = @sql
+ REPLACE(REPLACE(REPLACE(@src,N'$t$',t),N'$c$',c),N'$clause$',@DeleteCriteria)
FROM
(
SELECT t = QUOTENAME(OBJECT_SCHEMA_NAME(pt.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(pt.parent_object_id)),
c = QUOTENAME(pc.name)
FROM sys.foreign_key_columns AS pt
INNER JOIN sys.columns AS pc
ON pt.parent_object_id = pc.[object_id]
AND pt.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON pt.referenced_column_id = rc.column_id
AND pt.referenced_object_id = rc.[object_id]
WHERE pt.referenced_object_id = OBJECT_ID(@ParentTable)
AND rc.name = @ParentColumn
) AS x;
-- final delete of parent table:
SELECT @sql += REPLACE(REPLACE(REPLACE(@src,N'$t$',@ParentTable),
N'$c$',@ParentColumn),N'$clause$',@DeleteCriteria);
EXEC sys.sp_executesql @sql;
END
GO
Usage
exec dbo.GenerateCascadingDeletes '[app].ParentTable', 'ParentId', '= 1001'
select ChildId, ParentId from app.ChildTable where ParentId = 1001
exec dbo.GenerateCascadingDeletes 'app.ChildTable', 'ChildId', '= 2001'
exec dbo.GenerateCascadingDeletes 'app.ChildTable', 'ChildId', '= 2002'
The result set lists, for each child table, a DELETE statement preceded by a comment with the row count, and then the DELETE for the parent table. Run the statements in the order returned: children first, parent last.
Notes
- Only the tables with a foreign key that references
@ParentTabledirectly are included. Tables further down the chain are not listed. - The criteria in
@DeleteCriteriais inserted into the generated text without any check. Pass only trusted values. - The row counts are computed when the procedure runs. Run the statements soon after generating them, or the counts can be out of date.