If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Here's a naff script I wrote to compare SCHEMA2 to a BASELINE_SCHEMA. Replace these with your schema names. Just does a simple table comparison, nothing fancy like indexes etc though it would be easy to add.

Alan

select 1 idx, 'Table missing '||table_name
from
( select table_name from dba_tables where owner='BASE_SCHEMA'
minus
select table_name from dba_tables where owner='SCHEMA2'
)
union
select 2, 'Column missing ' || table_name||'.'||column_name
from
(
select table_name, column_name from dba_tab_columns
where owner='BASE_SCHEMA' and table_name in (select table_name from dba_tables where owner='BASE_SCHEMA')
minus
select table_name, column_name from dba_tab_columns where owner='SCHEMA2'
) where table_name not in
( select table_name from dba_tables where owner='BASE_SCHEMA' minus select table_name from dba_tables where owner='SCHEMA2')
union
select 3, 'Column to be removed ' || table_name||'.'||column_name
from
(
select table_name, column_name from dba_tab_columns
where owner='SCHEMA2' and table_name in (select table_name from dba_tables where owner='BASE_SCHEMA')
minus
select table_name, column_name from dba_tab_columns where owner='BASE_SCHEMA'
) where table_name not in
( select table_name from dba_tables where owner='BASE_SCHEMA' minus select table_name from dba_tables where owner='SCHEMA2' )
union
select 4, 'Column of the wrong datatype ' || table_name||'.'||column_name ||' should be of type ' ||data_type
from
(
select table_name, column_name, data_type from dba_tab_columns where owner='BASE_SCHEMA'
and table_name in (select table_name from dba_tables where owner='BASE_SCHEMA')
minus
select table_name, column_name, data_type from dba_tab_columns where owner='SCHEMA2'
)
where table_name not in
( select table_name from dba_tables where owner='BASE_SCHEMA' minus select table_name from dba_tables where owner='SCHEMA2' )
order by 1