Vider toutes les tables d’une base

Une question simple et un réponse efficace. Comment vider toutes les tables d’une base de données ?

L’intégrité référentielle va complexifier la chose car il faudrait faire un DELETE de chaque table en tenant compte de la hiérarchie d’imbrication des références d’intégrités du fait des contraintes de clefs étrangères. Mais SQL Server permet de désactiver les contraintes, ce qui va nous rendre service dans le cas présent.
La solution consiste donc à :
Рd̩sactiver les contraintes FOREIGN KEY
– lancer un DELETE sur toutes les tables
Рr̩activer les contraintes FOREIGN KEY
Voici le script final :

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'';

BEGIN TRANSACTION;

BEGIN TRY

   -- désactivation des contraintes d'intégrité référentielles pour les clefs étrangères

   SELECT @SQL = @SQL + N'ALTER TABLE [' + TABLE_SCHEMA + N'].[' + TABLE_NAME + N'] '
               + N' NOCHECK CONSTRAINT [' + CONSTRAINT_NAME + N'];'
   FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_TYPE = N'FOREIGN KEY';
   
   EXEC (@SQL);
   
   SET @SQL = N'';
   
   -- vidage des tables
   
   SELECT @SQL = @SQL + 'DELETE FROM [' + TABLE_SCHEMA + N'].[' + TABLE_NAME + N'];'
   FROM   INFORMATION_SCHEMA.TABLES
   WHERE  TABLE_TYPE = 'BASE TABLE';
   
   EXEC (@SQL);
   
   -- réactivation des contraintes d'intégrité référentielles pour les clefs étrangères

   SELECT @SQL = @SQL + N'ALTER TABLE [' + TABLE_SCHEMA + N'].[' + TABLE_NAME + N'] WITH CHECK'
               + N' CHECK CONSTRAINT [' + CONSTRAINT_NAME + N'];'
   FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_TYPE = N'FOREIGN KEY';
   
   EXEC (@SQL);  

   COMMIT TRANSACTION:

END TRY  
BEGIN CATCH
   ROLLBACK;
   DECLARE @MSG NVARCHAR(1200);
   RAISERROR ('Transaction annulée suite à erreur dans le script : ', 16, 1, @MSG)
END CATCH;
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES
Expert  S.G.B.D  relationnelles   et   langage  S.Q.L
Moste  Valuable  Professionnal  Microsoft  SQL Server
Société SQLspot  :  modélisation, conseil, formation,
optimisation,  audit,  tuning,  administration  SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.

L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR

MVP Microsoft SQL Server

Laisser un commentaire