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

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s