I had a bit of a problem in work. I had to write some SQL to drop a uniqueness constraint on a column but was unsure of the constraint name (since it was auto generated with a GUID on the end). Looking at the MSDN ‘Alter table’ documentation, it seems that you need the constraint name to drop it (please correct me if I’m wrong). I knew the table and the column names, so this is what I needed to work with.

So here’s what I came up with and this did the job:

DECLARE @constraintName VARCHAR(50)

select @constraintName = CONSTRAINT_NAME
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = '[YOUR_TABLENAME]' AND COLUMN_NAME = '[YOUR_COLUMNNAME]'

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[YOUR_TABLENAME]') AND name = @constraintName)
    EXEC('ALTER TABLE [YOUR_TABLENAME] DROP CONSTRAINT ' + @constraintName)

This worked for me since I know that the column will only have one constraint. Presumably the CONSTRAINT_COLUMN_USAGE will return multiple rows if there are multiple constraints. I also did the EXEC to get past the limitation that the DROP CONSTRAINT call doesn’t accept a variable.

If anyone has any comments/improvements on the above, I’d appreciate if you let me know.