Search / find through all databases, tables, columns in MySQL
What will you do if one day some one ask you to find single string in all databases, all tables and in all columns, In MySQL Database?
I just read such question and tried to find a “ready made” solution. Reusability is Key Concept 😉 !!
But I ended up finding no “copy-paste” material. Some of the posts like http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm helped me out and supported my thinking of “how to do it” !
Here is the idea for how to search through all databases – tables – columns:
- Create a table for storing output.
- Loop through information_schema database’s COLUMNS table to obtain alldatabases, table and column names.
- Execute a count(*) query on database.table for each column with appropriate search string in where condition.
- If count(*) > 0, that perticular column has the search term.
- Insert that triplet (database name, table name, column name) in to a table.
- Select * from table to view respective database,table and column names having the search term.