CODEWITHSUNDEEP
sqloracle-databaseplsqlplsqldeveloperdatabase-metadata
sree
sree

Reputation: 1

Oracle select statement to show matching columns in two tables ? No data just the column names that exist in both the tables

How can i show all the columns which are similar in two tables in oracle db ?

Upvotes: 0

Views: 866

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Another approach is aggregation:

select atc.column_name,
       (case when count(*) = 2 then 'Both'
             when min((atc.owner) = :owner1 and min(atc.table_name) = :table1
             then 'Table1 only'
             else 'Table2 only'
        end) 
from all_tab_columns atc
where (atc.owner = :owner1 and atc.table_name = :table1) or
      (atc.owner = :owner2 and atc.table_name = :table2) 
group by atc.column_name;

The advantage to this approach is that it easily generalizes to showing all columns:

select atc.column_name 
from all_tab_columns atc
where (atc.owner = :owner1 and atc.table_name = :table1) or
      (atc.owner = :owner2 and atc.table_name = :table2) 
group by atc.column_name
having count(*) = 2;

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231661

It sounds like you just want something like this.

select t1.column_name 
  from all_tab_columns t1
 where t1.owner = <<table1 owner>>
   and t1.table_name = <<table1 name>>
intersect
select t2.column_name 
  from all_tab_columns t2
 where t2.owner = <<table1 owner>>
   and t2.table_name = <<table1 name>>

You could write it as a join or as an exists as well if you'd rather. But intersect makes more sense to me from a readability perspective. You could use dba_tab_columns or user_tab_columns rather than all_tab_columns depending on what privileges you have in the database, whether you know the tables are in your current schema, etc.

Upvotes: 1

Related Questions