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;
Comments
Post a Comment