Finding out which tables contain certain columns in database

08/01/2016 - MYSQL

Say you want to know which tables in database has column named name or postcode. Example below does what you want.


Query


All the tables below contain name or postcode columns in database called my_database.


mysql> SELECT
    -> DISTINCT `table_name`
    -> FROM
    -> INFORMATION_SCHEMA.COLUMNS
    -> WHERE
    -> COLUMN_NAME IN ('name', 'postcode') AND
    -> TABLE_SCHEMA = 'my_database';
+------------+
| table_name |
+------------+
| address    |
| child      |
| country    |
| league     |
| parent     |
| player     |
| schools    |
| student    |
| team       |
| user       |
+------------+
10 rows in set (0.00 sec)