djsipe.com | Web Development

A common problem in complex database systems is an over-abundance of tables, columns, procedures, functions, and the databases that hold them.  Trying to figure out the right columns to use in that reporting query your boss wants by the end of the day can get just a little overwhelming.  Thankfully, there is an easy way to drill down into the structure of your database so you can find just what you’re looking for.

Behold, your information_schema database… The information_schema database is a special database that MySQL uses to keep track of its own internal plumbling.  It holds meta information on every field, column, table, and database on your MySQL server.  For this reason you must treat this database like royalty.  No inserting, updating, or deleting anything unless you want to have to rebuild all your tables.  That said, running selects is harmless enough and if you keep it to that you should be just fine.

Searching For a Column

Here’s a quick way to search for columns in any database by column name:

-- Search for column name containing "bar"
SELECT *
FROM information_schema.COLUMNS
WHERE
COLUMN_NAME LIKE '%bar%';

Searching For a Table

Need to narrow it down? Search by column name and table name.

-- Search only in tables beginning with "foo" for columns with "bar" in it
SELECT *
FROM information_schema.COLUMNS
WHERE
COLUMN_NAME LIKE '%bar%' AND
TABLE_NAME LIKE 'foo%';

Find Columns Used in Stored Procedures or Triggers

Being able to quickly track down all the places a given column can be updated in your database is extremely useful when it comes time to hunt down an illusive bug. Here’s how you can quickly narrow your search to only the stored procedures, functions, and triggers that actually mention your column somewhere in their code:

-- Search all stored procedures and functions for "foobar_column"
SELECT *
FROM information_schema.ROUTINES
WHERE
ROUTINE_DEFINITION LIKE '%foobar_column%';

-- Search all triggers for "foobar_column"
SELECT *
FROM information_schema.TRIGGERS
WHERE
ACTION_STATEMENT LIKE '%foobar_column%';

There’s a whole bunch of other tables to play with in the information_schema table, so dig in… but remember look, don’t touch.