MySQL search database | MySQL search all tables

“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:

1) SQLyog

2) phpMyAdmin

3) HeidiSQL

SQLyog

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.

phpMyAdmin

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?

HeidiSQL

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!

About these ads

6 Comments

Filed under MySQL

6 responses to “MySQL search database | MySQL search all tables

  1. I’ve used this on the :inux command line a few times (Provided the database isnt too big ;p )

    $ mysqldump salika –extended=FALSE | grep jack | less -S

  2. paranoiq

    Adminer is also capable of serching across all tables in database. http://www.adminer.org/en/

  3. Sam

    Is there simply an sql query or a complex combination of them to search a string..

  4. Pingback: MySQL – ganze Datenbank durchsuchen | tech@jbmedia.info

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s