菜鸟学sql，Oracle数据库结构比较

------------------------------------------------------------------------------------ --oracle数据库帐套比较， --在本例中，databaseA数据库， --databaseB是数据库。 --在实际的运行过程中，请依据需要修改本sql -------------------------------------------------------------------------------------- select (case when a.表名1 is null and b.序号=1 then ‘库1缺少表：‘||b.表名2 when b.表名2 is null and a.序号=1 then ‘库2缺少表:‘||a.表名1 when a.字段名 is null and exists(select 1 from (select table_name as 表名1 from DBA_TAB_COLUMNS where upper(DBA_TAB_COLUMNS.owner) =upper(‘databaseB‘)) where 表名1=b.表名2) then ‘库1 [‘||b.表名2||‘] 缺少字段：‘||b.字段名 when b.字段名 is null and exists(select 1 from (select table_name as 表名2 from DBA_TAB_COLUMNS where upper(DBA_TAB_COLUMNS.owner) =upper(‘databaseA‘)) where 表名2=a.表名1) then ‘库2 [‘||a.表名1||‘] 缺少字段：‘||a.字段名 when a.主键<>b.主键 then ‘主键不同‘ when a.类型<>b.类型 then ‘类型不同‘ when a.字段长度<>b.字段长度 then ‘字段长度不同‘ when a.精度<>b.精度 then ‘精度不同‘ when a.小数位<>b.小数位 then ‘小数位不同‘ when a.允许空<>b.允许空 then ‘允许空不同‘ else ‘‘ end) result,a.*,b.* from (select a.table_name as 表名1, a.column_name as 字段名,b.constraint_type as 主键, a.data_type as 类型,column_id as 序号, a.data_length as 字段长度, a.data_precision as 精度, a.data_scale as 小数位, a.nullable as 允许空 from all_tab_columns a left join (select d.constraint_type,e.column_name,e.table_name from all_constraints d,all_cons_columns e where d.owner = e.owner and d.table_name = e.table_name and d.constraint_name = e.constraint_name and d.constraint_type = ‘P‘) b on a.COLUMN_NAME = b.column_name and a.TABLE_NAME = b.table_name where a.owner = upper(‘databaseB‘) order by 表名1 asc) a full join (select a.table_name as 表名2, a.column_name as 字段名,b.constraint_type as 主键, a.data_type as 类型,column_id as 序号, a.data_length as 字段长度, a.data_precision as 精度, a.data_scale as 小数位, a.nullable as 允许空 from all_tab_columns a left join (select d.constraint_type,e.column_name,e.table_name from all_constraints d,all_cons_columns e where d.owner = e.owner and d.table_name = e.table_name and d.constraint_name = e.constraint_name and d.constraint_type = ‘P‘) b on a.COLUMN_NAME = b.column_name and a.TABLE_NAME = b.table_name where a.owner = upper(‘databaseA‘) order by 表名2 asc) b on a.表名1=b.表名2 and a.字段名=b.字段名 where a.表名1 is null or a.字段名 is null or b.表名2 is null or b.字段名 is null or a.类型<>b.类型 or a.字段长度<>b.字段长度 or a.精度<>b.精度 or a.小数位<>b.小数位 or a.允许空<>b.允许空 or a.主键<>b.主键 order by nvl(a.表名1,b.表名2),nvl(a.序号,b.序号)

执行结果显示的差异信息比较详细。可以依据这个来修改数据库，比较方便。
现在分析下这句sq的语法。
1.将两个数据库oracle叫schema下的所有表结构select出来，即：

<span style="color: #000000;">所有表结构
</span><span style="color: #0000ff;">select</span> a.table_name <span style="color: #0000ff;">as</span> 表名1, a.column_name <span style="color: #0000ff;">as</span> 字段名,b.constraint_type <span style="color: #0000ff;">as</span> 主键, a.data_type <span style="color: #0000ff;">as</span> 类型,column_id <span style="color: #0000ff;">as</span> 序号, a.data_length <span style="color: #0000ff;">as</span> 字段长度, a.data_precision <span style="color: #0000ff;">as</span> 精度,  a.data_scale <span style="color: #0000ff;">as</span> 小数位, a.nullable <span style="color: #0000ff;">as</span><span style="color: #000000;"> 允许空   
  </span><span style="color: #0000ff;">from</span> all_tab_columns a <span style="color: #808080;">left</span> <span style="color: #808080;">join</span> (<span style="color: #0000ff;">select</span> d.constraint_type,e.column_name,e.table_name <span style="color: #0000ff;">from</span><span style="color: #000000;"> all_constraints d,all_cons_columns e
  </span><span style="color: #0000ff;">where</span>  d.owner  <span style="color: #808080;">=</span>  e.owner  <span style="color: #808080;">and</span> d.table_name  <span style="color: #808080;">=</span>  e.table_name <span style="color: #808080;">and</span><span style="color: #000000;">
   d.constraint_name  </span><span style="color: #808080;">=</span>  e.constraint_name <span style="color: #808080;">and</span> d.constraint_type  <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P</span><span style="color: #ff0000;">‘</span>) b <span style="color: #0000ff;">on</span> a.COLUMN_NAME <span style="color: #808080;">=</span> b.column_name <span style="color: #808080;">and</span> a.TABLE_NAME <span style="color: #808080;">=</span><span style="color: #000000;"> b.table_name
  </span><span style="color: #0000ff;">where</span>  a.owner  <span style="color: #808080;">=</span> <span style="color: #ff00ff;">upper</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">databaseB</span><span style="color: #ff0000;">‘</span>) <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span> 表名1 <span style="color: #0000ff;">asc</span>

通过上述sql就可以将某个数据库下的表结构select出来，通过left join 关联列的主键。
2. 将要比较的两个库的表结构用full join 关联起来。
3. 同在用case来比较具体不同信息。然后在加上where条件。

综述，比较oracle中，2个数据库的表结构基本就用上述sql比较了，效率有些慢，朋友们有好的建议可以谈谈。谢谢。

 

菜鸟学sql，Oracle数据库结构比较

标签：