LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon