Tag Archives: Foreign Key Constraint

Truncate all/all except few/specified Tables of a Database in Sql Server

This article presents how we can generate a script to truncate all tables/all tables except few specified tables/specified tables of a Database in Sql Server.

One of major the problem with table truncation is, we need to remove if there are any foreign key’s defined in other tables which references the columns in the table to be truncated. So to truncate a table we need to first remove all the foreign key references then truncate the table and finally add back the removed foreign key constraints.

Let us first create a demo database with sample tables as shown in the below image by the by the following script:

TruncateAllDemoDB

--Create demo database with tables having foreign
--key relations between them and sample data in it
SET NOCOUNT ON
GO
CREATE DATABASE SqlhintsTruncateDBDemo
GO
USE SqlhintsTruncateDBDemo
GO
CREATE TABLE [dbo].[Customer] (
    [CustID] [int] NOT NULL ,
    CONSTRAINT [PK_Customer] PRIMARY KEY  CLUSTERED 
    ( [CustID] ) 
) 
GO
CREATE TABLE [dbo].[Order] (
    [OrderID] [int] NOT NULL ,
    [CustID] [int] NOT NULL ,
    CONSTRAINT [PK_Order] PRIMARY KEY  CLUSTERED 
    ( [OrderID] ),
    CONSTRAINT [FK_Order_Customer] FOREIGN KEY
    ( [CustID] ) REFERENCES [dbo].[Customer] (CustID) 
) 
GO
CREATE TABLE [dbo].[OrderItem] (
    [OrderItemId] [int] NOT NULL ,
    [OrderId] [int] NOT NULL ,
    CONSTRAINT [PK_OrderItem] PRIMARY KEY  CLUSTERED 
    ( [OrderItemId] ),
    CONSTRAINT [FK_OrderItem_Order] FOREIGN KEY
    ([OrderId]) REFERENCES [dbo].[Order] ([OrderId]) 
) 
GO

We can use the below script to generate the script to truncate all the tables of a database.

USE SqlhintsTruncateDBDemo
GO
SET NOCOUNT ON
GO
--Get the list of all the tables to be truncated
 DECLARE @TablesToBeTruncated AS TABLE
 (Id INT IDENTITY(1,1),TableObjectId INT, TableName SYSNAME,
	SchemaId INT)
INSERT INTO @TablesToBeTruncated
 SELECT ST.object_id,ST.name,ST.schema_id
 FROM sys.Tables ST
 WHERE ST.type = 'U' AND ST.NAME NOT LIKE '#%' 
 AND ST.name <> 'sysdiagrams'
 --AND ST.NAME NOT IN ('') -- Specify here the comma separated table names for which truncation is not required
 --AND ST.NAME IN ('') -- Specify here the comma separated table names which needs to be truncated

 --Generate the foreignkeys drop and create back script 
