Re: How do you search through multiple tables?

in SQL*plus you could do this i think:

if you don't know the name of the field where the string is recorded do:

            select * from (
                     select table_name 
                       from user_tables 
                      where table_name 
            in (your_50_tables))

you can write the response of this SQL request in a text file and then search for your string with a SEARCH query in it!

if you need more info or it doesn't work, email me

Guillaume Lapierre

xpepsi_manx_at_hotmail.com
guillaume.lapierre_at_meq.gouv.qc.ca

Richard Ward<Richard_at_ctcal.ctech.ac.za> wrote in message news:<b2voh1$2dbm$2_at_news.adamastor.ac.za>...
> Not sure how to do it in PLSQL, but in perl, you could do a
>
> $SQL= "select table_name from user_tables
> (optional)
> where table_name like '%your_50_tables%' "
>
> $dbh->do($SQL);
> while((@row) = $sph->fetchrow_array())
> {
> # then have it into a variable and use the variable in your next sql:
>
> $SQL = "select field1 from $row[0] where field2 like '%string to search for%' ";
>
> # send query to do and get back answer.
> }
>
> something like that. get answer from first query, use it to make second query.
> Unfortunatly for perl, you have to have the BDI and the DBD installed.
> but the same concept can be used for Pro*C or anything else (probably PL/SQL as well)
> Hope this helps, mail me if more is needed.
>
> Richard
Received on Wed Feb 19 2003 - 10:25:58 CST