The TRUNCATE DATABASE script

Last week I saw a post on Twitter from Wesley Backelant (blog|twitter) about somebody emptying a database and then shrinking the files to keep the database size acceptable. We were having a good laugh about a new ‘Empty’ button or a ‘Restore Database with SCHEMA_ONLY) command in SQL in Denali but I had to admit that I occasionally get the request to copy a database from a non-production environment to the production environment and remove all the data. I should be able to do it by scripting out all the possible objects in the database but often when I execute the scripts I’m having problems with tables with a FOREIGN KEY reference to tables that are not created yet, views or stored procedures missing some basetables…

So I want to see how hard it is to just truncate all the tables in a database. Let’s find out.

To get started I create a database to play around with:

CREATE DATABASE EmptyDB
GO

Next I add 2 simple tables:

USE EmptyDB
GO

CREATE TABLE NoFK1 (
 NFK1_ID int IDENTITY (1,1),
 Data char (5)
 )
GO
 
CREATE TABLE NoFK2 (
 NFK2_ID int IDENTITY (1,1),
 Data char (5)
 )
GO

Without any data I cannot prove that I can truncate the tables:

INSERT INTO NoFK1
 VALUES (‘abcde’)
INSERT INTO NoFK2
 VALUES (‘fghij’)
GO 25

Let’s see that the data is actually there:

SELECT * FROM NoFK1
 UNION
 SELECT * FROM NoFK2
GO

So let’s start scripting the truncate of our tables, I use a table variable to hold the fully qualified name of the tables and will loop trough it to truncate each table:

WARNING: EXECUTING THE CODE BELOW WILL RESULT IN DATA LOSS

DECLARE @EmptyTables table (
 ET_ID int IDENTITY (1,1),
 TableName varchar (100)
 )

INSERT INTO @EmptyTables
 SELECT ‘[‘ + TABLE_SCHEMA + ‘]’ + ‘.’ + ‘[‘ + TABLE_NAME + ‘]’
 FROM INFORMATION_SCHEMA.TABLES
 
DECLARE @i int
DECLARE @c int
DECLARE @Table varchar (100)
DECLARE @TruncateString varchar (125)

SET @i = 1
SELECT @c = COUNT(*) FROM @EmptyTables
WHILE @i <= @c
 BEGIN
  SELECT @Table = Tablename
   FROM @EmptyTables ET
   WHERE @i = ET.ET_ID
   
  SET @TruncateString = ‘TRUNCATE TABLE ‘ + @Table
  EXEC (@TruncateString)
  
  SET @i = @i + 1
 END
GO

And when I use the previous select again I get no rows back. But this was too easy, looking in BOL I see that the TRUNCATE TABLE statement is allergic for FOREIGN KEY constraints. This asks for further testings. So in my existing database I add 2 new tables, 1 with a primary key and 1 referencing this primary key:

CREATE TABLE FKParent (
 FKP_ID int IDENTITY (1,1)
  PRIMARY KEY,
 Data char (5)
 )
GO
 
CREATE TABLE FKChild (
 FKC_ID  int
  CONSTRAINT FK_FKChild_FKParent FOREIGN KEY (FKC_ID)
  REFERENCES FKParent(FKP_ID),
 Data char (5)
 )
GO

Off course I check if the foreign key is working (executing the code should end in an error):

INSERT INTO FKChild
 VALUES (1,’pqrst’)
GO

And here I go again filling up my tables:

INSERT INTO NoFK1
 VALUES (‘abcde’)
INSERT INTO NoFK2
 VALUES (‘fghij’)
 INSERT INTO FKParent
 VALUES (‘klmno’)
INSERT INTO FKChild
 VALUES (@@identity,’pqrst’)
GO 25

Off course a little check if there is data in the tables:

SELECT * FROM NoFK1
 UNION
 SELECT * FROM NoFK2
 UNION
 SELECT * FROM FKParent
 UNION
 SELECT * FROM FKChild
GO

If I execute the previous script to empty the tables I get a nice error message saying that a TRUNCATE on table FKParent was not possible because of the FOREIGN KEY constraint. The good news is BOL is right but my script will be a little more complicated. First I’ll need to get the necessary information about my existing FOREIGN KEY’s so I can drop them and, because I want my database only emptied, recreate them.  But first a little test to see how smart SQL Server is. I just disable the FOREIGN KEY, TRUNCATE the FKCHild table first to make sure there is no referencing data left and then try to TRUNCATE the FKParent table:

