Find all tables with specific column name

Ever need to find out if or where a column exists within one of your tables? MSSQL and MySQL are remarkably very similar with at least one strange difference:

-- MYSQL
SELECT COLUMN_NAME, TABLE_NAME  
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('club','ClubID')
AND TABLE_SCHEMA = 'mydatabase';

If you want to limit the filter to just one database (assuming multiple databases), you’ll filter upon TABLE_SCHEMA within MySQL but TABLE_CATALOG within MSSQL. The rest is pleasingly the same information schema.

-- MSSQL
SELECT COLUMN_NAME, TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME IN ('club','ClubID')
AND TABLE_CATALOG = 'mydatabase';

Alter the WHERE statement to use a wildcard if you need to perform a LIKE search.

Earlier versions of MySQL do not have INFORMATION_SCHEMA. You’ll need to dump the table structure to a flat file and perform a text search.