0

Is there a pure sql solution to search in all tables for a specific field with a given value or name. In pseudocode the query im looking for would be

SELECT * FROM * WHERE field = "value"

or

SELECT tablename, field FROM *

I already know how I could use the mysql information schema to search for all tables with the column, but I'm unsure if (and how) I could combine this inside a query (or stored procedure).

Philipp
  • 15,377
  • 4
  • 35
  • 52
  • 1
    what is REAL problem You want to resolve? – Jacek Cz Jun 28 '18 at 15:43
  • i believe one one the best architecural patters is relational data (busines or transactional or simmilar meaning) and connected text searcher to some level 'offline' (lucene or simmilar, or extra functionality fro DB provider) – Jacek Cz Jun 28 '18 at 15:45
  • @JacekCz I got a database dump with lots of tables, where some fields have invalid/missing foreign keys and now I have to fix this/dig into it. So the query don't need to be robust and only runs a few times – Philipp Jun 28 '18 at 15:57

1 Answers1

1

You can write a script wich will generate all queries:

SELECT concat('SELECT * FROM ',TABLE_NAME,' WHERE ',COLUMN_NAME,' =\'value\'')
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE COLUMN_NAME = 'columnname'
;

Then, you can execute each rows of the result to get the needed data.

Kobi
  • 2,494
  • 15
  • 30