Re: ** help with query?

From: Jim Kennedy <jim>
Date: Sun, 5 Mar 2006 14:46:00 -0800
Message-ID: <ePSdnXbd1J879JbZRVn-og@comcast.com>

"Jack" <jackbader_at_gmail.com> wrote in message news:1141589304.079064.295180_at_e56g2000cwe.googlegroups.com...
> I was hoping there was a simple SQL query so that I could determine
> which table and field contained a particular value.
>
> Jack
>

You would have to use the metadata to generate a series of queries to do the search. eg in sqlplus
spool a_ file.sql
select

' select to_char(rowid),'||
' dt.owner,'||
' dt.table_name,'||
' dtc.column_name'||
' from '||dt.owner||'.'||dt.table_name where dt.column_name like
'''||'%char_to_search_for%'||'''||';'

from

    dba_tables dt,
    dba_table_columns dtc
    where dt.owner=dtc.owner and

                dt.table_name=dtc.table_name
                and dtc.data_type='VARCHAR2'

order by dt.owner,dt.table_name,dtc.column_name spool off
spool results.log
@a_file.sql
spool off

I may have some of the column names wrong etc. Check the reference manual at otn.oracle.com under documentation. But that as a concept should work. It will take a long time to run.
Jim Received on Sun Mar 05 2006 - 16:46:00 CST