The information required to generate the queries and the counts is all available in the catalog views, like
sys.foreign_key_columns. We need to find all of the child tables, and then count how many rows in each child table meet the same criteria as the parent ID.
CREATE PROCEDURE dbo.GeneratedPathedDeletes
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.'';';
SELECT @sql += REPLACE(REPLACE(REPLACE(@src,N'$t$',t),N'$c$',c),N'$clause$',@DeleteCriteria)
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),
EXEC sys.sp_executesql @sql;
@ParentTable = N'dbo.persons',
@ParentColumn = N'person_id',
@DeleteCriteria = N' < 100000';
My sample tables were much smaller, but my output looked like this:
-- This would delete 12 rows.
DELETE dbo.persons_addresses WHERE person_id < 1000000;
-- This would delete 4 rows.
DELETE dbo.persons_phone_numbers WHERE person_id < 1000000;
-- This would delete 2 rows.
DELETE dbo.persons WHERE person_id < 1000000;
- This doesn't perform the deletes - you still have to run those manually based on the output.
- This doesn't handle cyclic paths or grandchildren; only traditional parent-child.
- This doesn't handle multi-column keys or, probably, self-referencing keys (didn't even try).
- More complex clauses, like an IN clause, or multiple ranges, will require more work.
- This isn't secure from SQL Injection - if you're accepting weapons from users and passing them into this procedure, please see my tips part 1 and part 2.