添加及修改默认值约束的方法步骤

--以表名BPR000T 字段名 LEVEL1 举例说明添加及修改默认值约束的方法步骤

--1.查看表BPR000T的LEVEL1字段是否有默认值约束

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 = 'BPR000T' and b.name = 'LEVEL1'

 

--2.如果有默认值约束，删除对应的默认值约束

declare @tablename varchar(30)

declare @fieldname varchar(50)

declare @sql varchar(300)

 

set @tablename = 'BPR000T'

set @fieldname = 'LEVEL1'

set @sql=''

 

select @sql = @sql + 'alter table ['+ a.name + '] drop constraint [' + d.name + ']'

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

 

exec(@sql)

 

--3.添加默认值约束

alter table BPR000T add default ('1') for LEVEL1 with values