Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Truncate all tables in a SQL Server database

MSSQLTips author Dave Bennett By:   |   Read Comments (9)   |   Related Tips: More > Constraints
Problem

As a DBA I have found many occasions in testing of various SQL Server deployments and scripts where I need to load a database with data from a lower environment or where I need to alter a table that might already have data in it in such a way that I need to first eliminate the data before I can proceed. Depending on the foreign key constraints in place, clearing old data can be a tedious process.

Solution

When I don't care about existing data in a SQL Server database, but I don't want to resort to dropping and re-creating the database and all the additional tasks that come with it, I have found truncating all the data in all the tables to be an effective solution. I prefer truncating over deleting the data because of the various advantages truncating has by nature when you want to reset to a clean state.

Unfortunately, in most complex database designs there are numerous foreign key relationships that make it impossible to just use a simple "for each" loop and issuing a TRUNCATE command.

To illustrate this problem and my solution, we first need to create a database with several tables with foreign key constraints.

CREATE DATABASE ForeignKeyTest
GO
USE ForeignKeyTest
GO
CREATE TABLE T1
       (
        T1Id INT IDENTITY
                 PRIMARY KEY
                 NOT NULL
       ,Customer VARCHAR(50) NOT NULL
       )
GO
CREATE TABLE T2
       (
        T2Id INT IDENTITY
                 PRIMARY KEY
                 NOT NULL
       ,T1Id INT NOT NULL
       ,OrderNum INT NOT NULL
       )
GO
CREATE TABLE T3
       (
        T3Id INT IDENTITY
                 PRIMARY KEY
                 NOT NULL
       ,T2Id INT NOT NULL
       ,ItemId INT NOT NULL
       ,Qty INT NOT NULL
       )
GO
CREATE TABLE Item
       (
        ItemId INT IDENTITY
                   PRIMARY KEY
                   NOT NULL
       ,Item VARCHAR(50)
       )
GO
ALTER TABLE dbo.T2
ADD CONSTRAINT FK_T2_T1 FOREIGN KEY( T1Id) REFERENCES dbo.T1 ( T1Id)
GO
ALTER TABLE dbo.T3
ADD CONSTRAINT FK_T3_T2 FOREIGN KEY( T2Id) REFERENCES dbo.T2 (T2Id)
GO
ALTER TABLE dbo.T3  
ADD CONSTRAINT FK_T3_Item FOREIGN KEY(ItemId) REFERENCES dbo.Item(ItemId)
GO
INSERT dbo.T1
        ( Customer )
SELECT 'FirstCust'
UNION
SELECT 'SecondCust'
UNION
SELECT 'ThirdCust' ;
INSERT dbo.T2
        ( T1Id, OrderNum )
SELECT 1, 1
UNION
SELECT 1, 2
UNION 
SELECT 2, 3
UNION
SELECT 3, 4
UNION
SELECT 3, 5 ;
INSERT dbo.Item
        ( Item )
SELECT 'Gunk'
UNION
SELECT 'Slop'
UNION
SELECT 'Glop'
UNION
SELECT 'Crud' ;
INSERT dbo.T3
        ( T2Id, ItemId, Qty )
SELECT 1,3,5
UNION
SELECT 1,2,2
UNION
SELECT 2,1,4
UNION
SELECT 3,3,10;

This creates tables that look like this:

Create a database with several tables with foreign key constraints

The data in the tables look like:

T1
Truncate all tables in a database
T2
T3
Just use a simple "for each" loop and issuing a TRUNCATE command.
ItemTable
Unfortunately, in most complex database designs there are numerous foreign key relationships

If you attempt to truncate any of the tables you get this error:

BEGIN TRAN
TRUNCATE TABLE dbo.T2
ROLLBACK
If you attempt to truncate any of the tables you get this error:

If you attempt to delete data from the tables you get this error:

BEGIN TRAN
DELETE dbo.T1
ROLLBACK
If you attempt to delete data from the tables you get this error:

If you only want to use the DELETE statement against your tables, you can disable the constraints, delete your data and re-enable your constraints.

BEGIN TRAN
ALTER TABLE dbo.T1 NOCHECK CONSTRAINT ALL
ALTER TABLE dbo.T2 NOCHECK CONSTRAINT ALL
DELETE dbo.T1
SELECT * FROM dbo.T1 AS T
ALTER TABLE dbo.T1 CHECK CONSTRAINT ALL ALTER TABLE dbo.T2 CHECK CONSTRAINT ALL ROLLBACK
If you only want to use the DELETE statement against your tables, you can disable the constraints, delete your data and re-enable your constraints.

