Compare table structure in oracle

I found an interesting script that I used sometime back to compare the structure of 2 tables. Thought I should preserve and blog it for the benefit of all.

--- SCRIPT START ---

Rem script name - compare_tables.sql
Rem Table structure comparison script
Rem Can compare tables across schema's provided the user has privilege
Rem Provide table name in the format SCHEMA_NAME.TABLE_NAME

set verify off
set heading off
set feedback off
set line 100

column owner format a20
column column_name format a20
column data_type format a20


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

prompt
prompt columns having same name but difference in datatype or length:
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
all_tab_columns a, all_tab_columns b
where
a.table_name = upper(substr('&table1',instr('&table1','.')+1)) and
a.owner = upper(substr('&table1',1,instr('&table1','.')-1)) and
b.table_name = upper(substr('&table2',instr('&table2','.')+1)) and
b.owner = upper(substr('&table2',1,instr('&table2','.')-1)) 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 present in &table1 but not in &table2
prompt ----------------------------------------------

select
column_name
from
all_tab_columns
where
table_name = upper(substr('&table1',instr('&table1','.')+1))
and owner = upper(substr('&table1',1,instr('&table1','.')-1))
minus
select
column_name --, data_type, data_length, data_scale, data_precision
from
all_tab_columns
where
table_name = upper(substr('&table2',instr('&table2','.')+1))
and owner = upper(substr('&table2',1,instr('&table2','.')-1));

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

select
column_name --, data_type, data_length, data_scale, data_precision
from
all_tab_columns
where
table_name = upper(substr('&table2',instr('&table2','.')+1))
and owner = upper(substr('&table2',1,instr('&table2','.')-1))
minus
select
column_name
from
all_tab_columns
where
table_name = upper(substr('&table1',instr('&table1','.')+1))
and owner = upper(substr('&table1',1,instr('&table1','.')-1));

--- SCRIPT END --

Example using the script

CREATE TABLE DEPT_1
(
DEPTNO NUMBER(2),
DNAME VARCHAR2(20),
LOC VARCHAR2(20)
);

create table dept_2 as select deptno,dname from dept;

alter table dept_2 modify dname VARCHAR2(40);

@compare_tables.sql

Table 1: SIPRAS.DEPT_1
Table 2: SIPRAS.DEPT_2

columns having same name but difference in datatype or length:
------------------------------------------------------------------------

SIPRAS DNAME VARCHAR2 20
SIPRAS DNAME VARCHAR2 40

columns present in SIPRAS.DEPT_1 but not in SIPRAS.DEPT_2
-------------------------------------------------------------------------

LOC
columns present in SIPRAS.DEPT_2 but not in SIPRAS.DEPT_1
--------------------------------------------------------------------------