CODEWITHSUNDEEP
oracle-database
Bopsi
Bopsi

Reputation: 2424

List of tables having two specific columns

Is there a way to get list of tables in oracle database which has two specific columns (together, not either) .

select table_name from all_tab_columns where column_name in ('CLOUMNNAME1','CLOUMNNAME2');

It gives me all the tables which has either one or both. but I need those ones who have both columns.

Upvotes: 0

Views: 299

Answers (1)

KevinKirkpatrick
KevinKirkpatrick

Reputation: 1456

Just find tables that return 2 rows:

SELECT table_owner, TABLE_NAME FROM ALL_TAB_COLUMNS where column_name in ('CLOUMNNAME1','CLOUMNNAME2') 
GROUP BY table_owner, TABLE_NAME HAVING COUNT(*) = 2;

EDIT: added "table_owner" to avoid tables from different schemas that share table_name

Upvotes: 1

Related Questions