Removing, altering table and re-adding unknown constraints in SQL

Every now and then when you are writing update scripts for a database you need to drop and recreate a constraint. The problem you might face is that you haven't explicit created the constraint in the first place which means that the constraint haven't gotten a given name. With the following small script you will be able to locate the constraint, alter the table and then recreate the constraint.

!!
Be warned that the script has only been tested on basic constraints and there might be constraints that specify different options that this script will ignore. Be sure to test the script in some test environment before committing that the script works as intended
!!


Declare @tableName varchar(30) = '<tableName>'
Declare @constraintName varchar(50) =
(
   Select OBJECT_NAME(OBJECT_ID)
   From sys.objects
   Where type_desc Like '%CONSTRAINT'
   and OBJECT_NAME(parent_object_id) = @tableName
   and type_desc = 'UNIQUE_CONSTRAINT'
)

Declare @constraintDrop varchar(200) = 'Alter Table ' + @tableName + ' drop constraint ' + @constraintName

Declare @ColumnList varchar(max) =
(
   Select Stuff(
     (
       Select '[' + COLUMN_NAME + '] Asc, '
       From INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
       Where CONSTRAINT_NAME = @constraintName
       For Xml Path('')), 1, 0, ''
     )
)

Set @ColumnList = (Select LEFT(@ColumnList, LEN(@ColumnList) - 1) )

Declare @constraintAdd varchar(Max) = 'Alter Table ' + @tableName + ' Add Unique NonClustered (' + @ColumnList + ') On [Primary]'

exec(@constraintDrop)

-- Add alter/update script here

exec(@constraintAdd)