Search MySQL field names

I had occasion recently to map out a number of databases, some of which contained quite a few tables. I was not intimately familiar with most of them, and since they didn’t have foreign keys established in most cases, I had to examine each one to see where the connections were. When I came across two tables I thought could be connected, sometimes there was no direct shared key, but they instead went through a mapping table (or two or three).

Anyway, the issue was finding everywhere some given field name occurred. At the time, I didn’t know of the query to search for a string in the field names in all columns of all tables in a given DB in SQL. So I created a little Python script to do just that. It asks you for your desired searchterm, DB, user, password, and then prints out any matching columns per table. It has decent error handling, and plenty of logging available for debugging in case needed. Here’s some example output:

It originally was simply querying for all tables, and then explaining each table, and parsing that output. Today I came across the command to do what I originally wanted, using the ever-awesome information_schema database:

1
2
3
4

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE '%MY_TERM%'
AND TABLE_SCHEMA = 'MY_DB';

However, I still like the other additions my script has, such as querying for the needed information, error handling, configurable output, etc. So I simply changed it around to use this new query, which just made it faster!

You can get the latest version of the script here on GitHub. I intend to add a few features soon, like allowing multiple searches, so you only have to enter creds once, and can keep searching for terms until you are done.

I also learned a few handy things while writing this script as well:

getpass for getting passwords and not echoing them to the screen, for better security
How to handle wildcard searches in the mysqldb module. This was pretty obvious, I just hadn’t done it before:
1
2
3
4
5
6
mysql_query = """
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE '%s'
and TABLE_SCHEMA = '%s';
""" % ('%' + searchterm + '%', DB)