Hi guys,

I know a string is coming out from the database (DB2), but I don't know which table and column(s) is it coming from.

What's the query I can use to find that out?

Thanks in advance.

Jiang

You can talk to whom ever designed what ever is giving you the string. Other wise you are doomed

Query all the data in your database, i.e. find all tables and all string columns, then query those columns to see if some value is the string you're looking for. And even that may not give you the desired answer if the string occurs in multiple tables or rows or in tables to which you have no direct access.

Using the information_schema, we can get all the tables and fields from the database. I probably can write a script to query all the fields to see if any of those contains that string...

I am looking for an elegant way (one query) of doing this, and I have used such a query on mssql server...

If you have such a query handy, could you please share it here?

Thanks again,

Jiang

This is crazy. You have no idea where the value is coming from and you are planning on querying the WHOLE db to find it?!?!

You have to query not only string columns. You have to query EVERY column as any data type could have been converted to string. Heck, that string could have been hardcoded to begin with and did not come from any of your tables.

I would think you have a better chance of winning a lotto tonight then finding this value.

Have you considered doing what I recomended originally?

You can't do that in a single query. And as Cougar says, there are a lot of scenarios where you will never find the row. Let's just assume that you have table X in your database. No user has SELECT privileges on that table - but some packages do. You invoke a stored procedure that executes statements in those packages and you get the value back. When you now try to query all the data in your database, you won't be able to access this table - you will not get the desired information. You have lost.

So let's step back a little: why would you want to do something like that in the first place? Can you explain the reasoning?

All previous comments would indeed also be my first answers to such a question.
Another option could be to export or unload the whole bunch of tablespaces into a single file, then use some OS tools (like grep on Unix, or an editor) to search for the needed string, then try to make sense of the location of the hit(s).
This may require a lot of disk storage, though ...