SQL Server Stored Procedure to truncate a table that has foreign keys referencing its columns.

use YOUR_DB_NAME;
go

if OBJECT_ID('dbo.INFTruncateTable', 'P') is null
begin
    print 'Creating procedure dbo.INFTruncateTable';
    exec ('CREATE PROCEDURE dbo.INFTruncateTable AS');
end;
go

-- ***************************************************************************
-- 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.
--
-- Sample Call:
--  EXEC dbo.INFTruncateTable 'Orders', 'dbo', 1 -- debug only - no truncation
--  EXEC dbo.INFTruncateTable 'Orders', 'dbo'    -- actually truncate the table
-- ***************************************************************************
alter procedure dbo.INFTruncateTable(
    @TableName sysname,
    @TableSchema sysname = 'dbo',
    @Debug bit = 0 -- If called with value 1 just prints information - no truncation takes place
)
as
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;

    -- rethrow error
    declare @ERROR_SEVERITY int,
        @ERROR_STATE int,
        @ERROR_NUMBER int,
        @ERROR_LINE int,
        @ERROR_MESSAGE nvarchar(4000);

    select @ERROR_SEVERITY = ERROR_SEVERITY(),
        @ERROR_STATE = ERROR_STATE(),
        @ERROR_NUMBER = ERROR_NUMBER(),
        @ERROR_LINE = ERROR_LINE(),
        @ERROR_MESSAGE = ERROR_MESSAGE();

    raiserror('Msg %d, Line %d, :%s',
        @ERROR_SEVERITY,
        @ERROR_STATE,
        @ERROR_NUMBER,
        @ERROR_LINE,
        @ERROR_MESSAGE
    );

    -- uncomment to rethrow error in sql server 2012+
    -- throw;  -- re-throw error
end catch;
end;
go