CODEWITHSUNDEEP
sqloracle-database
HJW
HJW

Reputation: 23443

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

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?

Upvotes: 7

Views: 17543

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332661

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

Upvotes: 13

Related Questions