CODEWITHSUNDEEP
oracle-database
Flood
Flood

Reputation: 3

How to get all the table names used in an Oracle view?

I want to get a list of all the table names used in an Oracle view or a SQL query.

For example, from below query

select a.col1, b.col2
from first_table a, second_table b;

I would like to get this result:

first_table
second_table

I have a customer creating very complex SQL queries as views and I would like to have a quick way to extract all the table used in it. I found a way in perl but I would like to do it in SQL.

Upvotes: 0

Views: 991

Answers (1)

Multisync
Multisync

Reputation: 8797

This query gives you all dependent tables (and their owners) of the view VIEW_NAME located in the schema VIEW_OWNER

select ud.referenced_owner tab_owner,
       ud.referenced_name tab_name
from all_dependencies ud
where ud.name = 'VIEW_NAME' 
  and ud.type = 'VIEW' 
  and ud.referenced_type = 'TABLE'
  and ud.owner = 'VIEW_OWNER';

You may use USER_DEPENDENCIES Oracle dictionary if all the tables and the view are in your schema or ALL_DEPENDENCIES if there can be tables from different schemas.

Upvotes: 2

Related Questions