1

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 < 1000000

Output:

-- This would delete 124,345 records
DELETE FROM persons_addresses WHERE person_id < 1000000
-- This would delete 82,954 records
DELETE FROM persons_phone numbers WHERE person_id < 1000000
...
-- This would delete 999,999 records
DELETE FROM persons WHERE person_id < 1000000
  • Why can't you use SELECT FROM persons WHERE person_id < 1000000 – James Jenkins Sep 9 '19 at 16:46
  • My goal is to delete records, a FK pointing to person can prevent me from doing it. SELECT will not help, I need all the DELETE from the referencing tables. – carlo.borreo Sep 9 '19 at 16:48
  • Can the input be provided as strings, like '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? – Aaron Bertrand Sep 9 '19 at 20:13
  • Of course it is provided as string. And in my database, I try to keep column names unique as much as possible, so child and parent have for sure different names. – carlo.borreo Sep 9 '19 at 20:46
2

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.
|improve this answer|||||
2

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 <JOIN ALL TABLES FROM PARENT OF T TO ROOT>
         WHERE ROOT.PREDICATE
    )
) AS X
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.