通用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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16436858/viewspace-674401/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16436858/viewspace-674401/