基于数据库字典或目录视图对数据库对象结构进行比较

基础知识
1、Oracle Dictionary Views
2、SQLServer目录视图

关于Oracle与SQL Server的不同或相同就不整理了，试着用两者完成相同的功能的同时自然就知道两者那些是一致的哪些地方是各自的。当然，要真是非常的了解Oracle与SQL深层次的不同，那么再回过头来看类似查询这样那样数据的工作就显得简单多了。

依据需求分析得出Oracle这块需要对表、视图、触发器、函数\过程、类型进行查询处理，包括知道

1. 哪些对象在A用户下有而在B用户下却没了？
2. A用户下C表有一些字段而在B用户下C表却对不起来，也就是哪些丢了那些是多余的？
3. A用户下C表中哪些字段类型与B用户下C表中字段类型对不上？
4. 用户下的过程\函数哪些是内容不一致的？

Oracle这块Oracle实现

Oracle实现时有个权限相关的问题，就是SYSTEM用户下的存储过程内部查个别ALL_**视图时存储过程下得不到数据的问题，这时需要用sys运行那个存储过程就可以了，大多数ALL_**视图在存储过程下访问也是正常的，但就有个别的不可以。。。

1. 统计两用户下的表对象缺失情况
   
   使用连接
   

   SELECT
    (CASE WHEN A.A_TABLE_NAME IS NULL THEN B.B_TABLE_NAME ELSE A.A_TABLE_NAME END) AS TABLE_NAME,
    (CASE WHEN A.A_TABLE_NAME IS NULL THEN '1' WHEN B.B_TABLE_NAME IS NULL THEN '2' ELSE '-1' END) AS TYPE
   FROM (
        SELECT OBJECT_NAME AS A_TABLE_NAME ,OBJECT_ID AS A_OBJECT_ID FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OWNER='USER_A'
   ) A FULL JOIN (
        SELECT OBJECT_NAME AS B_TABLE_NAME ,OBJECT_ID AS B_OBJECT_ID FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OWNER='USER_B'
   ) B ON A.A_TABLE_NAME = B.B_TABLE_NAME
   WHERE A.A_TABLE_NAME IS NULL OR B.B_TABLE_NAME IS NULL

使用子查询

SELECT '1' TYPE, A.OBJECT_NAME AS TABLE_NAME FROM ALL_OBJECTS A 
       WHERE OWNER='USER_A' AND OBJECT_TYPE='TABLE' AND NOT EXISTS(
             SELECT 1 FROM ALL_OBJECTS B WHERE B.OBJECT_NAME=A.OBJECT_NAME AND B.OWNER='USER_B')
UNION ALL
SELECT '2' TYPE, A.OBJECT_NAME AS TABLE_NAME FROM ALL_OBJECTS A 
       WHERE OWNER='USER_B' AND OBJECT_TYPE='TABLE' AND NOT EXISTS(
             SELECT 1 FROM ALL_OBJECTS B WHERE B.OBJECT_NAME=A.OBJECT_NAME AND B.OWNER='USER_A')

