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