CODEWITHSUNDEEP
oracle-database
No clue
No clue

Reputation: 11

Query to compare columns in multiple tables in Oracle

I have 3 tables with similar columns and the same primary key, let's call them tables T1, T2 and T3. The tables don't have the exact same number of columns, but some columns exist in all 3 tables.

I want to be able to, given the table names for T1, T2 and T3:

  1. list all columns from (T1+T2+T3)
  2. for each column, I want to see:
    • if they exist or not for each table
    • if they exist, their values(*)
    • a comparison of their values in each table(*)

(*) for a specific record

Something like below, where "-" means that the column doesn't exist for that table:

Column         T1     T2     T3      Comparison
-------        ----   ----   ----    ----------
C1             "A"    "A"    "A"     EQUAL
C2             -      "B"    "B"     DIFFERENT
C3             "C"    null   "C"     DIFFERENT
C4             "D"    "E"    "F"     DIFFERENT
C5             -      "G"     -      DIFFERENT
C6             null   null    null   EQUAL

Is there any way to do this?

Upvotes: 0

Views: 426

Answers (1)

Ronnis
Ronnis

Reputation: 12843

I can solve the first part for you. This will mark with an 'X' if the column exist in each table. 

select column_name
      ,max(case when owner = 'schema' and table_name = 'T1' then 'X' end) as t1
      ,max(case when owner = 'schema' and table_name = 'T2' then 'X' end) as t2
      ,max(case when owner = 'schema' and table_name = 'T3' then 'X' end) as t3
  from all_tab_columns
 where (owner = 'schema' and table_name = 'T1')
    or (owner = 'schema' and table_name = 'T2')
    or (owner = 'schema' and table_name = 'T3')
 group 
    by column_name;

Upvotes: 1

Related Questions