Before I get into this, I’m hoping that you’re aware that deleting foreign key constraints is a bad idea, and unless you have a good reason to do so, or at least have the ability to re-apply them, then leave them be.

That said, this script below will print out the commands to delete an entire schemas foreign keys.

Tomorrow I will post a more elegant solution to dropping foreign keys, and also a method to script out the create commands before you drop them so that you can re-apply them.


USE AdventureWorks2014
GO
; WITH FK_CTE
(
Fk_Schema,
FK_Table,
FK_Name
)
AS
(
SELECT
TABLE_SCHEMA,
TABLE_NAME,
CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
TABLE_SCHEMA = 'Sales'
AND
CONSTRAINT_TYPE = 'FOREIGN KEY'
)

SELECT
'ALTER TABLE '
+ QUOTENAME(Fk_Schema)
+'.'
+ QUOTENAME (FK_Table)
+'DROP CONSTRAINT'
+ QUOTENAME (FK_Name)
FROM FK_CTE