SQL Server Truncating Multiple Tables With Foriegn Keys

A typical task of a DBA is to truncate multiple tables in a database. Often this is used to shrink large datasets, to peel off irrelevant information for test or development. One of the obstacles to overcome in truncating data is foreign key references. This is because a truncation is just a drop/create table statement. The following script uses a cursor to loop the sys.tables to grab table names, and building a dynamic statement for the truncation. Prior to the truncation it loops through sys.foreign_keys DMV to grab the all references and deletes them first.

--Will hold Table Name
Declare @TName varchar(100)
--Will Hold FKName
Declare @FKName varchar(100)
--Hold TableName of foriegnKey
Declare @PName varchar(100)
--Hold Dynamic Statement
Declare @Stmt varchar(200)


DECLARE truncate_Cursor CURSOR
FOR
SELECT Name from sys.tables where Name in

--Place tables you want to truncate in list
(
't1',
't2',
't3',
't4',
't5',
't6'
)

Open truncate_Cursor
FETCH NEXT FROM truncate_cursor
into @TName

WHILE @@FETCH_STATUS = 0
BEGIN

--Delete Foriegn Keys
Declare FK_Cursor Cursor For
Select name, object_name(parent_object_id) from sys.foreign_keys
where referenced_object_id = object_id(@TName)

Open FK_Cursor
FETCH NEXT FROM FK_cursor
into @FKName, @Pname

WHILE @@FETCH_STATUS = 0
BEGIN
set @stmt = 'Alter table ' + @Pname + ' Drop constraint ' + @FKName
Exec(@Stmt)
FETCH NEXT FROM FK_cursor
into @FKName, @Pname
End

CLOSE FK_Cursor
DEALLOCATE FK_Cursor

--Run Truncation
set @stmt = 'Truncate table ' + @Tname
Exec(@Stmt)



FETCH NEXT FROM Truncate_Cursor
into @Tname
END
CLOSE Truncate_Cursor
DEALLOCATE Truncate_Cursor