create table ff
(
aa varchar(100) null default '1'
)
GO
Declare @name Varchar(50)
Select @name=b.name from syscolumns a,sysobjects b where a.id=object_id('ff') and b.id=a.cdefault and a.name='aa' and b.name like 'DF%'
If @name Is Not Null
Begin
Exec('Alter table ff drop Constraint '+@name)
Exec('Alter Table ff Add Constraint '+@name+' Default ''2'' for aa')
End
GO
insert ff values(default)
select * from ff
go
Drop table ff
GO
--结果
/*
aa
2
*/
--你的建表语句
create table ff
(
aa varchar(100) null default '1'
)
go
--修改默认值
declare @s nvarchar(4000)
select @s='alter table ['+b.name+'] drop constraint ['+d.name+']'
-- ,表名=b.name,字段名=a.name,默认值=c.text,约束名=d.name
from syscolumns a
join sysobjects b on a.id=b.id
join syscomments c on a.cdefault=c.id
join sysobjects d on c.id=d.id
where b.name='ff' and a.name='aa'
exec(@s)
--添加字段的默认值约束:
alter table ff add constraint DF_ff_aa_112312
default ('2') for aa
go
--插入一条记录测试
insert ff values(default)
select * from ff
go