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]';
The output from this query can look like this (yes I have masked the table names a bit):