MySQL - Search in all fields from every table from a database

Question

I know that probably it's not possible to do this, but just want to check it out with you, guys... Is it possible to do something like this, using MySQL? I want to search in all fields from all tables a given string:

SELECT * FROM * WHERE * LIKE '%stuff%'

Answer 1

you can peek into the information_schema table. It has a list of all the tables and all the fields that are in the table. You can then run queiries using the information that you have gotten from this table.

Answer 2

You could use

SHOW TABLES;

Then get the columns in those tables (in a loop) with

SHOW COLUMNS FROM table;

and then with that info create many many queries which you can also UNION if you need.

But this is extremely heavy on the database. Specially if you are doing a LIKE search.

Answer 3

You can use this project: http://code.google.com/p/anywhereindb

This will search all the data in all table.

Answer 4

Check this link: http://kedar.nitty-witty.com/miscpages/mysql-search-through-all-database-tables-columns-stored-procedure.php

Answer 5

Other posts too: http://kedar.nitty-witty.com/blog/search-through-all-databases-tables-columns-in-mysql/

Answer 6

If you have phpmyadmin installed use it's 'Search' feature.

• Select your db
• Be sure you do have a DB selected and not a table...or you get a completely different search dialog
• Click 'Search' tab
• Choose the search term you want
• Choose the tables to search

I have used this on up to 250 table/10GB databases (on a fast server) and the response time is nothing short of amazing.