通用SQL语句修改字段默认值

declare @name varchar(1000)
declare @tablename varchar(1000)
declare @fieldname varchar(1000)
declare @defaultValue varchar(100)

set @tablename='Contract'
set @fieldname='StockNo'
set @defaultValue=''''''--eg:''''='',''test''='test'

select @name=b.name from syscolumns a,sysobjects b
where a.id=object_id(@tablename)
and b.id=a.cdefault
and a.name=@fieldname
and b.name like 'DF%'

if @name is not null
   exec('alter table '+ @tablename+ ' drop constraint '+@name)
   exec('alter table '+ @tablename+ ' ADD constraint DF_'+@fieldname +' DEFAULT '+@defaultValue+' for '+@fieldname)
--print 'alter table '+ @tablename+ ' ADD constraint DF_'+@fieldname +' DEFAULT '+@defaultValue+' for '+@fieldname

转载于:https://www.cnblogs.com/umen/archive/2010/09/18/1830042.html