Dropping Unique Constraints in SQL Server

The problem

I got started with relational databases with mysql,  so I’m in the habit of making database changes with SQL scripts,  rather than using a GUI.  Microsoft SQL Server requires that we specify the name of a unique constraint when we want to drop it.  If you’re thinking ahead,  you can specify a name when you create the constraint;  if you don’t,  SQL Server will make up an unpredictable name,  so you can’t write a simple script to drop the constraint.

A Solution

In the spirit of “How To Drop A Primary Key in SQL Server“,  here’s a stored procedure that queries the data dictionary to find the names of any unique constraint on a specific table and column and drop them:

CREATE PROCEDURE [dbo].[DropUniqueConstraint]
    @tableName NVarchar(255),
    @columnName NVarchar(255)
AS
    DECLARE @IdxNames CURSOR
   
    SET @IdxNames = CURSOR FOR
        select sysindexes.name from sysindexkeys,syscolumns,sysindexes
            WHERE
                syscolumns.[id] = OBJECT_ID(N'[dbo].['+@tableName+N']')
                AND sysindexkeys.[id] = OBJECT_ID(N'[dbo].['+@tableName+N']')
                AND sysindexes.[id] = OBJECT_ID(N'[dbo].['+@tableName+N']')
                AND syscolumns.name=@columnName
                AND sysindexkeys.colid=syscolumns.colid
                AND sysindexes.[indid]=sysindexkeys.[indid]
                AND (
                    SELECT COUNT(*) FROM sysindexkeys AS si2
                    WHERE si2.id=sysindexes.id
                    AND si2.indid=sysindexes.indid
                )=1

    OPEN @IdxNames
    DECLARE @IdxName Nvarchar(255)
    FETCH NEXT FROM @IdxNames INTO @IdxName    

    WHILE @@FETCH_STATUS = 0 BEGIN
        DECLARE @dropSql Nvarchar(4000)

        SET @dropSql=
            N'ALTER TABLE  ['+@tableName+ N']
                DROP CONSTRAINT ['+@IdxName+ N']'
        EXEC(@dropSql)
           
        FETCH NEXT FROM @IdxNames
        INTO @IdxName
    END
CLOSE @IdxNames
DEALLOCATE @IdxNames

Usage is straightforward:

EXEC [dbo].[DropUniqueConstraint]
    @tableName='TargetTable',
    @columnName='TargetColumn'

This script has a limitation:  it only drops unique constraints that act on a single column,  not constraints that act on multiple columns.   It is smart enough,  however,  to not drop multiple-column constraints in case one of them involves @columnName.

Feedback from SQL stored procedure wizards would be mostly certainly welcome.