Supprimer toutes datatables dans la database SQL Server

La solution de SQLMenace a fonctionné pour moi avec un léger ajustement à la façon dont datatables sont supprimées – DELETE FROM au lieu de TRUNCATE .

 -- disable referential integrity EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' GO EXEC sp_MSForEachTable 'DELETE FROM ?' GO -- enable referential integrity again EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' GO 

Habituellement, je vais juste utiliser le proc non documenté sp_MSForEachTable

 -- disable referential integrity EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' GO EXEC sp_MSForEachTable 'TRUNCATE TABLE ?' GO -- enable referential integrity again EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' GO 

Voir aussi: Supprimer toutes datatables dans la database (lorsque vous avez des FK)

Je suis conscient que c'est en retard, mais je suis d'accord avec la suggestion d'Alex Kuznetsov de script de la database, plutôt que de passer par la tracasserie de purger datatables des tables. Si la solution TRUNCATE ne fonctionne pas et que vous disposez d'une grande quantité de données, l' DELETE instructions DELETE (consignées) peut prendre beaucoup de time et il vous restra des identifiants qui n'ont pas été réinsérés (par exemple, une instruction INSERT dans une table avec une colonne IDENTITY vous donnerait un ID de 50000 au lieu d'un ID de 1).

Pour scripter une database entière, dans SSMS, cliquez avec le button droit sur la database, puis select TASKS -> Generate scripts :

Cliquez sur Next pour ignorer l'écran d'ouverture de l'assistant, puis select les objects que vous souhaitez scripter:

Dans l'écran Set scripting options , vous pouvez choisir des parameters pour le script, comme si vous voulez générer un script pour tous les objects, ou des scripts séparés pour les objects individuels, et si vous souhaitez save le file en Unicode ou ANSI:

L'assistant affiche un résumé, que vous pouvez utiliser pour vérifier que tout est comme vous le souhaitez, et fermez en cliquant sur 'Terminer'.

 /* Drop all non-system stored procs */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name]) WHILE @name is not null BEGIN SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped Procedure: ' + @name SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name]) END GO /* Drop all views */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name]) WHILE @name IS NOT NULL BEGIN SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped View: ' + @name SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name]) END GO /* Drop all functions */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name]) WHILE @name IS NOT NULL BEGIN SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped Function: ' + @name SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name]) END GO /* Drop all Foreign Key constraints */ DECLARE @name VARCHAR(128) DECLARE @constraint VARCHAR(254) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) WHILE @name is not null BEGIN SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) WHILE @constraint IS NOT NULL BEGIN SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']' EXEC (@SQL) PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) END SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) END GO /* Drop all Primary Key constraints */ DECLARE @name VARCHAR(128) DECLARE @constraint VARCHAR(254) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME) WHILE @name IS NOT NULL BEGIN SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) WHILE @constraint is not null BEGIN SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']' EXEC (@SQL) PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) END SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME) END GO /* Drop all tables */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name]) WHILE @name IS NOT NULL BEGIN SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped Table: ' + @name SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name]) END GO 

Il est généralement plus rapide d'écrire un script sur tous les objects de la database et d'en créer un vide, de supprimer ou de tronquer les tables.

1. D'abord, vous devrez désactiver tous les triggersurs:

    sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'; 

2. Exécutez ce script: (Extrait de ce post Merci @SQLMenace)

    SET NOCOUNT ON GO SELECT 'USE [' + db_name() +']'; ;WITH a AS ( SELECT 0 AS lvl, t.object_id AS tblID FROM sys.TABLES t WHERE t.is_ms_shipped = 0 AND t.object_id NOT IN (SELECT f.referenced_object_id FROM sys.foreign_keys f) UNION ALL SELECT a.lvl + 1 AS lvl, f.referenced_object_id AS tblId FROM a INNER JOIN sys.foreign_keys f ON a.tblId = f.parent_object_id AND a.tblID <> f.referenced_object_id ) SELECT 'Delete from ['+ object_schema_name(tblID) + '].[' + object_name(tblId) + ']' FROM a GROUP BY tblId ORDER BY MAX(lvl),1 

Ce script produira des instructions DELETE dans le bon ordre. à partir de tables référencées, puis les references

3. Copiez les instructions DELETE FROM et exécutez-les une fois

4. activer les triggersurs

    sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all' 

5. Commettre les changements:

    begin transaction commit; 

Ci-dessous un script que j'ai utilisé pour supprimer toutes datatables d'une database SQL Server

 ------------------------------------------------------------ /* Use database */ ------------------------------------------------------------- use somedatabase; GO ------------------------------------------------------------------ /* Script to delete an repopulate the base [init database] */ ------------------------------------------------------------------ ------------------------------------------------------------- /* Procedure delete all constraints */ ------------------------------------------------------------- IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_DeleteAllConstraints' AND type = 'P') DROP PROCEDURE dbo.sp_DeleteAllConstraints GO CREATE PROCEDURE sp_DeleteAllConstraints AS EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL' GO ----------------------------------------------------- /* Procedure delete all data from the database */ ----------------------------------------------------- IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_DeleteAllData' AND type = 'P') DROP PROCEDURE dbo.sp_DeleteAllData GO CREATE PROCEDURE sp_DeleteAllData AS EXEC sp_MSForEachTable 'DELETE FROM ?' GO ----------------------------------------------- /* Procedure enable all constraints */ ----------------------------------------------- IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_EnableAllConstraints' AND type = 'P') DROP PROCEDURE dbo.sp_EnableAllConstraints GO -- .... -- .... -- .... 

 EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL' EXEC sp_MSForEachTable 'DELETE FROM ?' EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL' EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?' GO