MySQL: search for specific column names

Posted by Matt Thommes on November 20, 2009 | Post type: Gain

MySQL has powerful search features, but most users only utilize search for actual data records.

A often useful feature is the ability to search amongst column names.

For example, let's say you need to go through an entire application to find all sections that use a "sort order" column when displaying data through the interface. There is a new bit of interface logic you need to add to each of those sections.

Without knowing every page that uses a "sort order" column when displaying data, you could query the database to find all tables that have a field containing the word "sort":

SELECT table_name, column_name
FROM information_schema.columns
WHERE column_name LIKE '%sort%';

An example of the results I get when issuing this query on a particular database server:

Screenshot of phpMyAdmin

As you can see, it quickly found all of the different tables that have a field containing the word "sort." This helps me pinpoint which interface sections need attention.

information_schema is installed by default for every MySQL server, and contains information about all of the other databases on the server, and is itself a MySQL database, which can be searched upon or accessed in the same way as databases you create.

Here is an example view from phpMyAdmin:

Screenshot of phpMyAdmin

About the author(s)

Matt Thommes is an independent publishing enthusiast, mobile blogger, content creator, informative writer, web developer from a suburb of Chicago. Never one to conform, Matt intends to promote the effect the web has on our lives, in an effort to intensify, instruct, and clarify all that is happening around us.

Comments

Note: Comments may be viewed by authors, but if you have a more specific question you'd like to ask them, please email matt.thommes@paininthetech.com.