SchCompare.sql

==== CREATING TABLES ====
create table saner10 as select a.TABLE_NAME, a.COLUMN_NAME, a.DATA_TYPE, a.DATA_TYPE_MOD,
a.DATA_TYPE_OWNER, a.DATA_LENGTH, a.DATA_PRECISION, a.DATA_SCALE, a.NULLABLE, a.COLUMN_ID
from user_tab_columns a, user_tables b where a.table_name = b.table_name;


create table saner9 as select a.TABLE_NAME, a.COLUMN_NAME, a.DATA_TYPE, a.DATA_TYPE_MOD,
a.DATA_TYPE_OWNER, a.DATA_LENGTH, a.DATA_PRECISION, a.DATA_SCALE, a.NULLABLE, a.COLUMN_ID
from user_tab_columns@er90 a, user_tables@er90 b where a.table_name = b.table_name;

==========CODE STARTS HERE=============
DECLARE
t_prev_tab varchar2(30);
DATA_TYPE_10 saner10.data_type%type;
DATA_TYPE_MOD_10 saner10.data_type_mod%type;
DATA_TYPE_OWNER_10 saner10.DATA_TYPE_OWNER%type;
DATA_LENGTH_10 saner10.DATA_LENGTH%type;
DATA_PRECISION_10 saner10.DATA_PRECISION%type;
DATA_SCALE_10 saner10.DATA_SCALE%type;
NULLABLE_10 saner10.NULLABLE%type;
COLUMN_ID_10 saner10.COLUMN_ID%type;
DATA_TYPE_9 saner10.data_type%type;
DATA_TYPE_MOD_9 saner10.data_type_mod%type;
DATA_TYPE_OWNER_9 saner10.DATA_TYPE_OWNER%type;
DATA_LENGTH_9 saner10.DATA_LENGTH%type;
DATA_PRECISION_9 saner10.DATA_PRECISION%type;
DATA_SCALE_9 saner10.DATA_SCALE%type;
NULLABLE_9 saner10.NULLABLE%type;
COLUMN_ID_9 saner10.COLUMN_ID%type;

BEGIN
dbms_output.put_line(rpad('=',80,'='));
dbms_output.put_line('Tables that do not exist in ER9.x Database');
for i in (select distinct a.table_name
from saner10 a
where not exists (select 1
from saner9 b
where substr(a.table_name,3) = b.table_name)) loop
dbms_output.put_line('==> '||i.table_name);
end loop;
dbms_output.put_line(rpad('=',80,'='));
dbms_output.put_line('Tables that do not exist in ER10 Database');
for i in (select distinct a.table_name
from saner9 a
where not exists (select 1
from saner10 b
where a.table_name = substr(b.table_name,3))) loop
dbms_output.put_line('==> '||i.table_name);
end loop;

-- Missing Columns Code
dbms_output.put_line(rpad('=',80,'='));
dbms_output.put_line('The following columns are new in ER10');
for i in (select distinct a.table_name
from saner10 a
where exists (select 1 from saner9 b where substr(a.table_name,3) = b.table_name)
order by a.table_name)
loop
t_prev_tab := i.table_name;
for j in (
select a.table_name, a.column_name
from saner10 a
where not exists (select 1
from saner9 b
where substr(a.table_name,3) = b.table_name
and a.column_name = b.column_name)
and a.table_name = i.table_name
) loop
if t_prev_tab = i.table_name then
dbms_output.put_line('Table: '||j.table_name);
t_prev_tab := null;
end if;
dbms_output.put_line(' ==> '||j.column_name);
end loop;
end loop;
dbms_output.put_line(rpad('=',80,'='));
dbms_output.put_line('The following columns are in ER9.x but NOT IN ER 10');
for i in (select distinct a.table_name
from saner10 a
where exists (select 1 from saner9 b where substr(a.table_name,3) = b.table_name)
order by a.table_name)
loop
for j in (
select a.table_name, a.column_name
from saner9 a
where not exists (select 1
from saner10 b
where substr(b.table_name,3) = a.table_name
and a.column_name = b.column_name)
and a.table_name = i.table_name
) loop
dbms_output.put_line('==> '||j.table_name||'.'||j.column_name);
end loop;
end loop;

--Column exists but DIFFERS code
dbms_output.put_line(rpad('=',80,'='));
dbms_output.put_line('Following columns exists in both differs in ER10');
for i in (select distinct a.table_name, a.column_name
from saner10 a
where exists (select 1 from saner9 b
where substr(a.table_name,3) = b.table_name
and a.column_name = b.column_name)
order by a.table_name, a.column_name)
loop

