Oracle SQL how to find out which table has the following two columns?

Question

I know of a statement that I can issue to find out the table with the column I am looking for:

SELECT DISTINCT(table_name)
  FROM all_tab_cols 
 WHERE column_name = 'EMP_ID';

However, how should I modify this so that I may find the table that has both say EMP_ID and EMP_NAME?

Rurik

Answer 1

Use:

  SELECT table_name
    FROM all_tab_cols 
   WHERE column_name IN ('EMP_ID', 'EMP_NAME')
GROUP BY table_name
  HAVING COUNT(DISTINCT column_name) = 2

The count comparison must equal the number of parameters defined in the IN clause.

...but this is a safer means of determining the table in case their are duplicates:

  SELECT table_name
    FROM all_tab_cols 
   WHERE column_name IN ('EMP_ID', 'EMP_NAME')
GROUP BY table_name, owner
  HAVING COUNT(column_name) = 2

Woods

in this you have to take 'owner' condition also... else it would give wrong ans... – Cipher

in all big project you have to work in different different schema and that's why i asked you to put owner condition here as well.... okay say in one schema test_1 table has a column name EMP_ID but not having EMP_NAME, and in other schema it has only EMP_NAME.... just think about this also.. this query will give test_1 also.. :) – Cipher

I agree with pratik garg, and also the DISTINCT is unnecessary when you've added the owner. – Endermic

NOTE THAT You could replace the IN with = and an OR clause, but generalizing this to like may not work because the like could get more than 1 count per term: e.g. SELECT OWNER, TABLE_NAME, count(DISTINCT COLUMN_NAME) as ourCount FROM all_tab_cols WHERE ( (column_name LIKE '%ID%') OR (COLUMN_NAME LIKE '%NUM%') ) GROUP BY OWNER, TABLE_NAME HAVING COUNT(DISTINCT column_name) = 2 ORDER BY OWNER, TABLE_NAME ; – Regrate

If interested, see also my question: #73805157 This generalizes the question so that "LIKE" can be used for two columns. – Regrate