Skip to main content

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;