批量修改Ms SqlServer 的default(默认值)

1、取得数据库所有表的默认值：

select??

?? t3.name?? as?? 表名,t1.name?? as?? 字段名,t2.text?? as?? 默认值??,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;

?

2、生成删除所有默认值的语句：

select "ALTER? TABLE " +? t3.name?? + " DROP? CONSTRAINT? " +t4.name? +";"
? from?? syscolumns?? t1,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? ;

?

3、生成批量添加所有默认值的语句：select "alter table " +? t3.name?? + "? add default? (1)? for " +t1.name? +";"? from?? syscolumns?? t1,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 ;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!