【SQL Server】修改字段默认值
背景:
Oracle数据库迁移到SQL Server,在Oracle数据库中有个字段叫cdate default sysdate,通过Navicat工具,将Oracle库中的表迁移到SQL Server中,在迁移的过程中,由于default值利用了Oracle的sysdate函数,迁移工具无法进行转换,SQL Server中也没有sysdate函数,所以迁移的过程中会报错,由于表的数量较多,所以在Oracle数据库中通过命令批量删除了cdate字段的默认值。命令如下:
1 2 3 |
select 'alter table '||table_name||' modify '||column_name||' default null;' from user_tab_columns where data_type='DATE' and data_default is not null |
上面的SQL,看似没什么问题,不过,如果要是查询user_tab_columns 视图的话,你会发现data_default 字段中有个‘null’的字符串。虽然在Oracle里没有什么问题(oracle默认的就是null),但是在迁移的时候,SQL Server确当回事儿了,就把null当成那个字段的默认值了。
迁移完成后,在SQL Server中要添加default,如果想添加的话,需要如下两步:
1:删除原来的default值
2:添加新的default
删除default相对较费劲,在SQL Server中将default当作约束使用,如果删除default的话,需要知道约束的名称。我查了好半天才找到合适的SQL语句,如下:
1 2 3 4 |
## default值得约束类型为D,通过下面SQL查到约束的名称 select 'alter table '+ t.name +' drop constraint '+ d.name +';' from sysobjects d,(select object_id(a.name) as oid,a.name from sys.objects a,sys.columns b where a.object_id=b.object_id and b.name='cdate') t where d.parent_obj=t.oid and d.xtype='D' |
上面SQL会生成删除约束的SQL,然后批量执行一下。
生成修改default值的SQL
1 2 3 |
select a.name,'alter table '+a.name+' ADD CONSTRAINT DF_'+a.name+'_CDATE DEFAULT getdate() FOR CDATE;' from sys.objects a,sys.columns b where a.object_id=b.object_id and b.name='ctime' |
批量执行。
由于之前从未使用过SQL Server,也许可能会有更方便的办法。