How to delete all rows from all tables in a SQL Server database

Question

User · Answer

if you want to delete the whole table  you must follow the next SQL instruction  Delete  FROM TABLE Where PRIMARY KEY  is Not NULL

User · Answer

In my recent project my task was to clean an entire database by using sql statement and each table having many constraints like Primary Key and Foreign Key  There are more than 1000 tables in database so its not possible to write a delete query on each and ever table    By using a stored procedure named sp MSForEachTable which allows us to easily process some code against each and every table in a single database  It means that it is used to process a single T-SQL command or a different T-SQL commands against every table in the database   So follow the below steps to truncate all tables in a SQL Server Database   Step 1- Disable all constraints on the database by using below sql query    EXEC sys sp msforeachtable  ALTER TABLE   NOCHECK CONSTRAINT ALL    Step 2- Execute a Delete or truncate operation on each table of the database by using below sql command    EXEC sys sp msforeachtable  DELETE FROM      Step 3- Enable all constraints on the database by using below sql statement   EXEC sys sp MSForEachTable  ALTER TABLE   CHECK CONSTRAINT ALL

User · Answer

Here is a solution that    Drops constraints  thanks to this post  Iterates through INFORMATION SCHEMA TABLES for a particular database SELECTS tables based on some search criteria Deletes all the data from those tables Re-adds constraints Allows for ignoring of certain tables such as sysdiagrams and   RefactorLog   I initially tried EXECUTE sp MSforeachtable  TRUNCATE TABLE     but that deleted my diagrams   USE  lt DB name gt   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

User · Answer

In my case  I needed to set QUOTED IDENTIFIER on  This led to a slight modification of Mark Rendle s answer above   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

User · Answer

For some requirements we might have to skip certain tables  I wrote the below script to add some extra conditions to filter the list of tables  The below script will also display the pre delete count and post delete count            IF OBJECT ID  TEMPDB   TEMPRECORDCOUNT   IS NOT NULL          DROP TABLE  TEMPRECORDCOUNT           CREATE TABLE  TEMPRECORDCOUNT                   TABLENAME NVARCHAR 128                   PREDELETECOUNT BIGINT                  POSTDELETECOUNT BIGINT                         INSERT INTO  TEMPRECORDCOUNT  TABLENAME  PREDELETECOUNT  POSTDELETECOUNT           SELECT   O name TableName                  DDPS ROW COUNT PREDELETECOUNT                  NULL  FROM sys objects O           INNER JOIN                        SELECT OBJECT ID  SUM row count  ROW COUNT                      FROM SYS DM DB PARTITION STATS                     GROUP BY OBJECT ID                      DDPS ON DDPS OBJECT ID   O OBJECT ID         WHERE O type    U  AND O name NOT LIKE  OC   AND O schema id   1          DECLARE  TableName NVARCHAR MAX           DECLARE TableDeleteCursor CURSOR FAST FORWARD          FOR          SELECT TableName from  TEMPRECORDCOUNT          OPEN TableDeleteCursor         FETCH NEXT FROM TableDeleteCursor INTO  TableName          WHILE    FETCH STATUS  lt  gt  -1          BEGIN         IF    FETCH STATUS  lt  gt  -2          BEGIN         DECLARE  STATEMENT NVARCHAR MAX           SET  STATEMENT     DISABLE TRIGGER ALL ON      TableName                                ALTER TABLE      TableName     NOCHECK CONSTRAINT ALL                                DELETE FROM      TableName                               ALTER TABLE      TableName     CHECK CONSTRAINT ALL                                ENABLE TRIGGER ALL ON      TableName          PRINT  STATEMENT         EXECUTE SP EXECUTESQL  STATEMENT          END         FETCH NEXT FROM TableDeleteCursor INTO  TableName         END         CLOSE TableDeleteCursor         DEALLOCATE TableDeleteCursor          UPDATE T           SET T POSTDELETECOUNT   I ROW COUNT           FROM  TEMPRECORDCOUNT T           INNER JOIN                           SELECT O name TableName  DDPS ROW COUNT ROW COUNT                           FROM sys objects O                          INNER JOIN                                    SELECT OBJECT ID  SUM row count  ROW COUNT                                  FROM SYS DM DB PARTITION STATS                                 GROUP BY OBJECT ID                                  DDPS ON DDPS OBJECT ID   O OBJECT ID                         WHERE O type    U  AND O name NOT LIKE  OC   AND O schema id   1                        I ON I TableName COLLATE DATABASE DEFAULT   T TABLENAME           SELECT   FROM  TEMPRECORDCOUNT          ORDER BY TABLENAME ASC

User · Answer

