I have two versions of database V5 and V9. V9 being the latest version. I have to find the columns in similar table name that are present in table of V9 and not in V5. example, table name : ashish_table in V9 has 5 columns A,B,C,D and E. same table ashish_table in V5 has 3 columns A,B and C.

So there are two columns D and E present in table ashish_table of V9 and not in ashish_table of V5.

I have made connections between these two databases. I also searched and found below script which compares tables based on "data_type, data_length, data_scale, data_precision";but it matches only the columns with same name. eg. it will match column A,B and C based on data_type, data_length, data_scale, data_precision; if suppose datatype of column A has been changed from char to varchar and other two columns details are same then it will return only column A. It does not give D and E which is not existing in ashish_table of V5.

set verify  off 
set heading off
column data_type format a20

accept table1 prompt 'Table 1: '
accept table2 prompt 'Table 2: '

prompt
prompt The table &table1 and &table2 have columns with the same name but differ:
prompt -------------------------------------------------------------------------

select 
       column_name,data_type, data_length, data_scale, data_precision
from  
       user_tab_columns
where 
       table_name = upper('&table1')
minus
select 
       column_name,data_type, data_length, data_scale, data_precision
from 
       user_tab_columns
where 
       table_name = upper('&table2');

Thanks for reading the big question. Is there any script which compares two tables and gives the missing columns.

1 Answers

0
Gauravsa On

You can use the following to find if there are any extra columns:

select c2.table_name,c2.COLUMN_NAME
from all_tab_cols c2
where table_name='&table1'
and c2.COLUMN_NAME not in (select column_name 
    from all_tab_cols 
    where table_name='&table2');

This is untested but can do this:

select 
       column_name,data_type, data_length, data_scale, data_precision
from  
       user_tab_columns
where 
       table_name = upper('&table1')
minus
select 
       column_name,data_type, data_length, data_scale, data_precision
from 
       user_tab_columns
where 
       table_name = upper('&table2');
union
select c2.COLUMN_NAME, c2.data_type, c2.data_length, c2.data_scale, c2.data_precision
from all_tab_cols c2
where table_name='&table1'
and c2.COLUMN_NAME not in (select column_name 
    from all_tab_cols 
    where table_name='&table2');