for j in (select substr(a.table_name,3) table_name, a.COLUMN_NAME, a.DATA_TYPE, a.DATA_TYPE_MOD,
a.DATA_TYPE_OWNER, a.DATA_LENGTH, a.DATA_PRECISION, a.DATA_SCALE, a.NULLABLE, a.COLUMN_ID
from saner10 a
where A.table_name = i.table_name
and a.column_name = i.column_name
minus
select a.table_name, a.COLUMN_NAME, a.DATA_TYPE, a.DATA_TYPE_MOD,
a.DATA_TYPE_OWNER, a.DATA_LENGTH, a.DATA_PRECISION, a.DATA_SCALE, a.NULLABLE, a.COLUMN_ID
from saner9 a
where A.table_name = substr(i.table_name,3)
and a.column_name = i.column_name)
loop
select DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER, DATA_LENGTH,
DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID
into DATA_TYPE_10, DATA_TYPE_MOD_10, DATA_TYPE_OWNER_10, DATA_LENGTH_10,
DATA_PRECISION_10, DATA_SCALE_10, NULLABLE_10, COLUMN_ID_10
from saner10
where table_name = i.table_name
and column_name = i.column_name;

select DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER, DATA_LENGTH,
DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID
into DATA_TYPE_9, DATA_TYPE_MOD_9, DATA_TYPE_OWNER_9, DATA_LENGTH_9,
DATA_PRECISION_9, DATA_SCALE_9, NULLABLE_9, COLUMN_ID_9
from saner9
where table_name = substr(i.table_name,3)
and column_name = i.column_name;
dbms_output.put_line(rpad('=',80,'='));
dbms_output.put_line('Column differences for Table '||i.table_name||'.'||i.column_name);
dbms_output.put_line(lpad('in ER10',27,' ')||rpad(' ',23,' ')||'in ER9.x');

dbms_output.put_line(rpad('Data Type',20,'-') ||rpad(data_type_10,30,' ')||rpad(data_type_9,30,' '));
dbms_output.put_line(rpad('Data Type Mod',20,'-') ||rpad(data_type_mod_10,30,' ')||rpad(data_type_mod_9,30,' '));
dbms_output.put_line(rpad('Data Type Owner',20,'-') ||rpad(data_type_owner_10,30,' ')||rpad(data_type_owner_9,30,' '));
dbms_output.put_line(rpad('Data Length',20,'-') ||rpad(data_length_10,30,' ')||rpad(data_length_9,30,' '));
dbms_output.put_line(rpad('Data Precision',20,'-') ||rpad(data_precision_10,30,' ')||rpad(data_precision_9,30,' '));
dbms_output.put_line(rpad('Data Scale',20,'-') ||rpad(data_scale_10,30,' ')||rpad(data_scale_9,30,' '));
dbms_output.put_line(rpad('Nullable',20,'-') ||rpad(nullable_10,30,' ')||rpad(nullable_9,30,' '));
dbms_output.put_line(rpad('Column ID',20,'-') ||rpad(column_id_10,30,' ')||rpad(column_id_9,30,' '));
end loop;
end loop;
end;
/

Schema Comparison

I know there are many tools out there that gives the schema comparison in probably 3 or 4 clicks. But the problem that I have faced is, when the table names are different, alright not completely different but with one or two standard characters difference viz., prefixing with "t_" or appending with "_x" or something, then the tools (atleast that i know of) are not good any more. Thus made me to go back to my old days to write my own script to achieve this.

Here is my scenario:
The environment that i am working on is going thru some major migration and integration process and we end up in renaming all the tables to prefix with "t_" and then create a synonym so that the existing applications dont have to change completely. So, that said, we have an existing database with regular names and the new one with "t_".
Guess what, TOAD can not compare these schemas anymore since all table names are different.

Basically, this is what i have done to achieve this:
created a tableA using user_tab_columns data dictionary data from existing and
created a tableB using user_tab_columns data dictionary data from the new schema over the database link.
One can question, why create these tables instead of directly accessing them thru database links? Answer is, there are few columns in this data dictionary that can be accessed and compared via db links. So, created these tables.

Once the tables are there, you can do what ever you want and however you want to compare correct. So, here is my script:

Not sure if I can attach a file here but let me see (as this is first blog :( ).
Well, cant upload a file so published another post and linking here: