Re: Looking into all the tables of a schema

From: Ratan <ratan.nitrkl_at_gmail.com>
Date: Wed, 16 Jan 2008 23:55:02 -0800 (PST)
Message-ID: <96ff8b45-185e-4e80-864e-5fb0e241a2f3@s27g2000prg.googlegroups.com>


On Jan 16, 3:44 pm, "news.verizon.net" <kenned..._at_verizon.net> wrote:
> "Ratan" <ratan.nit..._at_gmail.com> wrote in message
>
> news:d75c8bf5-a9ca-4ee5-9d6b-5e3955b1fc7f_at_s8g2000prg.googlegroups.com...
>
> > Hi everybody,
>
> > Is there any method using which I can search some numbers in all
> > numeric fields in all the tables of a particular schema ????
>
> > This is urgent. Thnaks in advance.
>
> Use sql to generate sql and run the result.
>
> eg something like:
> spool tmp.sql
>
>  select 'select * from '||owner||'.'||table_name||' where
> '||column_name||'=myvalue;' from dba_tab_columns dtc
> where dtc.owner='THESCHEMA' and dtc.column_type='NUMBER' order by
> table_name, column_name;
>
> spool off
> @tmp.sql
>
> You have to debug the above a little bit, but it gives  you the idea.
> Jim

Thanks a lot Jim. I have got my answer.

But the problem is, I have to login as SYSDBA to execute this query. As an ordinary user, I cant execute, because, then it cant find the dba_tab_columns table. So, what is the alternative? Received on Thu Jan 17 2008 - 01:55:02 CST

Original text of this message