The TRUNCATE DATABASE script
14/03/2011 Leave a comment
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?