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.