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.
 */
  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): 

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

//mattssok

mattssok

Leave a Reply

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