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

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

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

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

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

---- 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

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

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

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

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

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