Published on: 09 Jan, 2021

How to delete all foreign keys constraint in TSQL?

Tumblr Digg Google Plus

Inside the INFORMATION_SCHEMA table, there is a table called TABLE_CONSTRAINTS which hold and store all the Foreign Key constraint. 

Here is the full TSQL query to delete all foreign key constraints of a database in SQL Server.


DECLARE @tableName VARCHAR(128)
DECLARE @constraintName VARCHAR(255)
DECLARE @SQL VARCHAR(500)

SELECT @tableName = (
	SELECT TOP 1 TABLE_NAME 
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
	WHERE constraint_catalog = DB_NAME() 
	AND CONSTRAINT_TYPE = 'FOREIGN KEY' 
	ORDER BY TABLE_NAME
)

WHILE @tableName is not null
	BEGIN
		SELECT @constraintName = (
			SELECT TOP 1 CONSTRAINT_NAME 
			FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
			WHERE constraint_catalog = DB_NAME() 
			AND CONSTRAINT_TYPE = 'FOREIGN KEY' 
			AND TABLE_NAME = @tableName 
			ORDER BY CONSTRAINT_NAME
		)
		WHILE @constraintName IS NOT NULL
		BEGIN
			SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@tableName) +'] DROP CONSTRAINT [' + RTRIM(@constraintName) +']'
			EXEC (@SQL)
			PRINT 'Deleted Foreign Key Constraint: ' + @constraintName + ' from table ' + @tableName
			SELECT @constraintName = (
				SELECT TOP 1 CONSTRAINT_NAME 
				FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
				WHERE constraint_catalog = DB_NAME() 
				AND CONSTRAINT_TYPE = 'FOREIGN KEY' 
				AND CONSTRAINT_NAME <> @constraintName 
				AND TABLE_NAME = @tableName 
				ORDER BY CONSTRAINT_NAME
			)
		END
	SELECT @tableName = (
			SELECT TOP 1 TABLE_NAME 
			FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
			WHERE constraint_catalog = DB_NAME() 
			AND CONSTRAINT_TYPE = 'FOREIGN KEY' 
			ORDER BY TABLE_NAME
		)
	END
GO

The function DB_NAME() will return the currently connected database in SQL Server. Basically, the idea of the above code is performing two while loop. The first while loop is to get the top 1 of the user's creation table. Then it will perform a loop against this table and look for any existing foreign key constraint and perform a deletion until the constraint is no longer found. Once this will loop is completed, it will loop against the next user creation table and perform again the deletion of foreign key constraint until all are completely deleted. Both while loop will be completed while there are no longer foreign key constraints found.

Comments

There are no comments available.

Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.