Truncate a table that has foreign keys referencing its columns

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