This works, but if you try do the same thing using TRUNCATE you get the following:

BEGIN TRAN
ALTER TABLE dbo.T1 NOCHECK CONSTRAINT ALL
ALTER TABLE dbo.T2 NOCHECK CONSTRAINT ALL
TRUNCATE TABLE  dbo.T1
SELECT * FROM dbo.T1 AS T
ROLLBACK
This works, but if you try do the same thing using TRUNCATE you get the following:

A solution that can TRUNCATE all tables

In order to truncate all tables in your database you must first remove all the foreign key constraints, truncate the tables, and then restore all the constraints.

The below script accomplishes this in an automated fashion, by executing the following steps:

  1. Create a table variable to store the constraint drop and creation scripts for the database
  2. Load the data for all tables in the database
  3. Execute a cursor to drop all constraints
  4. Truncate all tables
  5. Recreate all the constraints
 /* 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  --Test statement*/
DECLARE @DropStatement NVARCHAR(MAX)
DECLARE @RecreateStatement NVARCHAR(MAX)
/* Drop Constraints */
DECLARE Cur1 CURSOR READ_ONLY
FOR
        SELECT  DropStmt
        FROM    @dropAndCreateConstraintsTable
OPEN Cur1
FETCH NEXT FROM Cur1 INTO @DropStatement
WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Executing ' + @DropStatement
            EXECUTE sp_executesql @DropStatement
            FETCH NEXT FROM Cur1 INTO @DropStatement
      END
CLOSE Cur1
DEALLOCATE Cur1
/* Truncate all tables in the database in the dbo schema */
DECLARE @DeleteTableStatement NVARCHAR(MAX)
DECLARE Cur2 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 Cur2
FETCH NEXT FROM Cur2 INTO @DeleteTableStatement
WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Executing ' + @DeleteTableStatement
            EXECUTE sp_executesql @DeleteTableStatement
            FETCH NEXT FROM Cur2 INTO @DeleteTableStatement
      END
CLOSE Cur2
DEALLOCATE Cur2
/* Recreate foreign key constraints  */
DECLARE Cur3 CURSOR READ_ONLY
FOR
        SELECT  CreateStmt
        FROM    @dropAndCreateConstraintsTable
OPEN Cur3
FETCH NEXT FROM Cur3 INTO @RecreateStatement
WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Executing ' + @RecreateStatement
            EXECUTE sp_executesql @RecreateStatement
            FETCH NEXT FROM Cur3 INTO @RecreateStatement
      END
CLOSE Cur3
DEALLOCATE Cur3
GO   

When the above script is run against any database, all the tables are emptied and reset.

Next Steps


Last Update: 4/29/2014


About the author
MSSQLTips author Dave Bennett
Dave Bennett is a Senior SQL Server DBA for a major telecommunications company. He has been working in technology for over 20 years.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Friday, September 19, 2014 - 9:49:57 AM - Rukmini Read The Tip

Thank you for the script it is useful


Saturday, September 13, 2014 - 7:02:41 AM - Yasir Read The Tip

Really nice tip, saved my time. Thumbs Up @Dave.


Tuesday, July 01, 2014 - 3:32:02 AM - rajesh Read The Tip

 

 

good work.


Thursday, May 01, 2014 - 10:44:15 AM - Scott Coleman Read The Tip

I would probably use a temp table instead of a table variable to hold the commands.  That way if there is any unexpected error, the commands to recreate the foreign keys are still available.


Wednesday, April 30, 2014 - 11:51:25 AM - jonmcrawford Read The Tip

Curious why you wouldn't just generate scripts to create structure without data? Already built into the tools?


Wednesday, April 30, 2014 - 4:50:49 AM - Leif Nielsen Read The Tip

Thank you for the script.

 

I just have a small problem with it: it only seems to work on keys/references with a single column.


Tuesday, April 29, 2014 - 5:28:08 PM - Jeremy Kadlec Read The Tip

Dave,

Congrats on your first tip!

Thank you,
Jeremy Kadlec
MSSQLTips.com Community Co-Leader


Tuesday, April 29, 2014 - 3:30:21 PM - Jean-Pierre Lebrasseur Read The Tip

Thank you for the script it is usefull

In the my copy I add two fields in the script (Table name and reference Table name) that way you can extract only the one that you want to truncate and you have the reference to them.

 


Tuesday, April 29, 2014 - 12:43:24 PM - TimothyAWiseman Read The Tip

This looks like an interesting and comprehensive solution.

 

Just out of curiosity, why use cursors as opposed to deleting the records from your table variable as you go and looping through the table variable?




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.