Share
Go down
avatar
Admin
Posts : 155
Join date : 2017-11-11
Age : 27
View user profilehttp://net-media.yoo7.com

Oracle find all tables that have specific columns

on Tue Dec 26, 2017 5:39 pm
Oracle find all tables that have specific columns
Code:
select owner, table_name, column_name
from all_tab_columns
where column_name in ('LOCATION','ASSET_ID');
the problem is this query give all tables that have LOCATION OR ASSET_ID not both of them.

so i changed it to this :
Code:
select owner, table_name, column_name
from all_tab_columns
where 1=1
and column_name ='LOCATION'
and column_name = 'ASSET_ID';

1 Answer
Select all the rows as in your initial attempt. Then group by owner and table_name and keep only those that have TWO rows returned in the initial query. Use the having clause for that:
Code:
select  owner, table_name
from    all_tab_columns
where    column_name in ('LOCATION','ASSET_ID')
group by owner, table_name
having  count(*) = 2
;
Back to top
Permissions in this forum:
You can reply to topics in this forum