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.