SQL SERVER数据库快速转ORACLE实战

操作步骤如下，供参考。
 数据库转型工作涉及的工作事项分析：表，表数据，索引，外键约束，字段默认值。
存储过程、函数、触发器、视图等由于语法存在差异，只能自行改写处理。
 
(一)在MS SQL SERVER服务器端的准备工作。
1).创建关于表、视图、主键、索引、字段字典、默认值约束的对象视图。以方便下一步使用。
USE  EJ_ZSZQ   ---USE   [数据库名]
GO
--.1不含有TEXT类型、BIGINT类型字段的表视图
CREATE VIEW V_MYTABLE AS
 Select UPPER(NAME) AS NAME from sysobjects A
 where xtype = 'U' AND NAME<>'dtproperties'
  AND NOT EXISTS(SELECT TOP 1 B.NAME from SYSCOLUMNS B,SYSTYPES C WHERE B.XTYPE=C.XTYPE AND B.ID=A.ID
   AND (C.NAME = 'text' OR C.NAME='bigint'))
GO
--2.含有TEXT类型、BIGINT类型字段的表视图
CREATE VIEW V_MYTABLE2 AS
 Select UPPER(NAME) AS NAME from sysobjects A
 where xtype = 'U' AND NAME<>'dtproperties'
  AND EXISTS(SELECT TOP 1 B.NAME from SYSCOLUMNS B,SYSTYPES C WHERE B.XTYPE=C.XTYPE AND B.ID=A.ID
   AND (C.NAME = 'text' OR C.NAME='bigint'))
GO
--3.所有主键视图，构建主键时使用
CREATE VIEW V_MYPK AS
SELECT A.parent_obj AS TABLEID,
       UPPER(E.NAME) AS TABLENAME,
       UPPER(A.NAME) AS INDEXNAME,
       UPPER(D.NAME) AS COLNAME,
       C.KEYNO AS COLNO,
       (SELECT TOP 1 KEYNO
          from sysindexkeys
         WHERE ID = B.ID
           AND INDID = B.INDID
         ORDER BY KEYNO DESC) AS KEYCNT
  from sysobjects   A,
       sysindexes   B,
       sysindexkeys C,
       syscolumns   D,