Truncate all tables in a SQL Server database

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:

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 delete data from the tables you get this error:

BEGIN TRAN
DELETE dbo.T1
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.

BEGIN TRAN<br>ALTER TABLE dbo.T1 NOCHECK CONSTRAINT ALL<br>ALTER TABLE dbo.T2 
NOCHECK CONSTRAINT ALL<br>DELETE dbo.T1<br>SELECT * FROM dbo.T1 AS T<br>
ALTER TABLE dbo.T1 CHECK CONSTRAINT ALL
ALTER TABLE dbo.T2 CHECK CONSTRAINT ALL
ROLLBACK

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

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:

Create a table variable to store the constraint drop and creation scripts for the database
Load the data for all tables in the database
Execute a cursor to drop all constraints
Truncate all tables
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

The differences between Truncate and Delete commands Truncate vs. Delete

Resources

Community

Subscribe