Compare column names SQL-CodePudding

is it somehow possible to compare column names from two different tables that do not have the same structure? (Oracle SQL) I cannot use information_schema.columns. They also have a different number of columns (both more than 30). Is there a possibility with full outer join?

CodePudding user response：

One of the query's you are looking for is:

select *
from ALL_TAB_COLUMNS;

For example you want to find all the columns with the same name as columns in your table named A for some reason...

select *
from ALL_TAB_COLUMNS ATC1
where (owner, column_name) in (select ATC2.owner
                                      , ATC2.column_name 
                               from ALL_TAB_COLUMNS ATC2 
                               where table_name = 'A')

Here is a small demo

CodePudding user response：

If you have access to dba_tables and dba_tab_columns you can use them like:

select * from dba_tables where owner = 'X' and table_name in ('Y' and 'Z');
select * from dba_tab_columns  where owner = 'X' and table_name in ('Y' and 'Z');

If you don' t have access to dba_tables and dba_tab_columns, so you can try to access to all_tables and all_tab_columns. all_tables and all_tab_columns show all tables you have access to.

select * from all_tables where owner = 'X' and table_name in ('Y' and 'Z');
select * from all_tab_columns where owner = 'X' and table_name in ('Y' and 'Z');

Also, there are user_tables and user_tab_columns, but in these dictionary view you can only list the tables are in your schema(user). So, if you have access to the tables that you want to compare you already will reach them by using all_tables and user_tab_columns.

select * from user_tables where owner = 'X' and table_name in ('Y' and 'Z');
select * from user_tab_columns where owner = 'X' and table_name in ('Y' and 'Z');