sql - Find all views that contain a certain table or column -

in legacy database infrastructure, how best find views access table or column? i'm refactoring tables (i.e. delete unused columns) , want find views still rely on columns , break, if remove columns.

is there tool/feature search through view definitions in oracle sql developer?

you can use function dependent_views, code below. example usage:

select dependent_views('customer_name', 'customers') list dual  output: list ----------------- scott.v_persons 

function searches dependendent views in all_dependencies, next searches text column all_views occurence of column_name.

note: because all_dependences may not contain full data of dependent objects (for instance when view created execute immediate) - function may not find object. if column_name substring of other column - function may return many views.

create or replace function dependent_views   (i_column varchar2, i_table varchar2, i_owner varchar2 default user)   return varchar2    o_ret varchar2(4000) := '';   v_text long := ''; begin   o in (     select * all_dependencies        referenced_name  = upper(i_table)         , referenced_owner = upper(i_owner)         , type = 'view')   loop     begin       select text v_text all_views          view_name = o.name , owner = o.owner;     exception when no_data_found        null;     end;     if upper(v_text) '%'||upper(i_column)||'%'       o_ret := o_ret||o.owner||'.'||o.name||'  ';     end if;   end loop;   return o_ret; end dependent_views;