Truncate Table with Foreign Key constraint

This post helps you in automating the process of Truncate Table with Foreign Key constraint”. We recently working for a client on data migration project. There was a requirement for truncating tables. But we had to face issues as all those tables are having foreign keys. So the requirement is :

Everytime we’ll send you a list of tables to be truncated
First collect all constraint details from those tables
Drop all foreign keys
Truncate tables
Recreate all constraints
If in-case of any failure the script should not fail or through any error message instead it should continue with the next object and failed objects / constraints should be printed

We built the below stored procedure to fulfill the client requirement and it worked perfectly as expected.

Stored Procedure Creation to Truncate Table with Foreign Key constraint:


IF EXISTS(SELECT 1 FROM SYS.SYSOBJECTS WHERE NAME='USP_Truncate_Tabs' AND TYPE='P')
BEGIN
	DROP PROC USP_Truncate_Tabs;
END

GO

CREATE PROC USP_Truncate_Tabs(@String VARCHAR(7500))  
AS 
BEGIN 
SET NOCOUNT ON 
 
/*** Verify table existence if not avilable it creates anew table on master database ***/
IF NOT EXISTS(SELECT 1 FROM master.sys.sysobjects WHERE Name='Constraints' AND TYPE='U')
BEGIN
	CREATE TABLE [master].[dbo].[Constraints]
			(ID INT IDENTITY,
			DropStmt VARCHAR(1000),
			Createstmt VARCHAR(1000),
			DropStatus BIT DEFAULT(1),
			CreateStatus BIT DEFAULT(1),
			TruncateStatus BIT DEFAULT(1),
			LastUsed DATETIME DEFAULT(GETDATE()));
END

TRUNCATE TABLE [master].[dbo].[Constraints]

/*** Get the list of Tables from the input String ***/
DECLARE @Tab TABLE(ID INT IDENTITY,TabName VARCHAR(200)) 
DECLARE @SQL1 VARCHAR(8000) 
SET @SQL1='SELECT NAME FROM sysobjects  WHERE TYPE=''U'' AND name in('+(@String)+')' 
INSERT INTO @Tab EXEC(@SQL1) 
 

/*** Insert All Drop and Create dtatements into the table Constraints ***/
INSERT INTO [master].[dbo].[Constraints] (DropStmt,CreateStmt) 
SELECT 
  'ALTER TABLE [' + ForeignKeys.ForeignTableSchema +  
      '].[' + ForeignKeys.ForeignTableName +  
      '] DROP CONSTRAINT [' + ForeignKeys.ForeignKeyName + ']; ' 
,'ALTER TABLE ['+ ForeignKeys.ForeignTableSchema +  
      '].[' + ForeignKeys.ForeignTableName +  
      '] WITH CHECK ADD CONSTRAINT [' +  ForeignKeys.ForeignKeyName +  
      '] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn +  
      ']) REFERENCES [' + schema_name(sys.objects.schema_id) + '].[' + 
  sys.objects.[name] + ']([' + 
  sys.columns.[name] + ']);  ' 
 from sys.objects 
  inner join sys.columns 
    on (sys.columns.[object_id] = sys.objects.[object_id]) 
  inner join ( 
    select sys.foreign_keys.[name] as ForeignKeyName 
     ,schema_name(sys.objects.schema_id) as ForeignTableSchema 
     ,sys.objects.[name] as ForeignTableName 
     ,sys.columns.[name]  as ForeignTableColumn 
     ,sys.foreign_keys.referenced_object_id as referenced_object_id 
     ,sys.foreign_key_columns.referenced_column_id as referenced_column_id 
     from sys.foreign_keys 
      inner join sys.foreign_key_columns 
        on (sys.foreign_key_columns.constraint_object_id 
          = sys.foreign_keys.[object_id]) 
      inner join sys.objects 
        on (sys.objects.[object_id] 
          = sys.foreign_keys.parent_object_id) 
        inner join sys.columns 
          on (sys.columns.[object_id] 
            = sys.objects.[object_id]) 
           and (sys.columns.column_id 
            = sys.foreign_key_columns.parent_column_id) 
    ) ForeignKeys 
    on (ForeignKeys.referenced_object_id = sys.objects.[object_id]) 
     and (ForeignKeys.referenced_column_id = sys.columns.column_id) 
 where (sys.objects.[type] = 'U') 
  and (sys.objects.[name] not in ('sysdiagrams')) 
 

/*** Drop all Constraints in the database and update the Constraints table for failed statements ***/ 
/*** From Constraints Table Status 1 - Success and Status 0 - Failed ***/
PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
PRINT'@@@@@@@@@@@@@@@@   Drop Constraints @@@@@@@@@@@@@@@@@@@@'
PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'

DECLARE @Sql VARCHAR(1000) 
DECLARE @i INT,@Max INT 
SELECT @Max=COUNT(1)FROM [master].[dbo].[Constraints]  
SET @i=1 

WHILE(@i<=@Max) 
BEGIN 
	SELECT @Sql=DropStmt FROM [master].[dbo].[Constraints] WHERE ID=@i 
	BEGIN TRY
		EXEC(@Sql) 
	END TRY
	BEGIN CATCH
		UPDATE [master].[dbo].[Constraints] SET DropStatus=0 WHERE ID=@i
	END CATCH
	SET @Sql='' 
	SET @i=@i+1 
END 

/*** Check for the errors in droping the constraints ***/

