Truncate a table that has foreign keys referencing its columns

,

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. 

The call to the stored procedure is:

EXEC dbo.INFTruncateTable 'Orders', 'dbo'

To process an entire database a script can be set up that will process each of the tables:

DECLARE @Debug bit = 1
SET NOCOUNT ON
DECLARE @TABLE_SCHEMA     sysname,
        @TABLE_NAME       sysname
DECLARE @TablesToTruncate cursor
SET @TablesToTruncate = cursor FOR
SELECT t.TABLE_SCHEMA,
       t.TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES t
 WHERE (   t.TABLE_NAME LIKE 'Fact%'
        OR t.TABLE_NAME LIKE 'Dim%'
        OR t.TABLE_NAME LIKE 'Bridge%'
       )
   AND t.TABLE_SCHEMA = 'dbo'
   AND t.TABLE_TYPE = 'BASE TABLE'
 ORDER BY t.TABLE_NAME
OPEN @TablesToTruncate 
FETCH NEXT FROM @TablesToTruncate INTO @TABLE_SCHEMA, @TABLE_NAME
WHILE @@FETCH_STATUS = 0 BEGIN
    IF @Debug = 1 BEGIN
        PRINT  'EXEC dbo.INFTruncateTable ''' + @TABLE_NAME + ''', ''' + @TABLE_SCHEMA + ''', ' + CONVERT(varchar,@Debug)
        EXEC dbo.INFTruncateTable @TABLE_NAME, @TABLE_SCHEMA, @Debug -- Debug only
    END
    ELSE BEGIN
        EXEC dbo.INFTruncateTable @TABLE_NAME, @TABLE_SCHEMA, @Debug
    END
    FETCH NEXT FROM @TablesToTruncate INTO @TABLE_SCHEMA, @TABLE_NAME
END
CLOSE @TablesToTruncate
DEALLOCATE @TablesToTruncate
USE myDatabase
GO
IF OBJECT_ID('dbo.INFTruncateTable','P') IS NULL BEGIN
    PRINT ('Creating procedure dbo.INFTruncateTable')
    EXEC('CREATE PROCEDURE dbo.INFTruncateTable AS')
END
GO
-- ****************************************************************
-- 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
-- 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
        DROP TABLE #DropCreateFKs

    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 si
         INNER JOIN sys.objects so
                 ON so.[object_id] = si.[object_id]
         INNER JOIN sys.schemas 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 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 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 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 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 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
            SELECT *
              FROM #DropCreateFKs

        DROP TABLE #DropCreateFKs

    END TRY

    BEGIN CATCH

        PRINT 'Rolling back transaction';
        IF @@TRANCOUNT > 0 BEGIN
            ROLLBACK
        END;
        THROW;  -- re-throw error

    END CATCH

END
GO

Rate

3.5 (4)

Share

Share

Rate

3.5 (4)