WOW – you want to search every column in every table looking for some piece of data?
Yes, you can build some queries to do that. I have done it on a limited set of tables during one of the conversions I was involved in. (there were six characters that had to be found and dealt with.)
Are you sure you want to do this? If you have lots of data, this could run for a LONG time.
first you will have to get a list of all tables from your RDBMS catalog
Can you limit or restrict yourself to only certain tables?
then a list of all columns in each of those tables.
can you limit the columns? to only numeric? or not dates?
then a SELECT statement for each of the columns in each table with a WHERE clause using a LIKE with wildcards at the front and the end of the string you are searching for
OR the WHERE could use a LOCATE function if your RDBMS has one.
I would suggest using SQL to create the SQL you will actually use to search for your data. It will save lots of typing.
You might INSERT into a results table when you find what you are looking for.
this is a sample of what you might want to end up with – this is for only one column in one table. this would be repeated for each and every column in every table that you need to search.
INSERT INTO results_table
‘table_owner_or_schema . table_name . column_name’
FROM schema . table_name
WHERE LOCATE (‘what you are looking for’,column_name) <> 0
I looked but I cannot locate the code I used back when I had to do this. I never throw anything away so it is here someplace. If I can find it, I will return to post it here.
good luck. this is not trivial. I again I caution on the run time this might take.