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

[Arjun Reddy]

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.

[Jeanne Boyarsky]

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.

[Arjun Reddy]

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]

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.