SQL Server Stored Procedure to truncate a table that has foreign keys referencing its columns.
create procedure dbo.TruncateTable(
@TableName sysname,
@TableSchema sysname = 'dbo',
@Debug bit = 0 -- If called with value 1 just prints information - no truncation takes place
)
as
-- ***************************************************************************
-- Truncate a table that has foreign keys referencing its columns
-- http://www.sqlservercentral.com/scripts/Truncate+Table/155085/
--
-- This SP will truncate a table that has FK's pointing to its columns.
-- Drops the FKs, truncates the table, re-creates the FKs with NO CHECK
--
-- I needed a procedure reset a Data Warehouse. To do this I needed to truncate
-- all the Dimension, Fact and Bridge tables. This stored procedure allows the
-- fast truncation of any table that has foreign keys referencing at columns on
-- the table. It drops all the foreign keys referencing the table to be
-- truncated, truncates the table, re-creates the foreign keys.
--
-- Example Usage:
--
-- EXEC dbo.TruncateTable 'Orders', 'dbo', 1 -- debug only - no truncation
-- EXEC dbo.TruncateTable 'Orders', 'dbo' -- actually truncate the table
-- ***************************************************************************
begin
set nocount on;
if object_id('tempdb..#DropCreateFKs', 'U') is not null
begin
drop table #DropCreateFKs;
end;
declare @NewLine nvarchar(max) = char(13) + char(10);
declare @FKCount int;
with CTE
as (select ss.name as TABLE_SCHEMA,
object_name(si.object_id) as TABLE_NAME,
si.name as CONSTRAINT_NAME
from sys.indexes as si
inner join sys.objects as so
on so.object_id = si.object_id
inner join sys.schemas as ss
on ss.schema_id = so.schema_id
and ss.name = @TableSchema
where si.is_unique = 1
and object_name(si.object_id) = @TableName)
select quotename(@TableSchema)+'.'+quotename(@TableName) as TableName,
'ALTER TABLE '+quotename(tc.TABLE_SCHEMA)+'.'+quotename(tc.TABLE_NAME)+@NewLine+' DROP CONSTRAINT '+quotename(tc.CONSTRAINT_NAME)+';'+@NewLine as Drop_FK,
'ALTER TABLE '+quotename(tc.TABLE_SCHEMA)+'.'+quotename(tc.TABLE_NAME)+@NewLine+' WITH NOCHECK'+@NewLine+' ADD CONSTRAINT '+quotename(tc.CONSTRAINT_NAME)+@NewLine+' FOREIGN KEY ('+ColsOnFKTable.Cols+')'+@NewLine+' REFERENCES '+quotename(ccu.TABLE_SCHEMA)+'.'+quotename(ccu.TABLE_NAME)+'('+UniqueCols.Cols+') '+@NewLine+' ON DELETE '+rc.DELETE_RULE+@NewLine+' ON UPDATE '+rc.UPDATE_RULE+';'+@NewLine as Create_FK
into #DropCreateFKs
from CTE as ccu
cross apply (select stuff( (select ', '+quotename(ccu2.COLUMN_NAME)
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as ccu2
where ccu2.TABLE_SCHEMA = ccu.TABLE_SCHEMA
and ccu2.TABLE_NAME = ccu.TABLE_NAME
and ccu2.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME for xml path(''), type) .value
('.', 'nvarchar(MAX)'
), 1, 2, '') as Cols) as UniqueCols
cross apply (select rc.CONSTRAINT_CATALOG,
rc.CONSTRAINT_NAME,
rc.DELETE_RULE,
rc.UPDATE_RULE
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc
where rc.UNIQUE_CONSTRAINT_NAME = ccu.CONSTRAINT_NAME) as rc
cross apply (select tc.CONSTRAINT_CATALOG,
tc.TABLE_SCHEMA,
tc.TABLE_NAME,
tc.CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
where tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
and tc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
and tc.CONSTRAINT_CATALOG = rc.CONSTRAINT_CATALOG) as tc
cross apply (select stuff( (select ', '+quotename(ccu2.COLUMN_NAME)
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as ccu2
where ccu2.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
and ccu2.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG for xml path(''), type) .value
('.', 'nvarchar(MAX)'), 1, 2, '') as Cols) as ColsOnFKTable;
set @FKCount = @@rowcount;
declare @sSqlDrop as nvarchar(max) = '';
declare @sSqlCreate as nvarchar(max) = '';
declare @sSqlTruncate as nvarchar(max) = '';
set @sSqlTruncate = @TableSchema+'.'+@TableName;
select @sSqlDrop = @sSqlDrop + x.Drop_FK, @sSqlCreate = @sSqlCreate + x.Create_FK
from #DropCreateFKs as x;
begin try
begin transaction;
if @FKCount > 0
begin
if @Debug = 1
begin
print '-- Dropping FKs on table '+@TableSchema+'.'+@TableName;
print @sSqlDrop;
end;
else
begin
exec (@sSqlDrop);
end;
end;
set @sSqlTruncate = 'TRUNCATE TABLE '+quotename(@TableSchema)+'.'+quotename(@TableName);
if @Debug = 1
begin
print @sSqlTruncate;
end;
else
begin
exec (@sSqlTruncate);
end;
if @FKCount > 0
begin
print '-- Creating FKs on table '+@TableSchema+'.'+@TableName;
if @Debug = 1
begin
print @sSqlCreate;
end;
else
begin
exec (@sSqlCreate);
end;
end;
commit;
if @Debug = 1 and @FKCount > 0
begin
select * from #DropCreateFKs;
end;
drop table #DropCreateFKs;
end try
begin catch
print 'Rolling back transaction';
if @@trancount > 0
begin
rollback;
end;
throw;
end catch;
end;