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.
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
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));
I DIDN'T TEST THIS PROCEDURE !!!
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.
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
[Edited by Shestakov on 04-03-2002 at 02:41 PM]
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.
Click Here to Expand Forum to Full Width