是去掉默认值属性吗??试试declare @name varchar(20)
select @name=b.name from syscolumns a,sysobjects b where a.id=object_id(N'表名') and b.id=a.cdefault and a.name='字段名' and b.name like 'DF%'
exec('Alter table 表名 drop constraint '+@name)
select @name=b.name from syscolumns a,sysobjects b where a.id=object_id(N'表名') and b.id=a.cdefault and a.name='字段名' and b.name like 'DF%'
exec('Alter table 表名 drop constraint '+@name)
alter column ss varchar(20) default 'test' null
declare @name varchar(20)select @name=b.name from syscolumns a,sysobjects b where a.id=object_id('BGS_JSCZ') and b.id=a.cdefault and a.name='F_FSDW' and b.name like 'DF%'if @name is NULL
begin
alter table BGS_JSCZ add default 'cc' for F_FSDW
end
else
begin
exec('alter table BGS_JSCZ drop constraint '+@name) ALTER TABLE BGS_JSCZ ALTER COLUMN F_FSDW varchar(20) not null
exec('alter table BGS_JSCZ add constraint '+@name+' default ''ddddddddd'' for F_FSDW')end
有时候会出错,错误信息如下
服务器: 消息 3728,级别 16,状态 1,行 1
'DF__BGS_JSCZ__F_FSDW' 不是约束。
服务器: 消息 3727,级别 16,状态 1,行 1
未能除去约束。请参阅前面的错误信息。
begin
alter table BGS_JSCZ add default 'cc' for F_FSDW
end
else
begin
exec('alter table BGS_JSCZ drop constraint '+@name) ALTER TABLE BGS_JSCZ ALTER COLUMN F_FSDW varchar(20) not null
exec('alter table BGS_JSCZ add constraint '+@name+' default ''ddddddddd'' for F_FSDW')end