删除具有约束constraint列的方法---T-sql

当删除一个表中的一个字段，恰好这个字段有默认值或是外键等约束的话，直接删除会报错

Msg 5074, Level 16, State 1, Line 32
The object 'DF__tmpTblFor__Incre__3D6081D7' is dependent on column 'ExampleColumn'.
Msg 4922, Level 16, State 9, Line 32
ALTER TABLE DROP COLUMN IncreaseApplies failed because
one or more objects access this column.

 

采用一下sql语句可以完全将此字段的所有关系删除并从当前表中删除此字段

 

-- first define variables
declare @default sysname, @sql nvarchar(max)

-- get name of default constraint
select @default = name
from sys.default_constraints
where parent_object_id = object_id('TABLE_NAME')
AND type = 'D'
AND parent_column_id = (
    select column_id
from sys.columns
    where object_id = object_id('TABLE_NAME')
and name = 'COLUMN_NAME'
)

-- create alter table command as string and run it
set @sql = N'alter table TABLE_NAME drop constraint ' + @default
exec sp_executesql @sql

-- now we can finally drop column
ALTER TABLE [TABLE_NAME]
DROP COLUMN COLUMN_NAME