The information required to generate the queries and the counts is all available in the catalog views, like sys.columns
and 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
@ParentTable nvarchar(512),
@ParentColumn nvarchar(128),
@DeleteCriteria nvarchar(255)
AS
BEGIN
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)
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
Sample usage:
EXEC dbo.GeneratedPathedDeletes
@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;
Limitations:
- 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.
'dbo.persons'
and'where person_id < 1000000'
? What about the case where the column name involved in the foreign key is different between child and parent?