--Load tables to delete from SELECT  DISTINCT   Delete top 1000000 from  lt DBName gt   lt schema gt      c TABLE NAME     WHERE  lt Filter Clause Here gt   AS query c TABLE NAME AS TableName  IsDeleted 0    lt InsertSomeDescriptorHere gt   AS  Source -- t TABLE TYPE  c               INTO dbo AllTablesToDeleteFrom             FROM INFORMATION SCHEMA TABLES AS t             INNER JOIN information schema columns c ON c TABLE NAME   t TABLE NAME      WHERE c COLUMN NAME     lt column name gt              AND c TABLE SCHEMA    dbo             AND c TABLE CATALOG     lt DB Name here gt              AND t TABLE TYPE  Base table             --AND t TABLE NAME LIKE   lt put filter here gt                DECLARE  TableSelect NVARCHAR 1000                   DECLARE  Table NVARCHAR 1000                   DECLARE  IsDeleted INT  0              DECLARE  NumRows INT   1000000              DECLARE  Source NVARCHAR 50                    WHILE    IsDeleted   0                   BEGIN                 --This grabs one table at a time to be deleted from   TableSelect has the sql to execute  it is important to order by IsDeleted ASC                 --because it will pull tables to delete from by those that have a 0 IsDeleted first  Once the loop grabs a table with IsDeleted 1 then this will pop out of loop                      SELECT TOP 1                              TableSelect   query                               IsDeleted   IsDeleted                               Table   TableName                               Source  a   Source                      FROM    dbo AllTablesToDeleteFrom a                     WHERE a  Source   SomeDescriptorHere --use only if needed                     ORDER BY a IsDeleted ASC --this is required because only those records returned with IsDeleted 0 will run through loop                      --SELECT   Table  can add this in to  monitor what table is being deleted from                      WHILE    NumRows   1000000  --only delete a million rows at a time                       BEGIN                      EXEC sp executesql  TableSelect                      SET  NumRows     ROWCOUNT                      --IF  NumRows   1000000 --can do something here if needed                     --One wants this loop to continue as long as a million rows is deleted  Once  lt  1 million rows is deleted it pops out of loop                     --and grabs next table to delete                     --    BEGIN                     --SELECT   NumRows --can add this in to see current number of deleted records for table                             INSERT  INTO dbo DeleteFromAllTables                                       tableName                                        query                                        cnt                                         Source                                                                    SELECT   Table                                       TableSelect                                       NumRows                                       Source                       --   END                   END      SET  NumRows   1000000   UPDATE  a SET     a IsDeleted   1 FROM    dbo AllTablesToDeleteFrom a WHERE   a TableName    Table  --flag this as deleted so you can move on to the next table to delete from  END

User · Answer

I had to delete all the rows and did it with the next script   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  lt  gt  -1  BEGIN IF    FETCH STATUS  lt  gt  -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   Hope this helps

User · Answer

This answer builds on Zach Smith s answer by resetting the identity column as well    Disabling all constraints Iterating through all tables except those you choose to exclude Deletes all rows from the table Resets the identity column if one exists Re-enables all constraints   Here is the query   -- 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                                          -- Optional                                         -- AND  TABLE SCHEMA    dbo    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          -- Reset identity counter if one exists         IF   SELECT OBJECTPROPERTY  OBJECT ID  catalog          schema          tbl    TableHasIdentity      1          BEGIN             SET  sql   N DBCC CHECKIDENT        catalog          schema          tbl       RESEED  0               PRINT  Executing statement       sql             --EXECUTE sp executesql  sql         END               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

User · Answer

Note that TRUNCATE won t work if you have any referential integrity set  In that case  this will work  EXEC sp MSForEachTable  DISABLE TRIGGER ALL ON    GO EXEC sp MSForEachTable  ALTER TABLE   NOCHECK CONSTRAINT ALL  GO EXEC sp MSForEachTable  DELETE FROM    GO EXEC sp MSForEachTable  ALTER TABLE   CHECK CONSTRAINT ALL  GO EXEC sp MSForEachTable  ENABLE TRIGGER ALL ON    GO  Edit  To be clear  the   in the statements is a    It s replaced with the table name by the sp MSForEachTable procedure

User · Answer

You could delete all the rows from all tables using an approach like Rubens suggested  or you could just drop and recreate all the tables  Always a good idea to have the full db creation scripts anyway so that may be the easiest quickest method

User · Answer

Set nocount on  Exec sp MSForEachTable  Alter Table   NoCheck Constraint All   Exec sp MSForEachTable   If ObjectProperty Object ID           TableHasForeignRef    1 Begin -- Just to know what all table used delete syntax  Print   Delete from            Delete From   End Else Begin -- Just to know what all table used Truncate syntax  Print   Truncate Table            Truncate Table   End    Exec sp MSForEachTable  Alter Table   Check Constraint All

[sql-server-2005] How to delete all rows from all tables in a SQL Server database?

Examples related to sql-server-2005