compare columns between two database in different schemas

[mkey]

Hi All,
Scenario:
Schema_one.table_one(id number, name varchar2(10))
Schema_two.table_one(name varchar2(10))
Schema_one.table_two(a number, b varchar2(5))

I need to check the number of columns for all the table that are the same in both schemas. So in this case Schema_two.table_one does not have id column and I want to output that. But I do not want to output the table_two.

My script look something like this:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM DBA_TAB_COLUMNS WHERE
OWNER = 'Schema_one'
UNION
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM DBA_TAB_COLUMNS WHERE
OWNER = 'Schema_two'
MINUS
(SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM DBA_TAB_COLUMNS WHERE OWNER = 'Schema_one'
INTERSECT
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM DBA_TAB_COLUMNS WHERE OWNER = 'Schema_two');

This query is returns table_one id and table_two a and b.
So how can I change my query to filter out for this?
Any thoughts will help.
Thank you for all your support.
mkey

 

[jee]

Try this:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE 
FROM DBA_TAB_COLUMNS D1 
WHERE OWNER = 'Schema_one'
AND EXISTS (
   SELECT TABLE_NAME
   FROM DBA_TABLES
   WHERE OWNER = 'Schema_two'
   AND TABLE_NAME = D1.TABLE_NAME)
UNION
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE 
FROM DBA_TAB_COLUMNS D2 
WHERE OWNER = 'Schema_two'
AND EXISTS (
   SELECT TABLE_NAME
   FROM DBA_TABLES
   WHERE OWNER = 'Schema_one'
   AND TABLE_NAME = D2.TABLE_NAME)
MINUS
(SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE 
FROM DBA_TAB_COLUMNS 
WHERE OWNER = 'Schema_one'
INTERSECT 
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE 
FROM DBA_TAB_COLUMNS 
WHERE OWNER = 'Schema_two');

 

[mkey]

Thank you!!!! JEEE