Drop a Column and All Corresponding Constraints

-- Author: Andy Brown (andy.brown@mm-games.com)

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

CREATE PROCEDURE spDropColumnAndConstraints
@table sysname = 'DefaultTable',
@column sysname = 'DefaultColumn',
@DropConstraints int = 0,
@DropColumn int = 0
AS 
BEGIN
SET NOCOUNT ON

DECLARE @tableID int
DECLARE @constraint sysname
DECLARE @constrainttype nchar(1)
DECLARE @sql nvarchar(4000)

SET @tableID = object_id(@table)

DECLARE cur_constraints CURSOR FOR
SELECT Cons.xtype, 
Cons.[name]
FROM[sysobjects] as Cons WITH(NOLOCK)
INNER JOIN
(select[id],
colid 
FROMsyscolumns WITH(NOLOCK)
WHEREid = @tableID
AND name = @column) AS Cols
ONCons.parent_obj = Cols.id
AND Cons.info = Cols.colid

WHERECons.xtype in ('C', 'F', 'PK', 'UQ', 'D')
AND @tableID = Cons.parent_obj

IF EXISTS (SELECT1 
FROMsysobjects WITH(NOLOCK)
INNER JOIN
syscolumns WITH(NOLOCK) 
ONsysobjects.[id] = syscolumns.[id]
WHEREsyscolumns.name = @column 
ANDsysobjects.name = @table)
BEGIN
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
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

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
END
ELSE
PRINT 'FOUND COLUMN:'+@column+' IN TABLE:'+@table
CLOSE cur_constraints
DEALLOCATE cur_constraints
END
ELSE
PRINT 'The column "'+@column+'" or the table "'+@table+'" does not exist.'
END
GO