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

Book: Effective Java

Recently I bought this extraordinary book: Effective Java by Joshua Bloch. I really think this is a must-read for anyone who considers her/himself a Java Programmer.

So far I have met only one person who learnt most of this book's wisdom himself without actually reading it. And this person is really good at programming, probably the best I've known.

So, in short, if you are not a Java-genius (sadly I am not one) you must read it.

Foreword

I have been working with IT- and, particularly, Java-related technologies for more than 6 years now, and decided to start his blog more as a notebook to myself about various problems in programming I come across every now and then - ranging from system or component architecture to simple proper usage of a library.

However, if these notes might help others it would be great.

P.S. English is not my native language, hence I apologise in advance.