Re: select for tables?

From: Tom Zamani <tomz_at_redflex.com.au>
Date: 2000/05/02
Message-ID: <8elk1l$gkj$1@perki.connect.com.au>#1/1

It can be done,

• select all the tables which have a column with the name customer_id like for c1 in (select table_name from all_tab_columns where owner ='YOU' and column_name ='CUSTOMER_ID') LOOP --for each table create a dynamic SQL SQLSTR:=' select 1 from '||c1.table_name||' where customer_id=12345;' using dynamic sql you can get the value and if it was 1 then you know waht. END LOOP;

Good luck tom

Gregory Bloom <gjbloom_at_yahoo.com> wrote in message news:sgs2jtsfoog108_at_corp.supernews.com...
> Pie-eyed newbie question of the week:
>
> Is there a way to find all tables that have a particular column with a
> particular value?
> Something like "select tablename from * where customer_id='12345'" which,
> let's pretend, would give a list of all tables which have customer_id as a
> column and which satisfy customer_id='12345'.
>
> Is this possible? Or do we really have to know all the tables having
> customer_id and search each of them individually?
>
>
Received on Tue May 02 2000 - 00:00:00 CDT