Comment supprimer toutes les lignes de toutes les tables dans une base de données SQL Server ?

Question

Comment supprimer toutes les lignes de toutes les tables dans une base de données SQL Server ?

Answer 1

Notez que TRUNCATE ne fonctionnera pas si vous avez défini une intégrité référentielle.

Dans ce cas, cela fonctionnera :

EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'
GO

Edit : Pour être clair, le ? dans les déclarations est un ? . Il est remplacé par le nom de la table par la commande sp_MSForEachTable procédure.

Answer 2

Dans mon récent projet, ma tâche était de nettoyer une base de données entière en utilisant des instructions SQL et chaque table ayant de nombreuses contraintes comme la clé primaire et la clé étrangère. Il y a plus de 1000 tables dans la base de données, il n'est donc pas possible d'écrire une requête de suppression sur chaque table.

En utilisant une procédure stockée nommée sp_MSForEachTable qui nous permet de traiter facilement du code pour chaque table d'une seule base de données. Cela signifie qu'il est utilisé pour traiter une seule commande T-SQL ou différentes commandes T-SQL contre chaque table de la base de données.

Suivez donc les étapes ci-dessous pour tronquer toutes les tables d'une base de données SQL Server :

Étape 1 - Désactiver toutes les contraintes sur la base de données en utilisant la requête sql ci-dessous :

EXEC sys.sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

Etape 2 - Exécuter une opération de suppression ou de troncature sur chaque table de la base de données en utilisant la commande sql ci-dessous :

EXEC sys.sp_msforeachtable 'DELETE FROM ?'

Étape 3 - Activez toutes les contraintes sur la base de données en utilisant l'instruction sql ci-dessous :

EXEC sys.sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Answer 3

J'ai dû supprimer toutes les lignes et je l'ai fait avec le script suivant :

DECLARE @Nombre NVARCHAR(MAX);
DECLARE curso CURSOR FAST_FORWARD 
FOR 
Select Object_name(object_id) AS Nombre from sys.objects where type = 'U'

OPEN curso
FETCH NEXT FROM curso INTO @Nombre

WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
DECLARE @statement NVARCHAR(200);
SET @statement = 'DELETE FROM ' + @Nombre;
print @statement
execute sp_executesql @statement;
END
FETCH NEXT FROM curso INTO @Nombre
END
CLOSE curso
DEALLOCATE curso

J'espère que cela vous aidera !

Answer 4

Dans mon cas, j'ai dû activer QUOTED_IDENTIFIER. Cela a conduit à une légère modification de la réponse de Mark Rendle ci-dessus :

EXEC sp_MSforeachtable 'DISABLE TRIGGER ALL ON ?'
GO
EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'
GO
EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
EXEC sp_MSforeachtable 'ENABLE TRIGGER ALL ON ?'
GO

Answer 5

Voici une solution qui :

1. Contraintes de chute (grâce à este post)
2. Itère à travers INFORMATION_SCHEMA.TABLES pour une base de données particulière
3. SELECTS des tableaux en fonction de certains critères de recherche
4. Supprime toutes les données de ces tables
5. Ajoute à nouveau des contraintes
6. Permet d'ignorer certains tableaux tels que sysdiagrams y __RefactorLog

J'ai d'abord essayé EXECUTE sp_MSforeachtable 'TRUNCATE TABLE ?' mais cela a supprimé mes diagrammes.

USE <DB name>;
GO

-- Disable all constraints in the database
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

declare @catalog nvarchar(250);
declare @schema nvarchar(250);
declare @tbl nvarchar(250);
DECLARE i CURSOR LOCAL FAST_FORWARD FOR select
                                        TABLE_CATALOG,
                                        TABLE_SCHEMA,
                                        TABLE_NAME
                                        from INFORMATION_SCHEMA.TABLES
                                        where
                                        TABLE_TYPE = 'BASE TABLE'
                                        AND TABLE_NAME != 'sysdiagrams'
                                        AND TABLE_NAME != '__RefactorLog'

OPEN i;
FETCH NEXT FROM i INTO @catalog, @schema, @tbl;
WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @sql NVARCHAR(MAX) = N'DELETE FROM [' + @catalog + '].[' + @schema + '].[' + @tbl + '];'
        /* Make sure these are the commands you want to execute before executing */
        PRINT 'Executing statement: ' + @sql
        -- EXECUTE sp_executesql @sql
        FETCH NEXT FROM i INTO @catalog, @schema, @tbl;
    END
CLOSE i;
DEALLOCATE i;

-- Re-enable all constraints again
EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"