Delete all data in SQL Server database

Question

How I can delete all records from all tables of my database  Can I do it with one SQL command or I need for one SQL command per one table

User · Answer

Usually I will just use the undocumented proc 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

See also: Delete all data in database (when you have FKs)

User · Answer

Below a script that I used to remove all data from an SQL Server database   ------------------------------------------------------------    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 --      --      --

User · Answer

First you ll have to disable all the triggers      sp msforeachtable  ALTER TABLE   DISABLE TRIGGER all    Run this script    Taken from this post Thank you  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  lt  gt  f referenced object id   SELECT       Delete from     object schema name tblID            object name tblId         FROM a GROUP BY tblId  ORDER BY MAX lvl  1    This script will produce DELETE statements in proper order  starting from referenced tables then referencing ones    Copy the DELETE FROM statements and run them once enable triggers   sp msforeachtable  ALTER TABLE   ENABLE TRIGGER all   Commit the changes     begin transaction commit

User · Answer

It is usually much faster to script out all the objects in the database  and create an empty one  that to delete from or truncate tables

User · Answer

Yes  it is possible to delete with a single line of code  SELECT  TRUNCATE TABLE     d NAME        FROM   sys tables d  WHERE  type    U

User · Answer

SQLMenace s solution worked for me with a slight tweak to how data is deleted - DELETE FROM instead of 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

User · Answer

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

User · Answer

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   gt   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   gt   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   gt   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  lt  gt   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  lt  gt   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   gt   name ORDER BY  name   END GO

User · Answer

As an alternative answer  if you Visual Studio SSDT or possibly Red Gate Sql Compare  you could simply run a schema comparison  script it out  drop the old database  possibly make a backup first in case there would be a reason that you will need that data   and then create a new database with the script created by the comparison tool  While on a very small database this may be more work  on a very large database it will be much quicker to simply drop the database then to deal with the different triggers and constraints that may be on the database

User · Answer

I m aware this is late  but I agree with AlexKuznetsov s suggestion to script the database  rather than going through the hassle of purging the data from the tables  If the TRUNCATE solution will not work  and you happen to have a large amount of data  issuing  logged  DELETE statements might take a long time  and you ll be left with identifiers that have not been reseeded  i e  an INSERT statement into a table with an IDENTITY column would get you an ID of 50000 instead of an ID of 1    To script a whole database  in SSMS  right-click the database  then select TASKS -  Generate scripts     Click Next to skip the Wizard opening screen  and then select which objects you want to script     In the Set scripting options screen  you can pick settings for the scripting  like whether to generate 1 script for all the objects  or separate scripts for the individual objects  and whether to save the file in Unicode or ANSI     The wizard will show a summary  which you can use to verify everything is as desired  and close by clicking on  Finish

[sql] Delete all data in SQL Server database

Examples related to sql

Examples related to sql-server

Examples related to delete-row

Examples related to multi-table-delete