2. 统计两用户下的表的字段结构情况
   这块用存储过程做也蛮方便的，但是后期还得时刻想着存储过程的移植等问题。使用存储过程就没有特殊处理long类型的问题。
   

   SELECT * FROM (
   SELECT A.TABLE_NAME,A.COLUMN_NAME,
    (CASE WHEN A.DATA_TYPE <> B.DATA_TYPE THEN A.DATA_TYPE || '!=' || B.DATA_TYPE ELSE NULL END) DATA_TYPE,
    (CASE WHEN A.DATA_LENGTH <> B.DATA_LENGTH THEN A.DATA_LENGTH || '!=' || B.DATA_LENGTH ELSE NULL END) DATA_LENGTH,
    (CASE WHEN A.DATA_PRECISION <> B.DATA_PRECISION THEN A.DATA_PRECISION || '!=' || B.DATA_PRECISION ELSE NULL END) DATA_PRECISION,
    (CASE WHEN A.DATA_SCALE <> B.DATA_SCALE THEN A.DATA_SCALE || '!=' || B.DATA_SCALE ELSE NULL END) DATA_SCALE,
    (CASE WHEN A.NULLABLE <> B.NULLABLE THEN A.NULLABLE || '!=' || B.NULLABLE ELSE NULL END) NULLABLE,
    (CASE WHEN A.DEFAULT_LENGTH <> B.DEFAULT_LENGTH THEN A.DEFAULT_LENGTH || '!=' || B.DEFAULT_LENGTH ELSE NULL END) DEFAULT_LENGTH
    /*
    (CASE WHEN A.DATA_DEFAULT <> B.DATA_DEFAULT THEN A.DATA_DEFAULT || '!=' || B.DATA_DEFAULT ELSE NULL END) DATA_DEFAULT
    */
   FROM (
   SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,DEFAULT_LENGTH,DATA_DEFAULT
          FROM ALL_TAB_COLS WHERE OWNER='USER_A'
   ) A FULL JOIN (
   SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,DEFAULT_LENGTH,DATA_DEFAULT
          FROM ALL_TAB_COLS WHERE OWNER='USER_B'
   ) B ON A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME
   WHERE A.TABLE_NAME IS NOT NULL AND B.TABLE_NAME IS NOT NULL
   ) C WHERE C.DATA_TYPE IS NOT NULL OR C.DATA_LENGTH IS NOT NULL OR C.DATA_PRECISION IS NOT NULL OR C.DATA_SCALE IS NOT NULL OR C.NULLABLE IS NOT NULL OR C.DEFAULT_LENGTH IS NOT NULL
   

   这条SQL是有局限的，原因在于LONG类型的列内容比较问题，这块的处理也有很多方案基本思路就是把LONG类型的列转成VARCHAR2或CLOB类型的数据存放到个临时表里然后通过连接或子查询的方式都可以

   1、使用临时表存储LONG类型的转换值

   CREATE TABLE TMP_CV_LONG(TMP_CHAR VARCHAR(2000), TMP_CLOB CLOB)

   INSERT INTO TMP_CV_LONG SELECT '', TO_LOB(DATA_DEFAULT) FROM ALL_TAB_COLS WHERE OWNER='ABC' AND DEFAULT_LENGTH<>0

   UPDATE TMP_CV_LONG SET TMP_CHAR = TO_CHAR(TMP_CLOB)

   2、在JDBC那块做处理，当默认值长度不等的情况下取LONG类型的数据然后处理（这样做就特别累了）

3. 其它情况
   其它情况包括函数\过程内容比较，在Oracle中可以通过ALL_SOURCE视图查出来，具体的问题是怎么比的问题，那简单的A.CTX = B.CTX是不能满足要求的。这块可以看人家SVN或Beyond Compare那样的结果。

SQL Server这块

SQL Server这块数据字典信息相关资料直接通过SQL Server联机丛书的内容就够用了，因为Oracle与SQL Server在整个结构上是不一致的（具体要去baidu文库找Oracle与SQL Server深度对比，文章写的非常棒，边实践边看完它基本上对Oracle与SQL Server就大致有个数了），所以SQL Server下需要做另一套查询SQL。

1. 统计两用户下的表对象缺失情况
   

   SELECT
    (CASE WHEN A_NAME IS NULL THEN '1' WHEN B_NAME IS NULL THEN '2' ELSE '-1' END) TYPE,
    (CASE WHEN A_NAME IS NULL THEN B_NAME ELSE A_NAME END) TABLE_NAME
   FROM (
   SELECT UPPER(NAME) A_NAME,OBJECT_ID A_OID FROM USER_A.SYS.TABLES
   ) A FULL JOIN (
   SELECT UPPER(NAME) B_NAME, OBJECT_ID B_OID FROM USER_B.SYS.TABLES
   ) B ON A.A_NAME = B.B_NAME AND A.A_OID = B.B_OID
   WHERE A.A_NAME IS NULL OR B.B_NAME IS NULL
   ORDER BY TYPE