DECLARE @CreateScript AS NVARCHAR(MAX), @DropScript AS NVARCHAR(MAX)
SELECT 
	------------DROP SCRIPT--------------------
	@DropScript = ISNULL(@DropScript,'') + 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(Tlist.SchemaId)) + '.'
	 + QUOTENAME(OBJECT_NAME(FKey.parent_object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(FKey.name)
	 + CHAR(10),
	 -----------CREATE BACK SCRIPT-------------
	@CreateScript = ISNULL(@CreateScript,'') + 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(Tlist.SchemaId)) + '.'
	 + QUOTENAME(OBJECT_NAME(FKey.parent_object_id)) + ' ADD CONSTRAINT ' + QUOTENAME(FKey.name)
	 + ' FOREIGN KEY ' + '(' + STUFF(( -- Get the list of columns
				 SELECT ',' + QUOTENAME(COL_NAME(FKeyCol.parent_object_id, FKeyCol.parent_column_id))
				 FROM SYS.FOREIGN_KEY_COLUMNS FKeyCol
				 WHERE FKey.OBJECT_ID = FKeyCol.constraint_object_id
				 ORDER BY FKeyCol.constraint_column_id
				 FOR XML PATH('')),1,1,'') + ')'
	 + ' REFERENCES ' + QUOTENAME(SCHEMA_NAME(Tlist.SchemaId)) + '.' 
				+ QUOTENAME(OBJECT_NAME(FKey.referenced_object_id)) + ' (' + STUFF(( -- Get the list of columns
				SELECT ',' + QUOTENAME(COL_NAME(FKeyCol.referenced_object_id, FKeyCol.referenced_column_id))
				FROM SYS.FOREIGN_KEY_COLUMNS FKeyCol
				WHERE FKey.OBJECT_ID = FKeyCol.constraint_object_id
				ORDER BY FKeyCol.constraint_column_id
				FOR XML PATH('')),1,1,'') + ') '
	 + CASE WHEN update_referential_action_desc = 'CASCADE' THEN ' ON UPDATE CASCADE'
	        WHEN update_referential_action_desc = 'SET_DEFAULT' THEN ' ON UPDATE SET DEFAULT'
	        WHEN update_referential_action_desc = 'SET_NULL' THEN ' ON UPDATE SET NULL'
	        ELSE '' 
	   END
	 + CASE WHEN delete_referential_action_desc = 'CASCADE' THEN ' ON DELETE CASCADE'
			WHEN delete_referential_action_desc = 'SET_DEFAULT' THEN ' ON DELETE SET DEFAULT'
			WHEN delete_referential_action_desc = 'SET_NULL' THEN ' ON DELETE SET NULL'
			ELSE ''
	   END  + CHAR(10)
 FROM @TablesToBeTruncated Tlist
			INNER JOIN SYS.FOREIGN_KEYS FKey
				ON Tlist.TableObjectId = FKey.referenced_object_id

--PRINT THE TRUNCATION SCRIPT
IF LEN(ISNULL(@DropScript,'')) > 0
 BEGIN
	 PRINT CHAR(10) + ' GO ' + CHAR(10) + '--------DROP FOREIGN KEY CONSTRAINTS SCRIPT--------'
	 PRINT @DropScript + CHAR(10) + ' GO ' + CHAR(10)
 END

PRINT '--------TRUNCATE TABLES SCRIPT--------'
--TRUNCATE TABLES
DECLARE @id INT,@truncatescript NVARCHAR(MAX)
SELECT @id = MIN(Id)FROM @TablesToBeTruncated
WHILE @id is not null
 BEGIN
	 SELECT @truncatescript = 'TRUNCATE TABLE ' + QUOTENAME(SCHEMA_NAME(SchemaId)) + '.' + QUOTENAME(TableName) 
	 FROM @TablesToBeTruncated WHERE Id = @id
	 PRINT @truncatescript
	 SELECT @id = MIN(Id)FROM @TablesToBeTruncated WHERE Id > @id
 END

IF LEN(ISNULL(@CreateScript,'')) > 0
 BEGIN
	 PRINT CHAR(10) + ' GO ' + CHAR(10) + '--------CREATE BACK THE FOREIGN KEY CONSTRAINTS SCRIPT--------'
	 PRINT CAST((@CreateScript + CHAR(10) + ' GO ' + CHAR(10)) AS NTEXT)
 END
 GO

Below is the result of executing the above script:

TruncateAllTableDataResult

Disclaimer: As TRUNCATE table removes all the records from the table permanently. So, think twice before executing the truncate table statement.

By default the above script generates the script to truncate all the tables of a database. If need is to truncate only the specified tables then uncomment the line number 15 (i.e.–AND ST.NAME IN (”)) in the above script and mention the list of tables to be truncated. And if requirement is to truncate all the tables except few tables, then uncomment only the line number 14 (i.e. –AND ST.NAME NOT IN (”)) in the above script and mention the list of tables which shouldn’t be considered for truncation.

Cannot truncate table ‘xyz’ because it is being referenced by a FOREIGN KEY constraint – Sql Server

In this article we will discuss on when we get the error like below in Sql Server and how to resolve it.

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

To demonstrate this error let us first create a demo db ‘SqlhintsTruncateDemo’, two tables Customer and Order.

CREATE DATABASE SqlhintsTruncateDemo
GO
USE SqlhintsTruncateDemo
GO
CREATE TABLE [dbo].[Customer] (
	[CustID] [int] IDENTITY (1, 1) NOT NULL ,
	CONSTRAINT [PK_Customer] PRIMARY KEY  CLUSTERED 
	(
		[CustID]
	) 
) 
GO
CREATE TABLE [dbo].[Order] (
	[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
	[CustID] [int] NOT NULL ,
	CONSTRAINT [PK_Order] PRIMARY KEY  CLUSTERED 
	(
		[OrderID]
	),
	CONSTRAINT [FK_Order_Customer] FOREIGN KEY 
	(
		[CustID]
	) REFERENCES [dbo].[Customer] (
		[CustID]
	) 
) 
GO

[ALSO READ] Truncate all/all except few/specified Tables of a Database in Sql Server

In the above script we have created a foreign key constraint FK_Order_Customer on the CustID column of the Order table which is referring to the CustID primary key column of the Customer table.

Disclaimer: As TRUNCATE table removes all the records from the table. Be careful before issuing this command.

Now try to truncate the Order Table

TRUNCATE TABLE [dbo].[Order]

RESULT:
Truncate Table Successful

As per the above result truncation of the Order table is successful. Now try to truncate the Customer Table

TRUNCATE TABLE [dbo].[Customer]

RESULT:
Truncate Table Failure

As per the result the truncation of the Customer table is failing, because the
CustID column of the Customer table is referenced by the CustID column of the Order Table.

If we still want to Truncate the table, then we have to drop all the foreign key
constraints which are referring to the table to be truncated.

So now drop the foreign key constraint FK_Order_Customer and then try truncating
the Customer table.

ALTER TABLE [dbo].[Order]
DROP CONSTRAINT FK_Order_Customer
GO
TRUNCATE TABLE [dbo].[Customer]
GO

RESULT:
Truncate Table Successful After Dropping of Foreign Key Constraint

As per the above result it is clear that now the table truncation is successful after dropping all the foreign key constraints which are refering to the table to be truncated.

We can use script like below to identify whether a Table is referenced by another Tables foreign key constraints in Sql Server.

SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table',
OBJECT_NAME(FK.parent_object_id) 'Referring Table',
FK.name 'Foreign Key',
COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column',
COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column'
FROM sys.foreign_keys AS FK
INNER JOIN sys.foreign_key_columns AS FKC
ON FKC.constraint_object_id = FK.OBJECT_ID
WHERE OBJECT_NAME (FK.referenced_object_id) = 'Enter Table Name'

In the above script replace the string ‘Enter Table Name’ with the table name for which you want to find out the referencing tables and the refering foreign key constraint name.

[ALSO READ] Truncate all/all except few/specified Tables of a Database in Sql Server

How to find whether a Table is referenced by the Foreign Key constraint defined in another Table – sql server

We can use script like below to identify whether a Table is referenced by another Tables foreign key constraints in Sql Server:

ALSO READ: How to find all dependencies of a table in Sql Server?

SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table', 
 OBJECT_NAME(FK.parent_object_id) 'Referring Table', 
 FK.name 'Foreign Key', 
 COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column',
 COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column'
FROM sys.foreign_keys AS FK
		INNER JOIN sys.foreign_key_columns AS FKC 
			ON FKC.constraint_object_id = FK.OBJECT_ID
WHERE OBJECT_NAME (FK.referenced_object_id) = 'Enter Table Name'

ALSO READ: How to find referenced/dependent objects (like Table, Function etc) of a Stored Procedure/Function in Sql Server?

Let us understand this with an example:

Below script creates a database SqlHintsDemo and adds to tables Customer and Order to it. Here foreign key constraints FK_Order_Customer defined on the Order Table CustID column refers to the CustomerId column of the Customer table.

CREATE DATABASE SqlHintsDemo
GO
USE SqlHintsDemo
GO
CREATE TABLE [dbo].[Customer] (
[CustomerId] [int] IDENTITY (1, 1) NOT NULL 
 PRIMARY KEY  CLUSTERED) 
GO
CREATE TABLE [dbo].[Order] (
	[OrderID] [int] IDENTITY (1, 1) NOT NULL 
         PRIMARY KEY  CLUSTERED ,
	[CustID] [int] NOT NULL ,	
	CONSTRAINT [FK_Order_Customer] FOREIGN KEY 
	([CustID]) REFERENCES [dbo].[Customer] ([CustomerId]) 
) 
GO

We can use a script like below to identify whether the Customer Table is referred by any other Tables foreign key constraint:

SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table', 
 OBJECT_NAME(FK.parent_object_id) 'Referring Table', FK.name 'Foreign Key', 
 COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column',
 COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column'
FROM sys.foreign_keys AS FK
		INNER JOIN sys.foreign_key_columns AS FKC 
			ON FKC.constraint_object_id = FK.OBJECT_ID
WHERE OBJECT_NAME (FK.referenced_object_id) = 'Customer'

RESULT:
Table Referenced by Another Tables Foregin Key Constraint