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

Question

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

question from:https://stackoverflow.com/questions/66051926/oracle-select-statement-to-show-matching-columns-in-two-tables-no-data-just-th

Answer 1

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.