There are some really nuisance times when you just forget where a column in a database is, or possibly due to architectural reasons you know what an unlinked foreign key is called but don’t know where it is. Well, you can find out using a rather simple SQL statement in MySQL (since version 5.0). See here:

SELECT * FROM `information_schema`.`COLUMNS` WHERE TABLE_SCHEMA = "databasename" and COLUMN_NAME = "columnname";

Yes, there is a hidden database schema called “information_schema“, which contains all the metadata from all of your database schemas on that mysql server. Within information_schema is a table called COLUMNS which has details about the columns of each table. By matching the TABLE_SCHEMA variable with your DBN and the COLUMN_NAME with the name that you think it is you’ll find the tables that you’re looking for. This could also quite easily be a LIKE comparator. Yay for metadata analysis!

To give you an example. In Drupal, each node (essentially an object of any type to put it in Object-Oriented terminology) has an nid (roughly equivalent to a universal Object ID to put it in OO terms again). This is called nid in all tables, but there are no explicit foreign key declarations. So we can do this to find all occurrences of the “nid” column, like this:

SELECT * FROM `information_schema`.`COLUMNS` WHERE TABLE_SCHEMA = "drupal" and COLUMN_NAME = "nid";