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
					AND	Cons.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