IF NOT EXISTS(SELECT 1 FROM [master].[dbo].[Constraints] WHERE DropStatus=0)
BEGIN
	PRINT'All foreign keys are Dropped successfully' 
END
ELSE
BEGIN
	PRINT'A result set retrived with the Failed Statements'
	SELECT CreateStmt AS'FailedtoDrop'FROM [master].[dbo].[Constraints] WHERE DropStatus=0
END


/*** Truncate the tables specified in the input string ******/
/*** It directly echos the failed statements on results window ***/
PRINT' ' 
PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
PRINT'@@@@@@@@@@@@@@@@ TRUNCATE Tables @@@@@@@@@@@@@@@@@@@@@@@'
PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
PRINT' ' 

DECLARE @TabName VARCHAR(200) 
SELECT @Max=COUNT(1)FROM @Tab 
SET @i=1 
WHILE(@i<=@Max) 
BEGIN 
	SELECT @TabName=TabName FROM @Tab WHERE ID=@i 
	SELECT @Sql='TRUNCATE TABLE '+@TabName 
	BEGIN TRY
		EXEC(@sql)
	END TRY
	BEGIN CATCH
		UPDATE [master].[dbo].[Constraints] SET TruncateStatus=0 WHERE ID=@i
	END CATCH
	SET @Sql='' 
	SET @i=@i+1 
END 

/*** Check for the errors in Truncating the Tables ***/

IF NOT EXISTS(SELECT 1 FROM [master].[dbo].[Constraints] WHERE TruncateStatus=0)
BEGIN
	PRINT'All specified Tables are Truncated successfully' 
END
ELSE
BEGIN
	PRINT'A result set retrived with the Failed Statements'
	SELECT CreateStmt AS'FailedtoTruncate'FROM [master].[dbo].[Constraints] WHERE TruncateStatus=0
END

/*** Recreate all Constraints in the database and update the Constraints table for failed statements ***/ 
/*** From Constraints Table Status 1 - Success and Status 0 - Failed ***/

PRINT' ' 
PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
PRINT '@@@@@@@@@@@@@@   Recreate Constraints   @@@@@@@@@@@@@@@@' 
PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
PRINT' '

SELECT @Max=COUNT(1)FROM [master].[dbo].[Constraints]  
SET @i=1 
WHILE(@i<=@Max) 
	BEGIN 
		SELECT @Sql=CreateStmt FROM [master].[dbo].[Constraints] WHERE ID=@i 
	BEGIN TRY
		EXEC(@Sql) 
	END TRY
	BEGIN CATCH
		UPDATE [master].[dbo].[Constraints] SET CreateStatus=0 WHERE ID=@i
	END CATCH
	SET @Sql='' 
	SET @i=@i+1 
END 

/*** Check for the errors in recreating the constraints ***/
IF NOT EXISTS(SELECT 1 FROM [master].[dbo].[Constraints] WHERE CreateStatus=0)
	PRINT'All foreign keys are recreated successfully'
ELSE
	PRINT'Foreign keys are recreated with errors look into Summary'

/***** Summary   ******/
PRINT' '
PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
PRINT'@@@@@@@@@@@@@@@@@@@@@@@  SUMMARY  @@@@@@@@@@@@@@@@@@@@@@'
PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
PRINT' '

IF NOT EXISTS(SELECT 1 FROM [master].[dbo].[Constraints] WHERE CreateStatus=0)
BEGIN
	PRINT'All foreign keys are recreated successfully'
END
ELSE
BEGIN
	PRINT'A result set retrived with the Failed Statements'
	SELECT CreateStmt AS'FailedtoRecreate'FROM [master].[dbo].[Constraints] WHERE CreateStatus=0
END

/**** Second Attempt to Recreating the failed constraints if any ****/

IF EXISTS(SELECT 1 FROM [master].[dbo].[Constraints] WHERE CreateStatus=0)
BEGIN
	PRINT' '
	PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
	PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
	PRINT'@@@@@@ Final Atempt to Recreate Constraints   @@@@@@@@@@'
	PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
	PRINT'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
	PRINT' '

	DECLARE @Recreate Table(ID INT IDENTITY,ConID INT,CreateStmt VARCHAR(1000))
	INSERT INTO @Recreate (ConID,CreateStmt)SELECT ID,CreateStmt FROM [master].[dbo].[Constraints] WHERE CreateStatus=0
	SELECT @Max=COUNT(1)FROM @Recreate
	SET @i=1 
	SET @sql=''
	WHILE(@i<=@Max)
		BEGIN 
		SELECT @Sql=CreateStmt FROM @Recreate WHERE ID=@i   
		BEGIN TRY
			EXEC(@Sql)
			UPDATE [master].[dbo].[Constraints] SET CreateStatus=1 WHERE ID IN(
			SELECT ConID FROM @Recreate WHERE ID=@i)
		END	TRY
		BEGIN	CATCH
			PRINT'Final Attmept also Failed to Execute '+@sql
		END CATCH
		SET @Sql='' 
		SET @i=@i+1 
		END
	PRINT' '
	IF NOT EXISTS(SELECT 1 FROM [master].[dbo].[Constraints] WHERE CreateStatus=0)
	PRINT'All foreign keys are recreated successfully in Final Attempt'
	ELSE
	PRINT'Final Attempt is also filed in recreating all foreign keys there might be problem with domain intigrity'
	PRINT' '

END

END

Example: