-1

Right now i have 100 tables in SQL and i am looking for a specific string value in all tables, and i do not know which column it is in.

select * from table1, table2 where column1 = 'MyLostString' will not work because i do not know which column it has to be in.

Is there a SQL query for that, must i brute force search every table for every column for that 'MyLostString'

If I were to brute-force search across all tables, is there an efficient query for that? For instance:

select * from table3 where allcolumns = MyLostString

bouncingHippo
  • 5,940
  • 21
  • 67
  • 107
  • Please do not use implicit joins ever again, they are a SQL antipattern and are 20 years out of date. – HLGEM Dec 13 '12 at 19:44
  • 1
    Why do you not know your datamodel well enough to know where the data will be stored? – HLGEM Dec 13 '12 at 19:44
  • i'm new to the company, and my tech architect is away on vacation. i know where the column heading is from the View by looking through the console. but my tech leads wants the column heading from the Table, not the View. – bouncingHippo Dec 13 '12 at 19:47
  • Yes it is possible but depends on RDBMS. What RDBMS do you use? Please add according TAG to your question. [Here is an example for MS SQL](http://stackoverflow.com/questions/591853/search-for-a-string-in-an-all-the-tables-rows-and-columns-of-a-db) – valex Dec 13 '12 at 19:49
  • why not look at the view script in your source control? – HLGEM Dec 13 '12 at 19:51
  • @HLGEM thanks for advising the view script – bouncingHippo Dec 13 '12 at 20:01

1 Answers1

1

It is the defining feature of a RDBMS (or at least one of them), that the meaning of a value depends on the column it is in. E.g.: The value 17 will have quite different meanings, if it stands in a customer_id column, than in the product_id of a fictional orders table.

This leads to the fact, that RDBMS are not well equipped to search for a value, no matter in which column of which tables it might be used.

My recommendation is to first study the data model to try and find out, which column of which table should be holding the value. If this really fails, you have a problem much worse than a "lost string".

The last ressort is to transform the DB into something better suited for fulltext search ... such as a flat file. You might want to try mydbexportcommand --options | grep -C10 'My lost string' or friends.

Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92