Find Value in database/schema

Question: How to find any value in any column in a schema?

I have been searching for this answer for long, and stumbled upon a beautiful response in Oracle Forums and tried the block and stunning to find the output much easily. This saved my day as I was searching for occurence of a value in entire schema. Thanks for pollywog for the posting.

I will reproduce the anonymous block here for my readers:

aCount pls_integer;
for c in
(select table_name, column_name 
from all_tab_columns 
where owner = 'OWNER' 
and data_type = 'DATATYPE') loop

execute immediate 'select count(*) from '||c.table_name||' where '||c.column_name||' = ''value_to_find'' ' into aCount;
if aCount > 0 then
dbms_output.put_line('found value_to_find in table '||c.table_name||', column '||c.column_name);
end if;
end loop;

Just modify the following in the block:
1. OWNER (The schema in which find is required)
2. DATATYPE (Data type of value which you are searching)
3. value_to_find (The string you are finding)

The output is like:

found value_to_find in table , column

Hope this tip helps you too, leave back a comment in case you like this tip.


  1. You can easily improve the overall runtime
    by adding "and rownum < 2" to your predicates rownum.

  2. it gives runtime error... "sql command not ended properly" on line starting with execute immediate.
    Any clue why its happening.?

  3. Gaurav, which version of Oracle are you using? Also reproduce the code here for my understanding.