ALTER TABLE FKChild NOCHECK CONSTRAINT FK_FKChild_FKParent
GO
TRUNCATE TABLE FKChild
TRUNCATE TABLE FKParent

But alas, the TRUNCATE statement fails  again. I will have to put a script around the TRUNCATE script that drops and recreates the FOREIGN KEY’s. My first step is to create a temporary table the hold the FOREIGN KEY information. I use a temporary table instead of a table variable to keep control when the table is dropped. A table variable will disappear after the batch completes so if I don’t run the complete script in 1 batch it’s possible that all my FOREIGN KEY’s are dropped and I don’t have the necessary information anymore to recreate them:

CREATE TABLE #FK_Info (
 FK_ID int IDENTITY (1,1),
 FK_Name varchar (120),
 CS_Name varchar (50),
 CT_Name varchar (50),
 PS_Name varchar (50),
 PT_Name varchar (50),
 CC_Name varchar (50),
 PC_Name varchar (50)
 )
GO

Now I’ve got the temporary table in place I can start filling it up with the details of my FOREIGN KEY’s:

INSERT INTO #FK_Info
 SELECT fk.name,
  SCHEMA_NAME(fk.schema_id),
  OBJECT_NAME(fk.parent_object_id),
  SCHEMA_NAME(o.schema_id),
  OBJECT_NAME(fk.referenced_object_id),
  cc.name,
  cp.name
 FROM sys.foreign_keys fk
 INNER JOIN sys.foreign_key_columns fkc
  ON fk.object_id = fkc.constraint_object_id
 INNER JOIN sys.columns cc
  ON fkc.parent_object_id = cc.object_id
   AND fkc.parent_column_id = cc.column_id
 INNER JOIN sys.columns cp
  ON fkc.referenced_object_id = cp.object_id
   AND fkc.referenced_column_id = cp.column_id
 INNER JOIN sys.objects o
  ON fk.referenced_object_id = o.object_id
GO

And in the third step I can drop the FOREIGN KEY’s using this script:

DECLARE @idrop int
DECLARE @cdrop int
DECLARE @CS varchar (50)
DECLARE @CT varchar (50)
DECLARE @FK varchar (50)
DECLARE @dropstring varchar (400)

SET @idrop = 1
SELECT @cdrop = COUNT(*) from #FK_Info

WHILE @idrop <= @cdrop
 BEGIN
  SELECT @CS = CS_Name,
   @CT = CT_Name,
   @FK = FK_Name
   FROM #FK_Info
   WHERE FK_ID = @idrop
  SET @dropstring = ‘ALTER TABLE [‘ + @CS + ‘].[‘ + @CT + ‘] DROP CONSTRAINT [‘ + @FK + ‘]’
  EXEC (@dropstring)
  
  SET @idrop = @idrop +1
 END
GO

Finally I can execute the above TRUNCATE script and get rid of all my data. The only thing I have to do is put the FOREIGN KEY’s back in place:

DECLARE @icreate int
DECLARE @ccreate int
DECLARE @CS varchar (50)
DECLARE @CT varchar (50)
DECLARE @FK varchar (50)
DECLARE @CC varchar (50)
DECLARE @PS varchar (50)
DECLARE @PT varchar (50)
DECLARE @PC varchar (50)
DECLARE @createstring varchar (600)

SET @icreate = 1
SELECT @ccreate = COUNT(*) from #FK_Info

WHILE @icreate <= @ccreate
 BEGIN
  SELECT @CS = CS_Name,
   @CT = CT_Name,
   @FK = FK_Name,
   @CC = CC_Name,
   @PS = PS_Name,
   @PT = PT_Name,
   @PC = PC_Name
   FROM #FK_Info
   WHERE FK_ID = @icreate
  SET @createstring = ‘ALTER TABLE [‘ + @CS + ‘].[‘ + @CT + ‘] ADD CONSTRAINT [‘ + @FK + ‘] FOREIGN KEY([‘ + @CC + ‘])
   REFERENCES [‘ + @PS + ‘].[‘ + @PT + ‘]([‘ + @PC + ‘])’
  EXEC (@createstring)
  
  SET @icreate= @icreate +1
 END
GO

Doing a proper job, I need to cleanup the mess:

DROP TABLE #FK_Info

And finally I have an empty database ready to be filled with new test or production data. For the moment this script does what I need to do but it will not work if I have INDEXED VIEWS in my database. Maybe something for a later blog post?