SQL TRUNCATE DATABASE How to TRUNCATE ALL TABLES

Question

I use  SQL server 2008 R2   Is there a SQL command to empty the database  instead of having to truncate all 20 my tables   I just want to delete the data not the structure

User · Answer

I use this script  EXEC sp MSForEachTable    ALTER TABLE   NOCHECK CONSTRAINT ALL    EXEC sp MSForEachTable    DELETE FROM      EXEC sp MSForEachTable    ALTER TABLE   CHECK CONSTRAINT ALL    GO

User · Answer

---- Remove Constraint ---- EXEC sp MSForEachTable  ALTER TABLE   NOCHECK CONSTRAINT all   ---- Delete Data ---- EXEC sp MSForEachTable  DELETE FROM     ---- Add Constraint ---- EXEC sp MSForEachTable  ALTER TABLE   WITH CHECK CHECK CONSTRAINT all   ---- Reset Identity value ---- EXEC sp MSForEachTable  DBCC CHECKIDENT        RESEED  0

User · Answer

The accepted answer doesn t quite work when you have foreign key relationships  In that case you have to drop the constraints and recreate them  Below is a stored proc for doing that based on the answer here  CREATE PROCEDURE  dbo   deleteAllDataFromAllTables  AS SET NOCOUNT ON      DECLARE  dropAndCreateConstraintsTable TABLE   DropStmt varchar max    CreateStmt varchar max         insert  dropAndCreateConstraintsTable select       DropStmt    ALTER TABLE      ForeignKeys ForeignTableSchema                      ForeignKeys ForeignTableName                 DROP CONSTRAINT      ForeignKeys ForeignKeyName                CreateStmt    ALTER TABLE      ForeignKeys ForeignTableSchema                      ForeignKeys ForeignTableName                 WITH CHECK ADD CONSTRAINT       ForeignKeys ForeignKeyName                 FOREIGN KEY      ForeignKeys ForeignTableColumn                  REFERENCES      schema name sys objects schema id                  sys objects  name                  sys columns  name                from sys objects       inner join sys columns         on  sys columns  object id    sys objects  object id         inner join           select sys foreign keys  name  as ForeignKeyName           schema name sys objects schema id  as ForeignTableSchema           sys objects  name  as ForeignTableName           sys columns  name   as ForeignTableColumn           sys foreign keys referenced object id as referenced object id           sys foreign key columns referenced column id as referenced column id          from sys foreign keys           inner join sys foreign key columns             on  sys foreign key columns constraint object id                 sys foreign keys  object id             inner join sys objects             on  sys objects  object id                  sys foreign keys parent object id              inner join sys columns               on  sys columns  object id                    sys objects  object id                  and  sys columns column id                   sys foreign key columns parent column id            ForeignKeys         on  ForeignKeys referenced object id   sys objects  object id            and  ForeignKeys referenced column id   sys columns column id       where  sys objects  type     U         and  sys objects  name  not in   sysdiagrams          DECLARE  DropStatement nvarchar max      DECLARE  RecreateStatement nvarchar max       DECLARE C1 CURSOR READ ONLY     FOR     SELECT DropStmt from  dropAndCreateConstraintsTable     OPEN C1      FETCH NEXT FROM C1 INTO  DropStatement      WHILE   FETCH STATUS   0     BEGIN         PRINT  Executing      DropStatement         execute sp executesql  DropStatement         FETCH NEXT FROM C1 INTO  DropStatement     END     CLOSE C1     DEALLOCATE C1      DECLARE  DeleteTableStatement nvarchar max      DECLARE C2 CURSOR READ ONLY     FOR     SELECT  Delete From  dbo       TABLE NAME       from INFORMATION SCHEMA TABLES          WHERE TABLE SCHEMA    dbo  -- Change your schema appropriately      OPEN C2      FETCH NEXT FROM C2 INTO  DeleteTableStatement      WHILE   FETCH STATUS   0     BEGIN         PRINT  Executing      DeleteTableStatement         execute sp executesql  DeleteTableStatement         FETCH NEXT FROM C2 INTO   DeleteTableStatement     END     CLOSE C2     DEALLOCATE C2      DECLARE C3 CURSOR READ ONLY     FOR     SELECT CreateStmt from  dropAndCreateConstraintsTable     OPEN C3      FETCH NEXT FROM C3 INTO  RecreateStatement      WHILE   FETCH STATUS   0     BEGIN         PRINT  Executing      RecreateStatement         execute sp executesql  RecreateStatement         FETCH NEXT FROM C3 INTO   RecreateStatement     END     CLOSE C3     DEALLOCATE C3  GO

