Hi all! This week we will have a look at an easy and convenient way to get around those situations when you have been a good developer and used you constraints. (As you should.) However, for some reason you need to make changes to your data or table structure which said constraints hinders you to do. And since you’ve created quite a few tables by now you have no clue where to find the constraint that obstructs you from dropping that table.
In this situation the following query comes in really hand. (Beware, when you run it, make sure to save the output from both columns since if you re-run it after dropping the constraints naturally you won’t get the re-enable logic back this time.)
Also, since I am a firm believer of giving credit where credit is due I want to post the link to what I think was my original source for this: http://www.grapefruitmoon.net/t-sql-to-drop-all-foreign-keys-that-reference-a-table/
/*
* Drop and re-enable constraints
* This query is perfect for when you work with your data and need to
* do changes, reloads, etc. that's hindered by the constraints.
*/
SELECT
DropStatement =
'ALTER TABLE ' + s.name + '.' + OBJECT_NAME(fk.parent_object_id)
+ ' DROP CONSTRAINT ' + fk.NAME + ' ;'
,CreateStatement =
'ALTER TABLE ' + s.name + '.' + OBJECT_NAME(fk.parent_object_id)
+ ' ADD CONSTRAINT ' + fk.NAME + ' FOREIGN KEY ('
+ COL_NAME(fk.parent_object_id, fkc.parent_column_id)
+ ') REFERENCES ' + ss.name + '.'
+ OBJECT_NAME(fk.referenced_object_id) + '('
+ COL_NAME(fk.referenced_object_id, fkc.referenced_column_id) + ');'
FROM
sys.foreign_keys fk
INNER JOIN
sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN
sys.schemas s ON fk.schema_id = s.schema_id
INNER JOIN
sys.tables t ON fkc.referenced_object_id = t.object_id
INNER JOIN
sys.schemas ss ON t.schema_id = ss.schema_id
WHERE
OBJECT_NAME(fk.referenced_object_id) = '[TableName]'
AND ss.name = '[SchemaName]';
Output
The output from this query can look like this (yes I have masked the table names a bit):
//mattssok
