有时候我们在drop column的时候，会遇到一些default constraints而不能drop，如果我们已经知道constraint name，则可以用下面的语句先把constraint remove掉，然后再drop column。
declare
@sql
nvarchar
(
1024
)
set @sql = N ' alter table [system] drop constraint DF_system_LastGraceDate '
exec sp_executesql @sql
set @sql = N ' alter table [system] drop constraint DF_system_LastGraceDate '
exec sp_executesql @sql
如果我们不知道constraint name，我们可以先把他们找出来，然后再remove掉。
代码
--
create alter table command as string and run it
declare @sql nvarchar ( 1024 )
set @sql = N ' alter table [system] drop constraint DF_system_LastGraceDate '
exec sp_executesql @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
declare @sql nvarchar ( 1024 )
set @sql = N ' alter table [system] drop constraint DF_system_LastGraceDate '
exec sp_executesql @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
本文转自loose_went博客园博客，原文链接：http://www.cnblogs.com/michaelxu/archive/2010/04/15/1712510.html，如需转载请自行联系原作者
