Início / dba / Perguntas / 247366
Accepted
carlo.borreo
Asked: 2019-09-10 08:42:33 +0800 CST

Simular DELETE CASCADE

  • 772

Eu preciso excluir muitas linhas de uma tabela pessoas, que é referenciada por muitas outras tabelas como FK.

Se eu tivesse DELETE CASCADE, poderia simplesmente deletar os registros das pessoas, e deletar automaticamente das outras tabelas, mas não gosto da ideia.

Eu gostaria de um script que recebesse como entrada uma instrução DELETE e gerasse todas as instruções DELETE necessárias. Idealmente, ele me diria quantos registros seriam excluídos.

Exemplo:

Entrada:

DELETE FROM persons WHERE person_id < 1000000

Resultado:

-- 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
sql-server sql-server-2016

2 respostas

  1. Não conheço essa ferramenta, mas você pode facilmente criar uma por conta própria ( chaves estrangeiras em um banco de dados de servidor sql ):

    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 = ?

    lhe dará todos os filhos da tabela raiz. Você pode criar um CTE recursivo que calcula todos os descendentes dessa tabela.

    Quanto ao número de linhas afetadas, você pode construí-lo como:

    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
    • 2
  2. Best Answer

    As informações necessárias para gerar as consultas e as contagens estão todas disponíveis nas visualizações do catálogo, como sys.columnse sys.foreign_key_columns. Precisamos encontrar todas as tabelas filhas e contar quantas linhas em cada tabela filha atendem aos mesmos critérios do ID pai.

    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

    Exemplo de uso:

    EXEC dbo.GeneratedPathedDeletes
     @ParentTable    = N'dbo.persons',
     @ParentColumn   = N'person_id',
     @DeleteCriteria = N' < 100000';

    Minhas tabelas de amostra eram muito menores, mas minha saída ficou assim:

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

    Limitações:

    • Isso não executa as exclusões - você ainda precisa executá-las manualmente com base na saída.
    • Isso não lida com caminhos cíclicos ou netos; apenas tradicional pai-filho.
    • Isso não lida com chaves de várias colunas ou, provavelmente, chaves de auto-referência (nem tentei).
    • Cláusulas mais complexas, como uma cláusula IN ou vários intervalos, exigirão mais trabalho.
    • Isso não é seguro para SQL Injection - se você estiver aceitando armas de usuários e passando-as para este procedimento, veja minhas dicas parte 1 e parte 2 .
    • 2

relate perguntas