I need to remove a unique constraints that I don't know the names of

Question

I maintain a product that is installed at multiple locations which as been haphazardly upgraded. Unique constraints were added to a number of tables, but I have no idea what the names are at any particular instance. What I do know is the table/columnname pair that has the unique constraints and I would like to write a script to delete any unique constraint on these column/table combinations.

This is SQL Server 2000 and later. Something that works on 2000/2005/2008 would be best!

Answer 1

This page has a quick and dirty way to pull out all the CONSTRAINTs in the database, and from there you could build up dynamic SQL to drop them:

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT';

Answer 2

This script would generate a list of ALTER TABLE..... DROP CONSTRAINT.... commands, which you can then copy+paste and execute (or tweak before executing as needed) to drop all unique constraints / unique indices:

SELECT 
    'ALTER TABLE ' + OBJECT_NAME(so.parent_obj) + ' DROP CONSTRAINT ' + so.name
FROM sysobjects so
WHERE so.xtype = 'UQ'

I hope it should work on all SQL Server versions from 2000 to 2008 R2.