CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloperlegacy
user1438038
user1438038

Reputation: 6079

Find all views that contain a certain table or column

In a legacy database infrastructure, how do I best find views that access a certain table or column? I'm currently refactoring certain tables (i.e. delete unused columns) and I want to find all views that still rely on those columns and will break, if I remove the columns.

Is there any tool/feature to search through all view definitions in Oracle SQL Developer?

Upvotes: 3

Views: 9079

Answers (3)

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

You can use something like function dependent_views, code below. Example usage:

select dependent_views('CUSTOMER_NAME', 'CUSTOMERS') list from dual

Output:
LIST
-----------------
SCOTT.V_PERSONS

Function searches dependendent views in ALL_DEPENDENCIES, next searches TEXT column from ALL_VIEWS for occurence of column_name.

Note: Because all_dependences may not contain full data of dependent objects (for instance when view was created by execute immediate) - my function may not find this object. Also if column_name is substring of other column - function may return to many views.

create or replace function dependent_views
  (i_column varchar2, i_table varchar2, i_owner varchar2 default USER)
  return varchar2 is

  o_ret varchar2(4000) := '';
  v_text long := '';
begin
  for o in (
    select * from all_dependencies 
      where referenced_name  = upper(i_table)
        and referenced_owner = upper(i_owner)
        and type = 'VIEW')
  loop
    begin
      select text into v_text from all_views 
        where view_name = o.name and owner = o.owner;
    exception when no_data_found then 
      null;
    end;
    if upper(v_text) like '%'||upper(i_column)||'%' then
      o_ret := o_ret||o.owner||'.'||o.name||'  ';
    end if;
  end loop;
  return o_ret;
end dependent_views;

Upvotes: 4

veljasije
veljasije

Reputation: 7092

Cause you search for all views that access certain table, this might help:

select 
    name, 
    type, 
    referenced_name, 
    referenced_type
from user_dependencies 
where type = 'VIEW' 
    and referenced_type = 'TABLE'

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

how do I best find views that access a certain table

You could query the [USER|ALL|DBA]_DEPENDENCIES view.

SELECT name ,
  type ,
  referenced_name ,
  referenced_type
FROM user_dependencies
WHERE TYPE   = 'VIEW' 
AND NAME = '<VIEW_NAME>'
AND referenced_type = '<TABLE_NAME'>;

To get the result for all the views at once, remove the filter NAME = '<VIEW_NAME>'.

For example,

SQL> column name format a15
SQL> column type format a15
SQL> column referenced_name format a15
SQL> column referenced_type format a15
SQL> SELECT name ,
  2    type ,
  3    referenced_name ,
  4    referenced_type
  5  FROM user_dependencies
  6  WHERE TYPE   = 'VIEW';

NAME            TYPE            REFERENCED_NAME REFERENCED_TYPE
--------------- --------------- --------------- ---------------
EMP_CUSTOM_VIEW VIEW            EMP             TABLE
EMP_VIEW        VIEW            EMP             TABLE

SQL>

Upvotes: 0

Related Questions