Search All Tables for a Value

[Bonediggler1]

Experts-

I need some code that will search all tables/fields in a schema for a specific value. The db is relatively small so this isn't a huge issue. Not being very conversant with pl/sql, I cannot get the code I have found elsewhere on the net to work.

Thanks in advance for your help.

 

[carp]

What is the datatype of the value you are looking for?

 

[Bonediggler1]

Should be varchar(80)

 

[SantaMufasa]

Can you please post the other code you have found to be unsuccessful (along with the problems you have encountered)?

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Bonediggler1]

This is the code sample:

select
table_name,
column_name
from
(select
rownum,
table_name,
regexp_substr(dbms_xmlgen.getxml(‘select * from “‘||table_name||’”‘),’<[^>]*>&string</[^<]*>’) column_name from user_tables) where length(column_name)!=0;

Enter value for string: email


I am getting this error message: - Oracle Database Error: ORA-01740: missing double quote in identifier