09 Dec 2009

使用SQL语句更改数据表的默认值

Share

以前还真没这么干过,都是手工去掉约束,再添加的,今天一边google一边搞啊搞啊的,最终下面这个脚本可以用了

alter table dbo.SOTransaction
    alter column UnitCostWithoutTax decimal(10, 2not null

go

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

set @tablename = ‘SOTransaction’;
set @fieldname = ‘UnitCostWithoutTax’;

select @name = b.name from syscolumns a inner join sysobjects b
    on b.id=a.cdefault
where
    a.id= object_id(@tablename)
    and a.name[email protected]fieldname
    and b.name like ‘DF%’
–print @name

exec(’ alter table SOTransaction drop constraint ‘ + @name);
go
alter table dbo.SOTransaction
    add constraint DF_SOTransaction_UnitCostWithoutTax default (0) for UnitCostWithoutTax

comments powered by Disqus