User · Answer

You can use the sp MSforeachtable stored procedure like so   USE MyDatabase EXEC sp MSforeachtable  TRUNCATE TABLE      Be warned that this will delete  by truncation  ALL data from all user tables  And in case you can t TRUNCATE due to foreign keys etc  you can run the same as a delete   USE MyDatabase EXEC sp MSforeachtable  DELETE FROM

User · Answer

You can also use this stored procedure if you only want to truncate all tables in a specific schema   --                                               -- Author       Ben de Ridder -- Create date  20160513 -- Description  Truncate all tables in schema --                                               CREATE PROCEDURE TruncateAllTablesInSchema      schema nvarchar 50  AS BEGIN     -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements      SET NOCOUNT ON       DECLARE  table nVARCHAR 255       DECLARE db cursor CURSOR FOR          select t name           from sys tables t inner join                sys schemas s on                      t schema id s schema id          where s name  schema          order by 1      OPEN db cursor         FETCH NEXT FROM db cursor INTO  table         WHILE   FETCH STATUS   0        BEGIN               declare  sql nvarchar 1000              set  sql    truncate table       schema            table                   exec sp sqlexec  sql             FETCH NEXT FROM db cursor INTO  table        END         CLOSE db cursor        DEALLOCATE db cursor   END GO

User · Answer

Most of the solutions provided either don t use TRUNCATE  which is different from DELETE  or they don t deal with the issue of foreign key constraints  The solution provided by Chaitanya comes close  but it falls back to using DELETE  and it does it in a stored procedure  which seems to not fit a situation where you are nuking all data in a database    So  below is my variation which does use TRUNCATE and does address the foreign key constraint problem          TRUNCATE ALL TABLES IN A DATABASE     DECLARE  dropAndCreateConstraintsTable TABLE                    DropStmt VARCHAR MAX           CreateStmt VARCHAR MAX               Gather information to drop and then recreate the current foreign key constraints     INSERT   dropAndCreateConstraintsTable         SELECT  DropStmt    ALTER TABLE      ForeignKeys ForeignTableSchema                           ForeignKeys ForeignTableName      DROP CONSTRAINT                      ForeignKeys ForeignKeyName                         CreateStmt    ALTER TABLE      ForeignKeys ForeignTableSchema                           ForeignKeys ForeignTableName                      WITH CHECK ADD CONSTRAINT      ForeignKeys ForeignKeyName                      FOREIGN KEY      ForeignKeys ForeignTableColumn                       REFERENCES      SCHEMA NAME sys objects schema id                            sys objects  name            sys columns  name                                   FROM    sys objects         INNER JOIN sys columns                 ON   sys columns  object id    sys objects  object id            INNER JOIN   SELECT sys foreign keys  name  AS ForeignKeyName                             SCHEMA NAME sys objects schema id  AS ForeignTableSchema                             sys objects  name  AS ForeignTableName                             sys columns  name  AS ForeignTableColumn                             sys foreign keys referenced object id AS referenced object id                             sys foreign key columns referenced column id AS referenced column id                      FROM   sys foreign keys                      INNER JOIN sys foreign key columns                             ON   sys foreign key columns constraint object id   sys foreign keys  object id                         INNER JOIN sys objects                             ON   sys objects  object id    sys foreign keys parent object id                        INNER JOIN sys columns                             ON   sys columns  object id    sys objects  object id                                   AND   sys columns column id   sys foreign key columns parent column id                        ForeignKeys                 ON   ForeignKeys referenced object id   sys objects  object id                       AND   ForeignKeys referenced column id   sys columns column id           WHERE     sys objects  type     U                    AND   sys objects  name  NOT IN    sysdiagrams          SELECT   FROM  dropAndCreateConstraintsTable AS DACCT     DECLARE  DropStatement NVARCHAR MAX  DECLARE  RecreateStatement NVARCHAR MAX      Drop Constraints    DECLARE C1 CURSOR READ ONLY FOR         SELECT  DropStmt         FROM     dropAndCreateConstraintsTable OPEN C1  FETCH NEXT FROM C1 INTO  DropStatement  WHILE   FETCH STATUS   0       BEGIN             PRINT  Executing      DropStatement             EXECUTE sp executesql  DropStatement             FETCH NEXT FROM C1 INTO  DropStatement       END CLOSE C1 DEALLOCATE C1     Truncate all tables in the database in the dbo schema    DECLARE  DeleteTableStatement NVARCHAR MAX  DECLARE C2 CURSOR READ ONLY FOR         SELECT   TRUNCATE TABLE  dbo       TABLE NAME               FROM    INFORMATION SCHEMA TABLES         WHERE   TABLE SCHEMA    dbo                  AND TABLE TYPE    BASE TABLE       Change your schema appropriately if you don t want to use dbo    OPEN C2  FETCH NEXT FROM C2 INTO  DeleteTableStatement  WHILE   FETCH STATUS   0       BEGIN             PRINT  Executing      DeleteTableStatement             EXECUTE sp executesql  DeleteTableStatement             FETCH NEXT FROM C2 INTO  DeleteTableStatement       END CLOSE C2 DEALLOCATE C2     Recreate foreign key constraints     DECLARE C3 CURSOR READ ONLY FOR         SELECT  CreateStmt         FROM     dropAndCreateConstraintsTable OPEN C3  FETCH NEXT FROM C3 INTO  RecreateStatement  WHILE   FETCH STATUS   0       BEGIN             PRINT  Executing      RecreateStatement             EXECUTE sp executesql  RecreateStatement             FETCH NEXT FROM C3 INTO  RecreateStatement       END CLOSE C3 DEALLOCATE C3  GO

