Here I'm posting a simple sql query which gives a simple difference of two database table.
It checks the column name,data type,length and precision.
The script is meant to find the differences of two tables in the same DB. You can extend the script to find differences in tables from different databases simply by creating a db link from one db to the other db.
--- Combined query
-- common columns
(select '1_SAME' as COL_TYPE,
e1.column_name T1_column_name, e1.data_type T1_data_type,e1.data_length t1_data_length,
e1.data_precision t1_data_precision,
e2.column_name T2_column_name, e2.data_type T2_data_type,e2.data_length t2_data_length,
e2.data_precision t2_data_precision
from all_tab_columns e1 , all_tab_columns e2
where e1.table_name='EMP'
and e2.table_name='EMP2'
and e1.column_name = e2.column_name
and e1.data_type = e2.data_type
and e1.data_length = e2.data_length
and e1.data_precision = e2.data_precision)
UNION
-- indifferent columns
(select '2_DIFF' as COL_TYPE,
e1.column_name T1_column_name, e1.data_type T1_data_type,e1.data_length t1_data_length,
e1.data_precision t1_data_precision,
e2.column_name T2_column_name, e2.data_type T2_data_type,e2.data_length t2_data_length,
e2.data_precision t2_data_precision
from all_tab_columns e1 , all_tab_columns e2
where e1.table_name='EMP'
and e2.table_name='EMP2'
and (e1.column_name = e2.column_name
and (e1.data_type <> e2.data_type
OR e1.data_length <> e2.data_length
OR e1.data_precision <> e2.data_precision)))
UNION
-- TAB 1 COLUMNS
(select '3_TAB1' as COL_TYPE,
e1.column_name T1_column_name, e1.data_type T1_data_type,e1.data_length t1_data_length,
e1.data_precision t1_data_precision,
null T2_column_name, null T2_data_type,null t2_data_length,
null t2_data_precision
from all_tab_columns e1
where e1.table_name='EMP'
and e1.column_name NOT IN (SELECT e2.column_name FROM all_tab_columns e2 WHERE e2.table_name='EMP2'))
UNION
-- TAB 2 columns
(select '4_TAB2' as COL_TYPE,
null T1_column_name, null T1_data_type,null t1_data_length,
null t1_data_precision,
e2.column_name T2_column_name, e2.data_type T2_data_type,e2.data_length t2_data_length,
e2.data_precision t2_data_precision
from all_tab_columns e2
where e2.table_name='EMP2'
and e2.column_name NOT IN (SELECT e1.column_name FROM all_tab_columns e1 WHERE e1.table_name='EMP'));
Individual queries
select * from emp;
select * from emp2;
select * from all_tab_columns where table_name in ('EMP','EMP2');
select emp.ename , emp2.ename
from emp , emp2
where emp = emp2
-- common columns
select 'SAME' as COL_TYPE,
e1.column_name T1_column_name, e1.data_type T1_data_type,e1.data_length t1_data_length,
e1.data_precision t1_data_precision,
e2.column_name T2_column_name, e2.data_type T2_data_type,e2.data_length t2_data_length,
e2.data_precision t2_data_precision
from all_tab_columns e1 , all_tab_columns e2
where e1.table_name='EMP'
and e2.table_name='EMP2'
and e1.column_name = e2.column_name
and e1.data_type = e2.data_type
and e1.data_length = e2.data_length
and e1.data_precision = e2.data_precision
-- indifferent columns
select 'DIFF' as COL_TYPE,
e1.column_name T1_column_name, e1.data_type T1_data_type,e1.data_length t1_data_length,
e1.data_precision t1_data_precision,
e2.column_name T2_column_name, e2.data_type T2_data_type,e2.data_length t2_data_length,
e2.data_precision t2_data_precision
from all_tab_columns e1 , all_tab_columns e2
where e1.table_name='EMP'
and e2.table_name='EMP2'
and (e1.column_name = e2.column_name
and (e1.data_type <> e2.data_type
OR e1.data_length <> e2.data_length
OR e1.data_precision <> e2.data_precision))
-- TAB 1 COLUMNS
select 'TAB1' as COL_TYPE,
e1.column_name T1_column_name, e1.data_type T1_data_type,e1.data_length t1_data_length,
e1.data_precision t1_data_precision,
null T2_column_name, null T2_data_type,null t2_data_length,
null t2_data_precision
from all_tab_columns e1
where e1.table_name='EMP'
and e1.column_name NOT IN (SELECT e2.column_name FROM all_tab_columns e2 WHERE e2.table_name='EMP2');
-- TAB 2 columns
select 'TAB2' as COL_TYPE,
null T1_column_name, null T1_data_type,null t1_data_length,
null t1_data_precision,
e2.column_name T2_column_name, e2.data_type T2_data_type,e2.data_length t2_data_length,
e2.data_precision t2_data_precision,
from all_tab_columns e2
where e2.table_name='EMP2'
and e2.column_name NOT IN (SELECT e1.column_name FROM all_tab_columns e1 WHERE e1.table_name='EMP');
-- Start of DDL Script for Table FUSION.EMP
-- Generated 24-Nov-2008 18:25:32 from FUSION@XE
CREATE TABLE emp2
(empno NUMBER(4,0) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(12),
mgr2 NUMBER(6,0),
hiredate DATE,
sal NUMBER(7,2),
comm2 NUMBER(7,2),
deptno NUMBER(2,0))
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
MONITORING
/
-- End of DDL Script for Table FUSION.EMP
