MS SQL 2005: dropping Foreign/Unique Keys, Indices

It is quite easy to create an index or a foreign/unique key. It is easy to drop it if you know its name, but sometimes you have no means of specifying a name of a constraint and MS SQL Server generates one.

Here are some procedures to help dealing with that:

-- drop existing foreign key from baseTable which is referenced from refTable

-- however this is for case of just one foreign key between tables

CREATE PROCEDURE DropForeignKey @baseTable varchar(50), @refTable varchar(50)

AS

BEGIN

declare @keyname as varchar(100);

select @keyname = sys.foreign_keys.name from sys.tables parent

inner join sys.foreign_keys on parent.object_id = sys.foreign_keys.parent_object_id

inner join sys.tables child on child.object_id = sys.foreign_keys.referenced_object_id

where parent.name = @baseTable

and child.name = @refTable;

PRINT 'dropping foreign key '+ @keyname +' from table '+ @baseTable;

EXEC('ALTER TABLE '+ @baseTable +' DROP CONSTRAINT '+ @keyname);

END

go

-- drop a unique key from a column columnName on baseTable

CREATE PROCEDURE DropUniqueKey @baseTable varchar(50), @columnName varchar(50)

AS

BEGIN

declare @indexname as varchar(100);

SELECT @indexname = sys.indexes.name

from sys.objects

inner join sys.index_columns on sys.objects.object_id = sys.index_columns.object_id

inner join sys.indexes on sys.index_columns.object_id = sys.indexes.object_id and sys.index_columns.index_id = sys.indexes.index_id

inner join sys.columns on sys.index_columns.object_id = sys.columns.object_id and sys.index_columns.column_id = sys.columns.column_id

where sys.objects.name = @baseTable

and sys.columns.name = @columnName;

PRINT 'dropping unique key '+ @indexname +' from table '+ @baseTable;

EXEC('ALTER TABLE '+ @baseTable +' DROP CONSTRAINT '+ @indexname);

END

go

-- drop index from a column columnName on baseTable

CREATE PROCEDURE DropIndex @baseTable varchar(50), @columnName varchar(50)

AS

BEGIN

declare @indexname as varchar(100);

SELECT @indexname = sys.indexes.name

from sys.objects

inner join sys.index_columns on sys.objects.object_id = sys.index_columns.object_id

inner join sys.indexes on sys.index_columns.object_id = sys.indexes.object_id and sys.index_columns.index_id = sys.indexes.index_id

inner join sys.columns on sys.index_columns.object_id = sys.columns.object_id and sys.index_columns.column_id = sys.columns.column_id

where sys.objects.name = @baseTable

and sys.columns.name = @columnName;

PRINT 'dropping index '+ @indexname +' from table '+ @baseTable;

EXEC('DROP INDEX '+ @baseTable +'.'+ @indexname);

END

go