Truncate Table referenced by a Foreign key
Last night, I was truncating some tables from an alien database, and while truncating one table I got below error, which is logical because tables were related by foreign key constraint
Msg 4712, Level 16, State 1, Line 1
Cannot truncate table ' ' because it is being referenced by a FOREIGN KEY constraint.
That particular database contains 100 tables and on creating the Database Diagram, I found that there are more than 15 tables related to each other.
Now to truncate all the tables, I have written a recursive procedure, which will perform following operation, starting from the leaf table (table which is not referenced by any table)
1. Drop the constraint
2. Truncate the table
3. Create the constraints back
/****** Object: StoredProcedure [dbo].[uspTruncateTableWithForeignKey] Script Date: 7/17/2015 1:45:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*****************************************************************************
PROCEDURE NAME: [uspTruncateTableWithForeignKey]
AUTHOR: Siddharth Tandon
CREATED: 04/10/2015
DESCRIPTION: This will truncate the table (sent as parameter) and all the tables related to it
PARAMETERS
@TableName: Name of the tbale to truncate
@CosntraintName: Pass NULL
@SchemaName: Schema of the table
EXEC [dbo].[uspTruncateTableWithForeignKey] 'TableName',NULL,'dbo'
****************************************************************************/
CREATE PROC [dbo].[uspTruncateTableWithForeignKey]
@TableName varchar(100)
,@ConstraintName varchaR(1000) = NULL
,@SchemaName varchar(100)
AS
BEGIN
DECLARE
@Index int = 1
,@Count int = 0
,@TmpTableName varchar(100)
,@Sql nvarchar(1000)
,@TmpConstraintName varchar(1000)
,@TmpSchemaName varchar(100)
,@TmpColumnName varchar(100)
,@TmpPKTableName varchaR(100)
,@TmpPKSchemaName varchar(100)
,@TmpPKColumnName varchar(100)
,@BaseTableName varchar(100)
,@TmpDeleteAction varchaR(100)
,@TmpUpdateAction varchar(100)
DECLARE @Tables TABLE
(
ID int IDENTITY(1,1),
TableName varchar(100),
ConstraintName varchaR(1000),
SchemaName varchar(1000)
)
INSERT INTO @Tables (TableName,ConstraintName,SchemaName)
SELECT OBJECT_NAME(parent_object_id),name,SCHEMA_NAME(schema_id)
FROM sys.foreign_keys
WHERE OBJECT_NAME(referenced_object_id) = @TableName
AND OBJECT_SCHEMA_NAME(referenced_object_id) = @SchemaName
IF object_id('tempdb..#tmpRelationship') IS NULL
BEGIN
SET @BaseTableName = @TableName
SET @ConstraintName = NULL
CREATE TABLE #tmpRelationship
(
ID int IDENTITY(1,1)
,ConstraintName nvarchar(1000)
,FKTableName varchar(100)
,FKColumnName varchar(100)
,FKTableSchemaName varchar(100)
,PKTableNAme varchar(100)
,PKColumnName varchar(100)
,PKTableSchemaName varchaR(100)
,DeleteAction varchar(100)
,UpdateAction varchar(100)
)
END
SELECT @COUNT = COUNT(*)
FROM @Tables
IF(@Count > 0)
BEGIN
WHILE(@Index<=@Count)
BEGIN
SELECT
@TmpTableName = TableName
,@TmpConstraintName = ConstraintName
,@TmpSchemaName = SchemaName
FROM @Tables
WHERE ID = @Index
EXEC [dbo].[uspTruncateTableWithForeignKey] @TmpTableName,@TmpConstraintName,@TmpSchemaName
SET @Index += 1
END
END
IF(@ConstraintName IS NOT NULL)
BEGIN
INSERT INTO #tmpRelationship
(
ConstraintName
,FKTableName
,FKColumnName
,FKTableSchemaName
,PKTableNAme
,PKColumnName
,PKTableSchemaName
,DeleteAction
,UpdateAction
)
SELECT
KCU1.CONSTRAINT_NAME AS FKConstraint
,KCU1.TABLE_NAME AS FKTable
,KCU1.COLUMN_NAME AS FKColumn
,KCU1.TABLE_SCHEMA AS FKSchema
,KCU2.TABLE_NAME AS ReferencedTable
,KCU2.COLUMN_NAME AS ReferencedColumn
,KCU2.TABLE_SCHEMA AS ReferencedSchema
,RC.DELETE_RULE
,RC.UPDATE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2
ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
WHERE KCU1.TABLE_NAME = @TableName
------------Drop all constraints related to it
SET @Sql = N'ALTER TABLE ['+@SchemaName+'].['+@TableName+'] DROP CONSTRAINT ['+@ConstraintName+']'
EXEC sp_Executesql @Sql
END
SET @Sql = 'TRUNCATE TABLE ' + ISNULL(@SchemaName,'dbo') + '.[' + @TableName + ']'
EXEC sp_executesql @Sql
IF(@BaseTableName = @TableName)
BEGIN
SELECT @COUNT = COUNT(*)
FROM #tmpRelationship
SET @Index = 1
IF(@Count > 0)
BEGIN
WHILE(@Index<=@Count)
BEGIN
SELECT
@TmpTableName = FKTableName
,@TmpConstraintName = ConstraintName
,@TmpSchemaName = FKTableSchemaName
,@TmpColumnName = FKColumnName
,@TmpPKTableName = PKTableNAme
,@TmpPKColumnName = PKColumnName
,@TmpPKSchemaName = PKTableSchemaName
,@TmpDeleteAction = DeleteAction
,@TmpUpdateAction = UpdateAction
FROM #tmpRelationship
WHERE ID = @Index
SET @Sql = N'ALTER TABLE ['+@TmpSchemaName+'].['+@TmpTableName+']
WITH NOCHECK ADD FOREIGN KEY([' + @TmpColumnName + '])
REFERENCES ['+@TmpPKSchemaName+'].['+@TmpPKTableName+'] (['+@TmpPKColumnName+'])
ON DELETE '+ @TmpDeleteAction + '
ON UPDATE ' + @TmpUpdateAction
EXEC sp_Executesql @Sql
SET @Index += 1
END
END
END
END
GO