How can I search a schema for a particular value in all tables?

[smoothyc]

Is there a way to search a schema and get the table and column name(s) that holds a particular value?

I'm need to find what column(s) in the schema that has the value "Charlotte" in it.

Any ideas or hints would be greatly appreciated.

Thanks,
smoothyc

[Shestakov]

It should be store procedure or pl/sql block (for example like) :

create or replace
procedure find_in schema(p_owner varchar2, p_value varchar2) is
cmd varchar2(1000);
n_rows number;
begin
fop rt in (select table_name from all_tables where p_owner=owner) loop
for rc in (select column_name from all_tab_columns
where p_owner = owner and table_name = rt.table_name)
cmd := 'select count(*) from '||p_owner||'.'||rt.table_name||
' where '|| rc.column_name ||'='''||rtrim(p_value)||''';
-- or with like : ' where '|| rc.column_name ||' like ''%'||rtrim(p_value)||''%';
execute immediate cmd into n_rows;
if n_rows > 0 then
dbms_output.put_line('Found value ('||p_value||') in table '||rt.table_name||'['||rc.column_name||']'
dbms_output.put_line('# of rows with this value = '||to_char(n_rows));
end if;
end loop;
end loop;
end;
/

I DIDN'T TEST THIS PROCEDURE !!!

[smoothyc]

Thanks Shestakov.

I'll take a closer look at this and see what I come up with. I'll post the results if/when I get them.

Thanks again,
smoothyc

[Shestakov]

Note about impruve this script (procedure):

1. check type of each columns
LOOP 2 -->

select column_name from all_tab_columns
where p_owner = owner and table_name = rt.table_name
AND DATA_TYPE in ('CHAR','VARCHAR2')

2. trunc trailing blanks in CHAR fileds

where rtrim('||rc.column_name||')='''||rtrim(p_value)||''';

[Edited by Shestakov on 04-03-2002 at 02:41 PM]

[smoothyc]

Shestakov,

I found the table.column that houses the value I was looking for. I was unable to get you PL/SQL script to work.

However, I'm now on a quest to get that to work (or create one very similar). Thanks again for your help. I'll post the script, once I get it to work the way I need it to.

Thanks again,
smoothyc