HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Search views for used columns - simple script
ORACLE STUFFS
17-Mar-2009
Sometimes it is useful to search all views using certain column.
Dependencies in the Oracle data dictionary do not allow analysis on column level (10g). Definition of view is stored in a LONG datatype column in data dictionary. Following simple script helps to find views with a column (table, function) within its definition.

set serveroutput on
Declare 
  c_column_name varchar2(30):='&column_name';
  v_text VARCHAR2(32656);
Begin
  DBMS_OUTPUT.ENABLE(1000000);
  for r1 in (select owner,view_name,text_length from all_views)LOOP
    if r1.text_length>32656 then
      DBMS_OUTPUT.PUT_LINE('... too long '||r1.view_name);
    else    
      select text into v_text from all_views where view_name=r1.view_name and owner=r1.owner;
      if regexp_like(v_text,'\W'||c_column_name||'\W') then
         DBMS_OUTPUT.PUT_LINE(r1.view_name);
      end if;
    end if;  
  end LOOP;  
end;
/

[Download]



Following variant uses temporary table to be able to solve views with the source longer than 32656 bytes:

Declare
  c_column_name varchar2(30):='@search_text';
  ---
  TYPE t_cursor IS REF CURSOR;
  c1 t_cursor;
  v_view_name Varchar2(100);
  procedure droptemp is
  Begin 
    execute immediate 'drop table A$FINDVIEW purge';
    Exception when others then 
       if sqlcode != -942 then 
         raise; 
       end if;
  end Droptemp;
Begin
  droptemp;
  execute immediate q'~create table A$FINDVIEW  as select view_name, text_length, to_lob(text) as text from all_views~';
  DBMS_OUTPUT.ENABLE(1000000);
  open c1 for q'~select view_name from A$FINDVIEW where regexp_like(text,:c,'i')~' USING c_column_name;
  LOOP
    fetch c1 into v_view_name;
    DBMS_OUTPUT.PUT_LINE(v_view_name); 
    exit when c1%NOTFOUND;
  end LOOP;
  close c1;
  droptemp;
end;

[Download]
Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky