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,
create procedure dbo.DropAllColumnConstraints
      @tableName varchar(128),
      @columnName varchar(128)
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'

Author: Christian d'Heureuse (,
License: Free / LGPL