mssql sqlserver “查看/修改/删除”指定表指定字段上的默认值的方法分享


摘要:
下文讲述查看指定字段的默认值(default value)
修改指定字段上的默认值—(删除默认值后再新增默认值)
删除指定字段上的默认值
的方法分享,如下所示:
实验环境:sqlserver 2008 R2


例:

   ---创建一个带默认值的表
   
   create table test
    (keyId int,info varchar(10))

    alter table test 
      ADD  DEFAULT ('a') FOR [info]
     ---为列info添加默认值 
    go
 
 
---2  获取表test 中列的默认值   
select
t3.name as [表对象名称],
t1.name as [字段名称],t2.text as [字段默认值] ,t4.name as [约束名称]
from syscolumns t1,syscomments t2,sysobjects t3 ,sysobjects t4
where t1.cdefault=t2.id and t3.xtype='u' and t3.id=t1.id
and t4.xtype='d' and t4.id=t2.id
and t3.name ='test' ---指定表名
;

go

---为test表中的info列 生成删除默认值的脚本

select 'ALTER  TABLE ' +  t3.name   + ' DROP  CONSTRAINT  [' +t4.name  +'];' 
from   syscolumns   t1,syscomments   t2,sysobjects   t3   ,sysobjects   t4   
  where     t1.cdefault=t2.id   and   t3.xtype='u'  and   t3.id=t1.id   
   and   t4.xtype='d'   and   t4.id=t2.id  
   and t3.name ='test'
   and t1.name ='info'
   ;

---为test表中的info列 生成添加默认值的脚本 

select 'alter table ' +  t3.name   + '  add default  (''a'')  for ' +t1.name  +';' 
  from   syscolumns   t1,syscomments   t2,sysobjects   t3   ,sysobjects   t4   
   where     t1.cdefault=t2.id   and   t3.xtype='u'   and   t3.id=t1.id   
    and   t4.xtype='d'   and   t4.id=t2.id
    and t3.name ='test'
    and t1.name ='info'
   ;

go

truncate table test 
drop     table test 
mssql_sqlserver_字段默认值

mssql_sqlserver_字段默认值