Drop a Column and its Constraints and Indexes

IF EXISTS (SELECT 1 
FROM sysobjects 
WHERE [name] = 'spDropColumnAndItsConstraintsAndIndexes'
AND xtype = 'P')
DROP PROCEDURE spDropColumnAndItsConstraintsAndIndexes
GO

CREATE PROCEDURE spDropColumnAndItsConstraintsAndIndexes
@table SYSNAME = 'DefaultTable',
@column SYSNAME = 'DefaultColumn',
@DropConstraints INT = 0,
@DropColumn INT = 0,
@DropIndexes INT = 0
AS 
BEGIN
SET NOCOUNT ON

DECLARE @rollback       INT
DECLARE @tableID        INT
DECLARE @constraint     SYSNAME
DECLARE @constrainttype NCHAR(1)
DECLARE @index          SYSNAME
DECLARE @sql            NVARCHAR(4000)

SET @tableID = OBJECT_ID(@table)
SET @rollback = 0

DECLARE cur_constraints CURSOR FOR
SELECT Cons.xtype, 
Cons.[name]
FROM dbo.sysobjects AS Cons WITH(NOLOCK)
INNER JOIN (
SELECT [id]
 , colid
FROM dbo.syscolumns WITH(NOLOCK)
WHERE id = @tableID
  AND name = @column) AS Cols
ON  Cons.parent_obj = Cols.id
WHERE ((OBJECTPROPERTY(Cons.[id],'IsConstraint') = 1
ANDCons.info = Cols.colid)
OR (OBJECTPROPERTY(Cons.[id],'IsPrimaryKey') = 1))
  AND Cons.parent_obj = @tableID

DECLARE cur_indexes CURSOR FOR
SELECT SI.name AS IndexName
FROM dbo.sysindexes SI WITH (NOLOCK)
INNER JOIN dbo.syscolumns SC WITH (NOLOCK)
ON  SI.id = SC.id
INNER JOIN dbo.sysindexkeys SIK WITH (NOLOCK)
ON  SIK.id = SC.id
AND SIK.colid = SC.colid
INNER JOIN dbo.sysobjects SO WITH (NOLOCK)
ON  SI.id = SO.id
WHERE SI.indid !=0
  AND OBJECTPROPERTY(OBJECT_ID(SI.name),'IsConstraint') = 0
  AND SC.id = @tableID
  AND SC.name = @column

IF NOT EXISTS (
SELECT 1 
FROM dbo.sysobjects SO WITH(NOLOCK)
INNER
JOIN dbo.syscolumns SC WITH(NOLOCK) 
ON SO.[id] = SC.[id]
WHERE SO.name = @table 
  AND SC.name = @column)
BEGIN
PRINT 'The column "'+@column+'" or the table "'+@table+'" does not exist.'
GOTO errorExit
END

BEGIN TRANSACTION

OPEN cur_constraints
FETCH NEXT FROM cur_constraints INTO @constrainttype, @constraint
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @sql = N'ALTER TABLE '+@table+N' DROP CONSTRAINT '+@constraint
IF @DropConstraints = 1
BEGIN
EXEC sp_executesql @sql
IF @@ERROR != 0
BEGIN
SET @rollback = 1
GOTO errorExit
END
PRINT 'DROPPING THE CONSTRAINT:'+@constraint+' (id:'+CAST(object_id(@constraint) AS VARCHAR)+') OF TYPE:'+@constrainttype+' FROM TABLE:'+@table
END
ELSE
PRINT 'FOUND THE CONSTRAINT:'+@constraint+' (id:'+CAST(object_id(@constraint) AS VARCHAR)+') OF TYPE:'+@constrainttype+' ON TABLE:'+@table
FETCH NEXT FROM cur_constraints INTO @constrainttype, @constraint
END
CLOSE cur_constraints

OPEN cur_indexes
FETCH NEXT FROM cur_indexes INTO @index
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @sql = N'ALTER TABLE '+@table+N' DROP INDEX '+@index
IF @DropConstraints = 1
BEGIN
EXEC sp_executesql @sql
IF @@ERROR != 0
BEGIN
SET @rollback = 1
GOTO errorExit
END
PRINT 'DROPPING THE INDEX:'+@index+' FROM TABLE:'+@table
END
ELSE
PRINT 'FOUND THE INDEX:'+@index+' ON TABLE:'+@table
FETCH NEXT FROM cur_indexes INTO @index
END
CLOSE cur_indexes

SET @sql = N'ALTER TABLE dbo.'+@table+N' DROP COLUMN '+@column
IF @DropColumn = 1
BEGIN
PRINT 'DROPPING COLUMN:'+@column+' FROM TABLE:'+@table
EXEC sp_executesql @sql
IF @@ERROR != 0
BEGIN
SET @rollback = 1
GOTO errorExit
END
END
ELSE
PRINT 'FOUND COLUMN:'+@column+' IN TABLE:'+@table
errorExit:
DEALLOCATE cur_indexes
DEALLOCATE cur_constraints
IF @rollback = 0 AND @@TRANCOUNT > 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
END
GO