How to find same columns in multiple tables/views
********************************************************
col column_name for a20;
select
a.table_name,a.column_name,
b.table_name,b.column_name,
c.table_name,c.column_name
from
(select * from all_tab_columns where table_name='DBA_DATA_FILES') a
inner join
(select * from all_tab_columns where table_name='DBA_FREE_SPACE') b
on a.column_name=b.column_name
inner join
(select * from all_tab_columns where table_name='DBA_EXTENTS') c
on b.column_name=c.column_name;
*************************************************************
TABLE_NAME COLUMN_NAME TABLE_NAME COLUMN_NAME TABLE_NAME COLUMN_NAME
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
DBA_DATA_FILES FILE_ID DBA_FREE_SPACE FILE_ID DBA_EXTENTS FILE_ID
DBA_DATA_FILES TABLESPACE_NAME DBA_FREE_SPACE TABLESPACE_NAME DBA_EXTENTS TABLESPACE_NAME
DBA_DATA_FILES BYTES DBA_FREE_SPACE BYTES DBA_EXTENTS BYTES
DBA_DATA_FILES BLOCKS DBA_FREE_SPACE BLOCKS DBA_EXTENTS BLOCKS
DBA_DATA_FILES RELATIVE_FNO DBA_FREE_SPACE RELATIVE_FNO DBA_EXTENTS RELATIVE_FNO
***********************************************************************************
-------------------------------
col table_name for a20;
col column_name for a20;
select
a.table_name,a.column_name,
b.table_name,b.column_name,
c.table_name,c.column_name
from
(select * from all_tab_columns where table_name = upper('&table_a')) a
inner join
(select * from all_tab_columns where table_name = upper('&table_b')) b
on a.column_name=b.column_name
inner join
(select * from all_tab_columns where table_name = upper('&table_c')) c
on b.column_name=c.column_name;
******************************************************
