Wednesday, November 3, 2010

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+'"')
Example of how to use the procedure:
exec DropAllColumnConstraints 'table1', 'column1'

No comments:

Post a Comment