Find all tables in a DB with column names that contain that substring? Say I have these three columns student_id, name, identify in three different tables in the same database. 

The query should search for column names that contain that substring. i.e. id should return tablesstudent_id,identify from those three tables^.

This only works if the strings are equal.

I apologise if this is a repost I couldn't find another question similar, and I've hardly ever worked with databases. Thanks
Login using the MySQL command-line client.

mysql&gt; USE Information_Schema;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql&gt; SHOW Tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
&lt;rest of tables snipped&gt;

mysql&gt; DESCRIBE Columns;
+--------------------------+---------------------+------+-----+---------+-------+
| Field                    | Type                | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG            | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA             | varchar(64)         | NO   |     |         |       |
| TABLE_NAME               | varchar(64)         | NO   |     |         |       |
| COLUMN_NAME              | varchar(64)         | NO   |     |         |       |
| ORDINAL_POSITION         | bigint(21) unsigned | NO   |     | 0       |       |
| COLUMN_DEFAULT           | longtext            | YES  |     | NULL    |       |
| IS_NULLABLE              | varchar(3)          | NO   |     |         |       |
| DATA_TYPE                | varchar(64)         | NO   |     |         |       |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_OCTET_LENGTH   | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_PRECISION        | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_SCALE            | bigint(21) unsigned | YES  |     | NULL    |       |
| DATETIME_PRECISION       | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_SET_NAME       | varchar(32)         | YES  |     | NULL    |       |
| COLLATION_NAME           | varchar(32)         | YES  |     | NULL    |       |
| COLUMN_TYPE              | longtext            | NO   |     | NULL    |       |
| COLUMN_KEY               | varchar(3)          | NO   |     |         |       |
| EXTRA                    | varchar(30)         | NO   |     |         |       |
| PRIVILEGES               | varchar(80)         | NO   |     |         |       |
| COLUMN_COMMENT           | varchar(1024)       | NO   |     |         |       |
+--------------------------+---------------------+------+-----+---------+-------+
20 rows in set (0.01 sec)


Your desired SQL is something like this:

mysql&gt; SELECT Table_Name, Column_Name FROM Columns WHERE Column_Name LIKE '%My_Search_String%';


My_Search_String could be 'student%' for student_id.
My_Search_String is case sensitive - and be careful, the underscore is a wild-card in SQL for a single character.

[EDIT]

I double checked about My_Search_String being case sensitive. It appears that metadata is searched in a case insensitive fashion. 

mysql&gt; CREATE TABLE xyz (x int, `xx` int, XX int);
ERROR 1060 (42S21): Duplicate column name 'XX'
mysql&gt; 


Now, if column_names were case sensitive, there should be no duplicate XX column. Just one to watch - it's the data in the fields (assuming VARCHARs) that is case sensitive.