René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

A script that shows differences in table structures [Oracle]

This script asks for two table names. Enter them, then it will tell you how the tables differ in their table structure (not in their content!).
comp_table_structure.sql
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 -------------------------------------------------------------------------

column data_precision fold_after

select
        a.column_name, a.data_type, a.data_length, a.data_scale,a.data_precision,
        b.column_name, b.data_type, b.data_length, b.data_scale,b.data_precision
from 
        user_tab_columns a, user_tab_columns b
where
        a.table_name  = upper('&table1')  and
        b.table_name  = upper('&table2')  and
        a.column_name = b.column_name     and
        (
          a.data_type      <> b.data_type   or
          a.data_length    <> b.data_length or
          a.data_scale     <> b.data_scale  or
          a.data_precision <> b.data_precision
        );

prompt columns in &table1 but not in &table2
prompt -------------------------------------

select 
       column_name
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');

prompt columns in &table2 but not in &table1
prompt -------------------------------------

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

A little demonstration

create table table_one (
  col_1    varchar2(10),
  col_2    number(5,2),
  col_3    number,
  col_4    date
);
create table table_two as select * from table_one;
alter table table_one add col_added integer;
alter table table_one drop column col_3;
alter table table_two modify col_2 number(9,8);
SQL> @comp_table_structure
Table 1: table_one
Table 2: table_two
The table table_one and table_two have columns with the same name but differ:
------------------------------------------------------------------------

COL_2                          NUMBER                        22          2              5
COL_2                          NUMBER                        22          8              9


columns in table_one but not in table_two
------------------------------------

COL_ADDED

columns in table_two but not in table_one
------------------------------------

COL_3

Links

This script is also used to compare v$sql and v$sqlarea.