Delete all Foreign Keys The Brute Force Way
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