用SQL存储过程修改默认值

CREATE PROCEDURE [dbo].[Message_alter]

 @tablename varchar(30)
 @fieldname varchar(50)
 @value varchar(1)
AS
declare @sql varchar(300)

set @sql=''
--（１）查看某表的某个字段是否有默认值约束
select a.name as 用户表,b.name as 字段名,d.name as 字段默认值约束
from sysobjects a
inner join syscolumns b on (a.id=b.id)
inner join syscomments c on ( b.cdefault=c.id )
inner join sysobjects d on (c.id=d.id)
where a.name=@tablename and b.name=@fieldname

--（２）如果有默认值约束，删除对应的默认值约束

select @sql=@sql+'
alter table ['+a.name+'] drop constraint ['+d.name+']'  
from sysobjects a  
join syscolumns b on a.id=b.id  
join syscomments c on b.cdefault=c.id  
join sysobjects d on c.id=d.id  
where a.name=@tablename and b.name=@fieldname

exec(@sql)

--（３）添加默认值约束
select @sql='ALTER TABLE '+ @tablename   +' ADD DEFAULT ('+@value+') FOR '+@fieldname + '  WITH VALUES'
exec (@sql)
GO