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'

Author: Christian d'Heureuse (www.source-code.biz, www.inventec.ch/chdh)
License: Free / LGPL
Index