Whole database search (solution)

From: Tom T <tomtul2_at_yahoo.com>
Date: 18 Jul 2003 11:27:13 -0700
Message-ID: <fcd672c.0307181027.32f0cedd@posting.google.com>

this is supposed to be a followup to an earlier message which was close but gave me syntax errors.

spool to a file:
  spool mystring.sql

  select 'select distinct '''||table_name||''', 
'||owner||'.'||table_name||'.'||column_name
       ||' from '||owner||'.'||table_name ||' where '||column_name ||'
like '||
        '''%SEARCHSTRING%'''||';'

  from all_tab_columns
  where data_type like 'VARCHAR2' and data_length >= LEN_SEARCHSTRING;

  spool off

which produces a bunch of selects, one for each varchar2 column in the db:

  select distinct 'EMPLOYEE', TIGER.EMPLOYEE.NAME from   TIGER.EMPLOYEE where NAME like '%SEARCHSTRING%';

then run the spooled file:
  spool mystring.lst
  @mystring.sql

---Original post is:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=383B724A.BFC4A0EC%40cuug.ab.ca

(From: Rachel Rainey (raineyr_at_cuug.ab.ca) Subject: Re: Whole database search?
Newsgroups: comp.databases.oracle.server Date: 1999/11/24 )

yes -- I used to have a script running on a cron job to look for stuff like that at night.
suppose your text is 5 characters long,
I'd spool to a file:
spool mystring.sql
select 'select distinct owner||'.'||table_name||'.'||column_name from '|| owner||'.'||table_name ||' where '||column_name ||' = '|| ''''your-string-in-question''''||';'
from all_tab_columns where
column_type like '%CHAR%' and column_length >= 5; spool off

then run the spooled file:
spool mystring.lst
@mystring.sql

'select distinct owner||'.'||table_name||'.'||column_name from '|| Received on Fri Jul 18 2003 - 13:27:13 CDT