CREATE PROCEDURE [dbo].[Message_alter]
@tablename varchar(30)
@fieldname varchar(50)
@value varchar(1)
AS
declare @sql varchar(300)
set @sql=''
--(1)查看某表的某个字段是否有默认值约束
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
--(2)如果有默认值约束,删除对应的默认值约束
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)
--(3)添加默认值约束
select @sql='ALTER TABLE '+ @tablename +' ADD DEFAULT ('+@value+') FOR '+@fieldname + ' WITH VALUES'
exec (@sql)
GO