2. 统计两用户下的表的字段结构情况
   

   SELECT * FROM (
   SELECT A.TABLE_NAME,A.COLUMN_NAME,
    (CASE WHEN A.A_DATATYPE <> A.B_DATATYPE THEN (UPPER(A.A_DATATYPE) + '!='+ UPPER(A.B_DATATYPE)) ELSE NULL END) FIELD_TYPE,
    (CASE WHEN A.A_ORD_POSI <> A.B_ORD_POSI THEN (UPPER(A.A_ORD_POSI) + '!='+ UPPER(A.B_ORD_POSI)) ELSE NULL END) FIELD_ORID,
    (CASE WHEN A.A_COL_DEF <> A.B_COL_DEF THEN (UPPER(A.A_COL_DEF) + '!=' + UPPER(A.B_COL_DEF)) ELSE NULL END) FIELD_DEFVALUE,
    (CASE WHEN A.A_ISNULL <> A.B_ISNULL THEN (UPPER(A.A_ISNULL) + '!=' + UPPER(A.B_ISNULL)) ELSE NULL END ) FIELD_ISNULL,
    (CASE WHEN A.A_CMAXLEN <> A.B_CMAXLEN THEN (UPPER(A.A_CMAXLEN) + '!=' + UPPER(A.B_CMAXLEN)) ELSE NULL END) FIELD_LEN,
    (CASE WHEN A.A_NUM_PREC <> A.B_NUM_PREC THEN (UPPER(A.A_NUM_PREC) + '!=' + UPPER(A.B_NUM_PREC)) ELSE NULL END) FIELD_PRE,
    (CASE WHEN A.A_NUMSCAL <> A.B_NUMSCAL THEN (UPPER(A.A_NUMSCAL) + '!=' + UPPER(A.B_NUMSCAL)) ELSE NULL END) FIELD_SCA
   FROM (
   SELECT  UPPER(A.TABLE_NAME) AS TABLE_NAME,
           UPPER(A.COLUMN_NAME) AS COLUMN_NAME,
           A.ORDINAL_POSITION AS A_ORD_POSI,
           A.COLUMN_DEFAULT AS A_COL_DEF,
           UPPER(A.DATA_TYPE) A_DATATYPE,
           A.IS_NULLABLE AS A_ISNULL,
           A.CHARACTER_MAXIMUM_LENGTH AS A_CMAXLEN,
           A.NUMERIC_PRECISION AS A_NUM_PREC,
           A.NUMERIC_SCALE AS A_NUMSCAL,
           B.ORDINAL_POSITION AS B_ORD_POSI,
           B.COLUMN_DEFAULT AS B_COL_DEF,
   UPPER(B.DATA_TYPE) B_DATATYPE,
           B.IS_NULLABLE AS B_ISNULL,
           B.CHARACTER_MAXIMUM_LENGTH AS B_CMAXLEN,
           B.NUMERIC_PRECISION AS B_NUM_PREC,
           B.NUMERIC_SCALE AS B_NUMSCAL
   FROM    USER_A.INFORMATION_SCHEMA.COLUMNS A,
           USER_B.INFORMATION_SCHEMA.COLUMNS B
   WHERE   A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME
           ) A
    ) B
   WHERE B.FIELD_TYPE IS NOT NULL OR B.FIELD_DEFVALUE IS NOT NULL
    OR B.FIELD_ORID IS NOT NULL OR B.FIELD_ISNULL IS NOT NULL 
    OR B.FIELD_LEN IS NOT NULL OR B.FIELD_PRE IS NOT NULL 
    OR B.FIELD_SCA IS NOT NULL
   

   
   

3. 其它情况