Re: Searching in complete DB

From: Marc Eggenberger <marc.eggenberger_at_itc.alstom.com>
Date: Wed, 16 Jul 2003 10:11:26 +0200
Message-ID: <MPG.197f271a1856a7269896b4@iww.cacti.ch.alstom.com>

In article <db9bbf31.0307100953.3139fa95_at_posting.google.com>, qazmlp1209 @rediffmail.com says...
> I want to search for a string in the values of all the columns of all
> the accessible Tables. How do you do that in SQL(sqlplus)?
>

I wrote a simple procedure which searches for a string in all Char-  in all tables of a specific schema.

HTH

• Diese Prozedur sucht nach einen String in
• allen CHAR Feldern (VARCHAR2 & CHAR) in
• ALLEN Tabellen eines Schemas

declare

	stmt varchar2(4000);
	type rc	is ref cursor;
	foundentries	 rc;
	v_rowid			 varchar2(20);
	gefunden boolean := false;
	cursor c1 is
		   select table_name, column_name
		   from dba_tab_columns
		   where data_type like '%CHAR%'
		   and OWNER = '&SCHEMA' and table_name not in (select 

view_name from dba_views); --Only search tables, not views

begin

	 for r in c1 loop
	 	 open foundentries for 'select rowid from &SCHEMA' || '.' 

|| r.table_name || ' where ' || r.column_name || ' like ''% &SEARCHSTRING%''';                            

			fetch foundentries into v_rowid;
			loop
				exit when foundentries%notfound;
				dbms_output.put_line('Found entry in table ' 

|| r.table_name || ' in column ' || r.column_name); -- || ' in row ' || v_rowid);

                                exit; --Wenn Wert in einer Column gefunden wird abbrechen. Einmal pro Column reicht.

				--fetch foundentries into v_rowid;
			end loop; 
			   
	 end loop;

end;                     

-- 
mfg
Marc Eggenberger

Received on Wed Jul 16 2003 - 03:11:26 CDT