| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Script to Compare table structures
Following is a script that will compare differences within a schema. If this is too much, just edit out the table comparison scripts.
HTH Mark
col table_name format a30 col column_name format a30 col medph2_format format a30
--
-- MATCHING THE TABLES -----------------------------------------
--
ttitle 'THE FOLLOWING TABLES EXIST IN USERA AND DOESN'T EXIST IN USERB'
select table_name from all_tables a
where owner = 'USERA'
and not exists (select 1 from all_tables b
where owner = 'USERB'
and b.table_name = a.table_name)
order by table_name
/
ttitle 'THE FOLLOWING TABLES EXIST IN USERB AND DOESN'T EXIST IN USERA'
select table_name from all_tables a
where owner = 'USERB'
and not exists (select 1 from all_tables b
where owner = 'USERA'
and b.table_name = a.table_name)
order by table_name
/
--
-- MATCHING THE COLUMNS -----------------------------------------
--
ttitle 'THE FOLLOWING COLUMNS EXIST IN USERA AND DOESN'T EXIST IN USERB'
select table_name, column_name from all_tab_columns a
where owner = 'USERA'
and not exists (select 1 from all_tab_columns b
where owner = 'USERB'
and b.table_name = a.table_name
and b.column_name = a.column_name)
order by table_name, column_id
/
ttitle 'THE FOLLOWING COLUMNS EXIST IN USERB AND DOESN'T EXIST IN USERA'
select table_name, column_name from all_tab_columns a
where owner = 'USERB
and not exists (select 1 from all_tab_columns b
where owner = 'USERA'
and b.table_name = a.table_name
and b.column_name = a.column_name)
order by table_name, column_id
/
ttitle 'THE FOLLOWING COLUMNS HAVE DIFFERENT CHARACTERISTICS IN EVERY
SCHEMA'
select a.table_name, a.column_name,
decode(a.data_type,'DATE','DATE',
'NUMBER',decode(a.data_scale,0,'NUMBER('||a.data_precision||')',
'NUMBER('||a.data_precision||','||a.data_scale||')'
),
a.data_type||'('||a.data_length||')'
)||' '||decode(a.nullable,'N','not null',' ') USERA_format,
decode(b.data_type,'DATE','DATE',
'NUMBER',decode(b.data_scale,0,'NUMBER('||b.data_precision||')',
'NUMBER('||b.data_precision||','||b.data_scale||')'
),
b.data_type||'('||b.data_length||')'
)||' '||decode(b.nullable,'N','not null',' ') USERB_format
from all_tab_columns a,
all_tab_columns b
where a.owner = 'USERA'
and b.owner = 'USERB'
and a.table_name = b.table_name
and a.column_name = b.column_name
and (a.data_type <> b.data_type
or a.data_length <> b.data_length
or a.data_precision <> b.data_precision
or a.data_scale <> b.data_scale
or a.nullable <> b.nullable)
order by 1, 2
/
--
-- MATCHING THE VIEWS -----------------------------------------
--
ttitle 'THE FOLLOWING VIEWS EXIST IN USERA AND DOESN'T EXIST IN USERB'
select view_name from all_views a
where owner = 'USERA'
and not exists (select 1 from all_views b
where owner = 'USERB'
and b.view_name = a.view_name)
order by view_name
/
ttitle 'THE FOLLOWING VIEWS EXIST IN USERB AND DOESN'T EXIST IN USERA'
select view_name from all_views a
where owner = 'USERB
and not exists (select 1 from all_views b
where owner = 'USERB'
and b.view_name = a.view_name)
order by view_name
/
--
-- MATCHING THE CONSTRAINTS -----------------------------------------
--
ttitle 'THE FOLLOWING CONSTRAINTS EXIST IN USERA AND DOESN'T EXIST IN USERB'
select table_name, constraint_name, constraint_type
from all_constraints a
where owner = 'USERA'
and constraint_type <> 'C'
and not exists (select 1 from all_constraints b
where owner = 'USERB'
and b.table_name = a.table_name
and b.constraint_name = a.constraint_name
and b.constraint_type = a.constraint_type)
order by table_name, constraint_name
/
ttitle 'THE FOLLOWING CONSTRAINTS EXIST IN USERB AND DOESN'T EXIST IN USERA'
select table_name, constraint_name, constraint_type
from all_constraints a
where owner = 'USERB'
and constraint_type <> 'C'
and not exists (select 1 from all_constraints b
where owner = 'USERA'
and b.table_name = a.table_name
and b.constraint_name = a.constraint_name
and b.constraint_type = a.constraint_type)
order by table_name, constraint_name
/
--
-- MATCHING THE INDEXES -----------------------------------------
--
ttitle 'THE FOLLOWING INDEXES EXIST IN USERA AND DOESN'T EXIST IN USERB'
select table_name, index_name
from all_indexes a
where owner = 'USERA'
and not exists (select 1 from all_indexes b
where owner = 'USERB'
and b.table_name = a.table_name
and b.index_name = a.index_name)
order by table_name, index_name
/
ttitle 'THE FOLLOWING INDEXES EXIST IN USERB AND DOESN'T EXIST IN USERA'
select table_name, index_name
from all_indexes a
where owner = 'USERB'
and not exists (select 1 from all_indexes b
where owner = 'USERA'
and b.table_name = a.table_name
and b.index_name = a.index_name)
order by table_name, index_name
/
spool off
edit match_schema.lst
-----Original Message-----
Pather
Sent: Thursday, March 29, 2001 01:11
To: Multiple recipients of list ORACLE-L
Johnson,
You could simply create a database link
and run these queries.
select table_name,column_name from user_tab_columns_at_link
minus
select table_name,column_name from user_tab_columns
/
Do it both ways
select table_name,column_name from user_tab_columns
minus
select table_name,column_name from user_tab_columns_at_link
/
You could add other columns in the query such as data_type, etc.
Or you could use Tools such as Enterprise Manager, Toad, etc. which also
works.
HTH
Suhen
--- Johnson Poovathummoottil <joni_65_at_yahoo.com>
wrote:
> Hi,
>
> I would appreciate if someone could pass me a script
> which compares table structures?
>
> Johnson
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Suhen Pather
INET: Suhen.Pather_at_strandbags.com.au
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Leith
INET: mark_at_cool-tools.co.uk
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu Mar 29 2001 - 05:11:44 CST
 |
 |