Re: Procedure to search ALL tables for a value?

From: Vitaliy Mogilevskiy <vit100gain_at_earthlink.net>
Date: Thu, 20 May 1999 17:05:47 -0700
Message-ID: <3744A35A.E17487CB@earthlink.net>

Here you go:

--
-- START
--

rem
rem File:        u_find_string.sql
rem Created:     Vitaliy Mogilevskiy (vit100gain_at_earthlink.net)
rem Desc:        Searches for character string in all tables and
rem                  views. Will prompt you for a string and owner.

rem

set serveroutput on size 1000000
accept string char prompt "Enter Char String to search for:" accept owner_name prompt "Enter Owner Name:"

set term off
set verify off
set lines 80
set feedback off

spool find_string.tmp

prompt spool find_string.out
prompt set feedback off
prompt set pages 1000
prompt set lines 132
prompt set wrap on
prompt break on found_in skip 1
prompt col found_in format a55 heading "Found In <owner>.<object_name>.<column_name>" prompt col string format a70 heading "String Found" prompt set term on
prompt

declare

cursor varchar_tables_cur IS

    select owner

    ,      table_name
    ,      column_name

    from dba_tab_columns

    where  owner != 'SYS'
    and    owner != 'SYSTEM'
    and    owner  = '&OWNER_NAME'

    and data_type in ( 'VARCHAR2','VARCHAR','CHAR');

begin
 for v_t_rec in varchar_tables_cur loop

      dbms_output.put_line('--');
      dbms_output.put('select '''||v_t_rec.owner||'.'||v_t_rec.table_name||'.'||v_t_rec.column_name||''' Found_In ,');
      dbms_output.new_line;
      dbms_output.put(''||v_t_rec.column_name||' String ');
      dbms_output.new_line;
      dbms_output.put_line('from   '||v_t_rec.owner||'.'||v_t_rec.table_name||'');
      dbms_output.put_line('where  '||v_t_rec.column_name||' like '||chr(39)||'&string'||chr(39)||'');
      dbms_output.put_line('/');

 end loop;
end;
/

prompt spool off
prompt prompt ====> created file find_string.out for your review

spool off

@find_string.tmp

--
-- END
--

You can also find this and many more scripts at my WEB Page: http://home.earthlink.net/~vit100gain/index.html Follow "SQL Scripts" link and then "Utility Scripts"

Vitaliy Mogilevskiy

Current User wrote:

> I would like to get some assistance in writing a procedure to search
> every table, and every column, in an instance for a specified value.
>
> Every time I try to refer to a column in the cursor from the body of the
> procedure, I get a compilation error saying the reference is out of
> scope.
>
> Anyone have a procedure like this, or willing to give a skeleton of how
> to do it?
>
> Thanks.
Received on Thu May 20 1999 - 19:05:47 CDT