How can I find all the tables in MySQL with specific column names in them?

I have 2-3 different column names that I want to look up in the entire database and list out all tables which have those columns. Is there any easy script?

SELECT DISTINCT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE 'employee%'
AND TABLE_SCHEMA='YourDatabase'

More simply done in one line of SQL:

SELECT * FROM information_schema.columns WHERE column_name = 'column_name';

For those searching for the inverse of this, i.e. looking for tables that do not contain a certain column name, here is the query...

SELECT DISTINCT TABLE_NAME FROM information_schema.columns WHERE
TABLE_SCHEMA = 'your_db_name' AND TABLE_NAME NOT IN (SELECT DISTINCT
TABLE_NAME FROM information_schema.columns WHERE column_name =
'column_name' AND TABLE_SCHEMA = 'your_db_name');

This came in really handy when we began to slowly implement use of InnoDB's special `ai_col` column and needed to figure out which of our 200 tables had yet to be upgraded.

To get all tables with columns `columnA` or `ColumnB` in the database `YourDatabase`:

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('columnA','ColumnB')
AND TABLE_SCHEMA='YourDatabase';

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%wild%';

SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%city_id%' AND TABLE_SCHEMA='database'

The problem with information_schema is that it can be terribly slow. It is faster to use the SHOW commands.

After you select the database you first send the query SHOW TABLES. And then you do SHOW COLUMNS for each of the tables.

In PHP that would look something like

<pre>

$res = mysqli_query("SHOW TABLES");
while($row = mysqli_fetch_array($res))
{ $rs2 = mysqli_query("SHOW COLUMNS FROM ".$row[0]);
while($rw2 = mysqli_fetch_array($rs2))
{ if($rw2[0] == $target)
....
}
}

</pre>

If you want to "get all tables only", then use this query:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like '%'
and TABLE_SCHEMA = 'tresbu_lk'

If you want "to get all tables with columns", then use this query:

SELECT DISTINCT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE '%'
AND TABLE_SCHEMA='tresbu_lk'

Use this one line query. Replace *desired_column_name* by your column name.

SELECT TABLE_NAME FROM information_schema.columns WHERE column_name = 'desired_column_name';