Simulate DELETE CASCADE I need to delete many rows from one table persons, that is referenced by many other tables as FK.

If I had DELETE CASCADE, I could just delete the records from persons, and automatically delete from the other tables, but I don't like the idea.

I would like a script that takes as input a DELETE statement, and outputs all the DELETE statements that are needed. Ideally, it would tell me how many records would be deleted.

Example:

Input:

DELETE FROM persons WHERE person_id &lt; 1000000


Output:

-- This would delete 124,345 records
DELETE FROM persons_addresses WHERE person_id &lt; 1000000
-- This would delete 82,954 records
DELETE FROM persons_phone numbers WHERE person_id &lt; 1000000
...
-- This would delete 999,999 records
DELETE FROM persons WHERE person_id &lt; 1000000
I'm not aware of such tool, but you can easily create one your self (foreign-keys-in-a-sql-server-database): 

SELECT C.TABLE_CATALOG [PKTABLE_QUALIFIER], 
       C.TABLE_SCHEMA [PKTABLE_OWNER], 
       C.TABLE_NAME [PKTABLE_NAME], 
       KCU.COLUMN_NAME [PKCOLUMN_NAME], 
       C2.TABLE_CATALOG [FKTABLE_QUALIFIER], 
       C2.TABLE_SCHEMA [FKTABLE_OWNER], 
       C2.TABLE_NAME [FKTABLE_NAME], 
       KCU2.COLUMN_NAME [FKCOLUMN_NAME], 
       RC.UPDATE_RULE, 
       RC.DELETE_RULE, 
       C.CONSTRAINT_NAME [FK_NAME], 
       C2.CONSTRAINT_NAME [PK_NAME], 
       CAST(7 AS SMALLINT) [DEFERRABILITY] 
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS C 
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU 
     ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA 
    AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME 
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC 
     ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
    AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2 
     ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA 
    AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME 
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 
     ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA 
    AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME 
    AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION 
WHERE  C.CONSTRAINT_TYPE = 'FOREIGN KEY'
  AND C.TABLE_SCHEMA = ?
  AND C.TABLE_NAME = ? 


will give you all the children of the root table. You can create a recursive CTE that computes all offspring to this table.

As for the number of rows affected, you can construct it like:

SELECT COUNT(1)
FROM (
    SELECT DISTINCT T.*
    FROM T
    WHERE EXISTS (
         SELECT 1
         FROM &lt;JOIN ALL TABLES FROM PARENT OF T TO ROOT&gt;
         WHERE ROOT.PREDICATE
    )
) AS X