How to drop all constraints of a table column in MS SQL Server (T-SQL)

In SQL Server, before a table column can be dropped, all constraints involving the column have to be dropped. The procedure below can be used to do that.

---

-- Drops all constraints that include the specified table column.

-- Author: Christian d'Heureuse, www.source-code.biz

create procedure dbo.DropAllColumnConstraints

@tableName varchar(128),

@columnName varchar(128)

as
set nocount on

set xact_abort on

while 0=0 begin

declare @constraintName varchar(128)

set @constraintName = (

select top 1 constraint_name

from information_schema.constraint_column_usage

where table_name = @tableName and column_name = @columnName )

if @constraintName is null break

exec ('alter table "'+@tableName+'" drop constraint "'+@constraintName+'"')

end

Example of how to use the procedure:

exec DropAllColumnConstraints 'table1', 'column1'