SQL Server – drop and alter constraints

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.
    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) + ');' 
    sys.foreign_keys fk
    sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
    sys.schemas s ON fk.schema_id = s.schema_id
    sys.tables t ON fkc.referenced_object_id = t.object_id
    sys.schemas ss ON t.schema_id = ss.schema_id
    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): 

Sorry for  the bad resolution, the scaling drives me a bit mad sometimes



Leave a Reply

Your email address will not be published. Required fields are marked *