How do you search through multiple tables?

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Wed, 19 Feb 2003 16:57:23 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA70353AF3D@lnewton.leeds.lfs.co.uk>

Afternoon Guillaume,

I'm afraid all you'll get with that query is a list of 50 tablenames :o(

Regards,
Norman.

---

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

-----Original Message-----
From: xpepsi_manx_at_hotmail.com (Guillaume Lapierre) [mailto:xpepsi_manx_at_hotmail.com]
Posted At: Wednesday, February 19, 2003 4:26 PM Posted To: server
Conversation: How do you search through multiple tables? Subject: 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:57:23 CST