Cannot truncate table 'XXXXXX' because it is being referenced by a FOREIGN KEY constraint.

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'XXXXX' because it is being referenced by a FOREIGN KEY constraint.

This is Automation script, this will
Backing up Foreign Key Definitions then Dropping Foreign Keys then Truncating Tables then Re-creating Foreign Keys

SET NOCOUNT ON

-- GLOBAL VARIABLESDECLARE @i intDECLARE
@Debug bitDECLARE
@Recycle bitDECLARE
@Verbose bitDECLARE
@TableName varchar(80)DECLARE @ColumnName varchar(80)DECLARE @ReferencedTableName varchar(80)DECLARE @ReferencedColumnName varchar(80)DECLARE @ConstraintName varchar(250)DECLARE @CreateStatement varchar(max)DECLARE @DropStatement varchar(max) DECLARE @TruncateStatement varchar(max)DECLARE @CreateStatementTemp varchar(max)DECLARE @DropStatementTemp varchar(max)DECLARE @TruncateStatementTemp varchar(max)DECLARE @Statement varchar(max)-- 1 = Will not execute statements SET @Debug = 0
-- 0 = Will not create or truncate storage table-- 1 = Will create or truncate storage tableSET @Recycle = 0
-- 1 = Will print a message on every stepset @Verbose = 1
SET @i = 1
SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>] WITH NOCHECK ADD CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'-- Drop Temporary tablesDROP TABLE #FKs-- GET FKsSELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,OBJECT_NAME(constraint_object_id) as ConstraintName,OBJECT_NAME(parent_object_id) as TableName,clm1.name as ColumnName,
OBJECT_NAME(referenced_object_id) as ReferencedTableName,clm2.name as ReferencedColumnNameINTO #FKsFROM sys.foreign_key_columns fkJOIN sys.columns clm1
ON fk.parent_column_id = clm1.column_id
AND fk.parent_object_id = clm1.object_idJOIN sys.columns clm2ON fk.referenced_column_id = clm2.column_id
AND fk.referenced_object_id= clm2.object_idWHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')ORDER BY OBJECT_NAME(parent_object_id)　-- Prepare Storage TableIF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')BEGINIF @Verbose = 1
PRINT '1. Creating Process Specific Tables...'-- CREATE STORAGE TABLE IF IT DOES NOT EXISTSCREATE TABLE [Internal_FK_Definition_Storage] (ID int not null identity(1,1) primary key,FK_Name varchar(250) not null,FK_CreationStatement varchar(max) not null,FK_DestructionStatement varchar(max) not null,Table_TruncationStatement varchar(max) not null)
END ELSEBEGINIF @Recycle = 0
BEGINIF @Verbose = 1
PRINT '1. Truncating Process Specific Tables...'-- TRUNCATE TABLE IF IT ALREADY EXISTSTRUNCATE TABLE [Internal_FK_Definition_Storage]
ENDELSEPRINT '1. Process specific table will be recycled from previous execution...'ENDIF @Recycle = 0
BEGINIF @Verbose = 1
PRINT '2. Backing up Foreign Key Definitions...'-- Fetch and persist FKs WHILE (@i <= (SELECT MAX(ID) FROM #FKs))BEGINSET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName)
INSERT INTO [Internal_FK_Definition_Storage]SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTempSET @i = @i + 1
IF @Verbose = 1
PRINT ' > Backing up [' + @ConstraintName + '] from [' + @TableName + ']'ENDEND
ELSE
PRINT '2. Backup up was recycled from previous execution...'IF @Verbose = 1
PRINT '3. Dropping Foreign Keys...'-- DROP FOREING KEYSSET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))BEGINSET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)IF @Debug = 1
PRINT @StatementELSEEXEC(@Statement)SET @i = @i + 1
IF @Verbose = 1
PRINT ' > Dropping [' + @ConstraintName + ']'END
IF @Verbose = 1
PRINT '4. Truncating Tables...'-- TRUNCATE TABLESSET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))BEGINSET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)IF @Debug = 1
PRINT @StatementELSEEXEC(@Statement)SET @i = @i + 1
IF @Verbose = 1
PRINT ' > ' + @StatementENDIF @Verbose = 1
PRINT '5. Re-creating Foreign Keys...'-- CREATE FOREING KEYSSET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))BEGINSET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)IF @Debug = 1
PRINT @StatementELSEEXEC(@Statement)SET @i = @i + 1
IF @Verbose = 1
PRINT ' > Re-creating [' + @ConstraintName + ']'ENDIF @Verbose = 1
PRINT '6. Process Completed'