![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--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; --取得数据库中已有默认值的脚本 select 'ALTER TABLE [' + t3.name + '] ADD DEFAULT ' + t2.text + ' 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 ORDER BY t3.name, t1.name; --alter table MES_MOAppoint_ProdProcSteps add default (1) for ContainerChangeOut; --2、生成删除所有默认值的语句: 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 ORDER BY t3.name, t1.name; --3、生成批量添加所有默认值的语句: select 'alter table ' + t3.name + ' add default (1) 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 ;