Re: Looking into all the tables of a schema
Date: Wed, 16 Jan 2008 23:55:02 -0800 (PST)
On Jan 16, 3:44 pm, "news.verizon.net" <kenned..._at_verizon.net> wrote:
> "Ratan" <ratan.nit..._at_gmail.com> wrote in message
> > 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
> You have to debug the above a little bit, but it gives you the idea.
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