Andy's script is great but not so clean.

I ran it against a SQL2000 database trying to drop a column along with all its constraints/indexes. Here are the issues I faced:

1. The criteria of the constraints cursor should be changed to

WHERE ((OBJECTPROPERTY(Cons.[id],'IsConstraint') = 1

OR (OBJECTPROPERTY(Cons.[id],'IsPrimaryKey') = 1))

AND Cons.info = Cols.colid)

AND Cons.parent_obj = @tableID

as you don’t want to drop the primary key if the given column is not part of it.

2. The join criteria for the sysindexes table in the indexes cursor should include a fourth line as follows:

INNER JOIN dbo.sysindexkeys SIK WITH (NOLOCK)

ON SIK.id = SC.id

AND SIK.colid = SC.colid

AND SIK.indid = SI.indid

otherwise the cursor will return duplicate rows.

3. The second line of the where criteria of the indexes cursor must be changed as follows:

WHERE SI.indid !=0

AND ISNULL(OBJECTPROPERTY(OBJECT_ID(SI.name),'IsConstraint'), 0) = 0

AND SC.id = @tableID

AND SC.name = @column

as many (valid) rows return a NULL value there

4. The statement that drops the index should be:

SELECT @sql = N'DROP INDEX '+@table+'.'+@index

5. The commit/rollback block should be:

IF @@TRANCOUNT > 0

BEGIN

IF @rollback = 0

COMMIT TRANSACTION

ELSE

ROLLBACK TRANSACTION

END

as you want to commit/rollback only if a transaction has been started.

These are all the things I had to change in order to have the script running properly, according to my testing. I hope this helps others, too, enjoying Andy’s great script !