User · Answer

No nonsense script this   EXEC sp MSForEachTable  ALTER TABLE   NOCHECK CONSTRAINT ALL  EXEC sp MSForEachTable  DELETE FROM    EXEC sp MSForEachTable  ALTER TABLE   CHECK CONSTRAINT ALL  EXEC sp MSforeachtable  DBCC CHECKIDENT          RESEED  0   GO   Truncate will still not work if you have foreign keys in tables  This script will reset all identity columns as well

User · Answer

As my purpose is to get an empty version of the test database to import data from an external previous current active source  Access database  once all is fine tuned  I found that using DBCC CloneDatabase with Verify CloneDB option fits perfectly

User · Answer

DECLARE  nombre NVARCHAR 100  DECLARE  tablas TABLE nombre nvarchar 100    INSERT INTO  tablas SELECT t TABLE SCHEMA      t TABLE NAME FROM INFORMATION SCHEMA TABLES T DECLARE  contador INT 0  SELECT  contador COUNT    FROM INFORMATION SCHEMA TABLES  WHILE  contador gt 0  BEGIN     SELECT TOP 1  nombre nombre FROM  tablas      DECLARE  sql NVARCHAR 500         SET  sql   sql  Truncate table     nombre     EXEC   sql      SELECT  sql     SET  contador  contador-1        DELETE TOP  1   tablas  END

User · Answer

execute this   EXEC sp MSforeachtable  PRINT   ALTER TABLE   NOCHECK CONSTRAINT ALL    EXEC sp MSforeachtable  print   DELETE FROM      EXEC sp MSforeachtable  print   ALTER TABLE   WITH CHECK CHECK CONSTRAINT all      After copy the printed result and paste it on Query field and Execute it  It will truncate all tables

User · Answer

Taking a point from both Boycs Answer and mtmurdock s subsequent answer I have the following stored proc on all of my development or staging databases  I ve added some switches to fit my own requirement if I need to add in statements to reseed the data for certain columns    Note  I would have added this as a comment to Boycs brilliant answer but I haven t got enough reputation to do that yet  So please accept my apologies for adding this as an entirely new answer    ALTER PROCEDURE up ResetEntireDatabase  IncludeIdentReseed BIT   IncludeDataReseed BIT AS  EXEC sp MSForEachTable  ALTER TABLE   NOCHECK CONSTRAINT ALL  EXEC sp MSForEachTable  DELETE FROM     IF  IncludeIdentReseed   1 BEGIN     EXEC sp MSForEachTable  DBCC CHECKIDENT          RESEED  1   END  EXEC sp MSForEachTable  ALTER TABLE   CHECK CONSTRAINT ALL   IF  IncludeDataReseed   1 BEGIN     -- Populate Core Data Table Here END GO   And then once ready the execution is really simple   EXEC up ResetEntireDatabase 1  1

[database] SQL TRUNCATE DATABASE ? How to TRUNCATE ALL TABLES

Examples related to database

Examples related to sql-server-2008

Examples related to truncate