“What will you do if one day some one ask you to find single string in all databases, all tables and in all columns?”
— This was asked to me once. There is some string in the database but I do not know where. There was a task to find an user’s details which is in the database which consists of many tables. And all I know is only the user name and do not know any other details. How am I supposed to find that string? I do not want to write some complex SQL for this. Digging more into this, I came across few GUI tools for MySQL which have good search feature.
So I am writing this blog about search feature in those tools. I shortlisted three client application for MySQL:
There is a feature called ‘Data Search‘ in SQLyog. To open this right-click on a database or connection root level. There are few filter options in this tool. You can select multiple databases and search based on a datatype.
It works like this-
- Type a text(Do filter operations if needed).
- Hit Search or press Enter.
That’s it! It populates 20 results in the search result and there are ‘First’, ‘Previous’ and ‘Next’ links which works like a Search Engine. The result can be viewed/edited in the same tab when clicking a search result.
If you have phpMyAdmin installed then use the Search feature.
- Select a database and click Search tab.
- Enter the text to search(You may use Regular expression too).
- Choose tables/columns to search.
Doing these will get you to the search results where you can ‘Browse’ and view the data. The down side in this search is that it cannot search the whole server. It does a pretty neat job under a single database, but what about searching in multiple databases?
There is an option in HeidiSQL- ‘Find text on server‘. You can select multiple databases on the left hand tree and press ‘Find’. After searching it populates the results where you can press ‘See Results’ to view the text. To view the data it opens a query tab with results in the Result view.
It doesn’t search under particular columns. Also, if you want to see a particular table search result, clicking on ‘See results’ opens up all the search results. But this search is very good for a free software.
So the conclusion is, Sometime we need to find out a small piece of string in big Database. Like where is the configuration is saved, or where is Mark’s Date of birth is saved etc. These above mentioned amazing tools help us achieve it!