GeeCON Prague 2014*
The moose likes Oracle/OAS and the fly likes How to search all the tables of a database for a data item? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "How to search all the tables of a database for a data item?" Watch "How to search all the tables of a database for a data item?" New topic
Author

How to search all the tables of a database for a data item?

Arjun Reddy
Ranch Hand

Joined: Nov 10, 2007
Posts: 627
Hi All,

I want to search all the tables in a database for a data entry say 12232332. By that, I mean I want to know the names of all the tables which have 12232332 in any of their rows. I am using DB Visualizer 4.3.2 and right now for all the tables. I don't have select or any other rights . How do I go about achieving this? I guess I cannot do anything without the rights. Or can I? In case I get the rights how can I achieve this?

Thanks.

Be Humble... Be Nice.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30596
    
154

Arjun,
Yes - you need the DBA to grant you select access to the tables.

The only way I can think of to query all tables, is to write a stored procedure.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Arjun Reddy
Ranch Hand

Joined: Nov 10, 2007
Posts: 627
Jeanne Boyarsky wrote:Arjun,
Yes - you need the DBA to grant you select access to the tables.

The only way I can think of to query all tables, is to write a stored procedure.


Thanks Jeanne for replying. I will try to get the rights tomorrow. Can you tell me the stored procedure to do this?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42050
    
  64
Jeanne Boyarsky wrote:The only way I can think of to query all tables, is to write a stored procedure.

For a solution w/o stored procs one could use the DatabaseMetaData class to find out the names of all tables, then query those individually, and combine the results in the client code.

Ping & DNS - my free Android networking tools app
 
GeeCON Prague 2014
 
subject: How to search all the tables of a database for a data item?