DELETE CASCADE SQL

13 de agosto de 2021   |   by administrador

CRIE ESTA PROCEDURE

CREATE PROCEDURE   dbo.GenerateCascadingDeletes
  @ParentTable     nvarchar(512),
  @ParentColumn    sysname,
  @DeleteCriteria  nvarchar(255)
AS
BEGIN
  SET NOCOUNT ON;
  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.'';';
  -- generate deletes for child tables:
  SELECT @sql = @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

EXECUTE DESTA FORMA

exec dbo.GenerateCascadingDeletes 'Personnel', 'ID', '< 4'

ISTO IRA RETORNAR AS TABELAS REFERENCIADAS PARA FAZER O DELETE CASCADE SIMPLES COM AS QUERYS PARA DELETE.