Simuler une suppression en cascade avant de supprimer des lignes
Procédure T-SQL qui génère les DELETE des tables enfants d'une ligne parent et compte les lignes concernées, sans rien supprimer.
Lorsqu'une clé étrangère n'a pas de ON DELETE CASCADE, les lignes enfants doivent être supprimées en premier. La procédure ci-dessous construit les instructions DELETE qui supprimeraient une ligne parent et ses lignes enfants directes, et compte les lignes que chaque instruction supprimerait. Elle ne supprime rien : elle renvoie les instructions sous forme de lignes, que vous exécutez vous-même.
Procédure
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
Utilisation
exec dbo.GenerateCascadingDeletes '[app].ParentTable', 'ParentId', '= 1001'
select ChildId, ParentId from app.ChildTable where ParentId = 1001
exec dbo.GenerateCascadingDeletes 'app.ChildTable', 'ChildId', '= 2001'
exec dbo.GenerateCascadingDeletes 'app.ChildTable', 'ChildId', '= 2002'
Le jeu de résultats liste, pour chaque table enfant, une instruction DELETE précédée d'un commentaire indiquant le nombre de lignes, puis le DELETE de la table parent. Exécutez les instructions dans l'ordre renvoyé : les enfants d'abord, le parent en dernier.
Remarques
- Seules les tables dont une clé étrangère référence directement
@ParentTablesont incluses. Les tables plus bas dans la chaîne ne sont pas listées. - Le critère
@DeleteCriteriaest inséré dans le texte généré sans aucun contrôle. Ne passez que des valeurs de confiance. - Les nombres de lignes sont calculés au moment de l'exécution de la procédure. Exécutez les instructions peu après leur génération, sinon les compteurs peuvent être périmés.