Note: Technical Talk being moved to:
www.nazimcricket.com/wiki


Tuesday, 19 August 2008

Oracle - Playing Around with Views

Just mucking about with some SQL to see what tables are referenced in views:

select name
  from dba_dependencies 
 where type='VIEW'
   and referenced_name in ('EMP','DEPT','BONUS');
To narrow this down further to views which use all three tables, we could add:

select name
  from (
select name
  from dba_dependencies 
 where type='VIEW'
   and referenced_name in ('EMP','DEPT','BONUS')
           )
having count(*) = 3 -- Same count as the number of tables
group by name;
To then check the actual SQL script for the views returned here, we could add:
select view_name
     , text
  from dba_views
 where view_name in 
( 
select name
  from (
select name
  from dba_dependencies 
 where type='VIEW'
   and referenced_name in ('EMP','DEPT','BONUS')
           )
having count(*) = 3
group by name  
)
I am sure there are better ways of doing these, but there you have it, a simple SQL to get more information about views and the tables used by them.

No comments: