By: Dave Bennett | Updated: 2014-04-29 | Comments (17) | Related: > 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:
The data in the tables look like:
T1
 |
T2
 |
T3
 |
ItemTable
 |
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
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
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
About the author
Dave Bennett is a Senior SQL Server DBA for a major telecommunications company. He has been working in technology for over 20 years. This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-04-29
