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.
Leave a comment