jQuery in Action, 3rd edition
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: 629
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: 32495
    
214

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.


[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Arjun Reddy
Ranch Hand

Joined: Nov 10, 2007
Posts: 629
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
Rancher

Joined: Mar 22, 2005
Posts: 42958
    
  73
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.
 
Have you checked out Aspose?
 
subject: How to search all the tables of a database for a data item?
 
It's not a secret anymore!