Create two tables
Two tables are created to test the SQL statement below that find structural differences in them:
drop table y;
drop table x;
create table x (a number, b number(2), c number(2,3), d varchar2(4) , f date null, g varchar2(20), h number(6,2));
create table y (a number, c number(2,3), d date , e varchar2(5), f date not null, g varchar2(20), h number(7,2));
Select the difference between their structures
The following statement finds column that are either missing in one of the two tables being compared or have different
data types or characteristics.
Because the statement uses a full outer join, the compared columns appear on one record in the result set:
with a as (select * from dba_tab_columns where owner = user and table_name = 'X'),
b as (select * from dba_tab_columns where owner = user and table_name = 'Y')
select
coalesce(a.column_name, b.column_name) col_name,
--
a.column_id column_id_a , b.column_id column_id_b,
--
-- Columns with column information (data types, sizes etc.):
--
a.data_type data_type_a , b.data_type data_type_b ,
a.data_length data_length_a , b.data_length data_length_b ,
a.data_scale data_scale_a , b.data_scale data_scale_b ,
a.data_precision data_precision_a, b.data_precision data_precision_b,
a.nullable nullable_a , b.nullable nullable_b ,
--
-- Columns with information not related to table structure:
--
a.last_analyzed last_analyzed_a , b.last_analyzed last_analyzed_b ,
a.sample_size sample_size_a , b.sample_size sample_size_b ,
a.num_distinct num_distinct_a , b.num_distinct num_distinct_b ,
a.low_value low_value_a , b.low_value low_value_b
from
a full outer join
b on a.column_name = b.column_name
where
not (nvl(a.data_type , 'x') = nvl(b.data_type , 'x') and
nvl(a.data_length , -1 ) = nvl(b.data_length , -1 ) and
nvl(a.data_scale , -1 ) = nvl(b.data_scale , -1 ) and
nvl(a.data_precision , -1 ) = nvl(b.data_precision, -1 ) and
nvl(a.nullable , 'x') = nvl(b.nullable , 'x')
)
order by
coalesce (a.column_id, b.column_id);