CODEWITHSUNDEEP
sqloracle-database
kh.tab
kh.tab

Reputation: 1304

Oracle find all tables that have specific columns

I want to find all tables in my database that have two columns LOCATION and ASSET_ID

So I tried this :

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 :

select owner, table_name, column_name
from all_tab_columns
where 1=1
and column_name ='LOCATION'
and column_name = 'ASSET_ID';

it shows 0 result.

Please help.

Upvotes: 3

Views: 1163

Answers (4)

miracle173
miracle173

Reputation: 1973

Using joins (similar to @BobJarvis' https://stackoverflow.com/a/47984132/754550 but more traditional without with - clause):

select a1.owner,a1.table_name
from all_tab_columns a1, 
    all_tab_columns a2
where a1.owner=a2.owner
    and a1.table_name=a2.table_name
    and a1.column_name='LOCATION'
    and a2.column_name='ASSET_ID'
order by a1.owner,a1.table_name

or using sets:

    select owner, table_name
    from all_tab_columns
    where column_name='LOCATION'
intersect 
    select owner, table_name
    from all_tab_columns
    where column_name='ASSET_ID'
order by owner, table_name

or using 'group by' clause as already posted here https://stackoverflow.com/a/47981174/754550 by @mathguy

select owner, table_name
from all_tab_columns
where column_name in ('LOCATION', 'ASSET_ID')
group by owner, table_name
having count(*)=2
order by owner, table_name

Upvotes: 0

eifla001
eifla001

Reputation: 1157

try this,

SELECT *
  FROM (SELECT a.*,
               COUNT(1) OVER (PARTITION BY table_name) cnt
          FROM all_tab_columns a
         WHERE column_name IN ('LOCATION','ASSET_ID')
         ORDER BY table_name)
 WHERE cnt = 2

Upvotes: -1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

You can do this using a couple of subqueries factored as common table expressions and a join, as in:

WITH cteLOCATION AS (SELECT OWNER, TABLE_NAME
                       FROM ALL_TAB_COLS
                       WHERE COLUMN_NAME = 'LOCATION'),
     cteASSET_ID AS (SELECT OWNER, TABLE_NAME
                       FROM ALL_TAB_COLS
                       WHERE COLUMN_NAME = 'ASSET_ID')
SELECT OWNER, TABLE_NAME
  FROM cteLOCATION
  NATURAL JOIN cteASSET_ID

This is one of the very few situations where I'd ever use NATURAL JOIN.

SQLFiddle here

Best of luck.

Upvotes: 0

user5683823
user5683823

Reputation:

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:

select   owner, table_name
from     all_tab_columns
where    column_name in ('LOCATION','ASSET_ID')
group by owner, table_name
having   count(*) = 2
;

Upvotes: 7

Related Questions