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)

Deal with multiple .vsmdi-files in sourcecontrol

If you ever have worked in a large project you have probably seen that there are multiple .vsmdi-files under sourcecontrol after a while. These files contains information about the testruns in the project and if the current file is checked out to another developer you will create a new such file if you change the testrun.

There is a good reason for not having these files under sourcecontrol.

Almost all developers have their own way of viewing files and especilly testruns and if this file is checked in and somebody makes a change that change will affect all other developers when they perform a "get latest"

The below blogpost is very informativ in specifying in how you can remove these files from sourcecontrol and make it possible for every developer to create their own testruns.

http://seriouscodage.blogspot.com/2010/03/multiple-vsmdi-files-with-vs2008-path.html