EN
MySQL - find all tables with specific column names
0 points
In this article, we would like to show you how to find all tables with specific column names in MySQL
Quick solution:
xxxxxxxxxx
1
SELECT DISTINCT TABLE_NAME
2
FROM INFORMATION_SCHEMA.COLUMNS
3
WHERE COLUMN_NAME IN ('column1','column2', 'columnN')
4
AND TABLE_SCHEMA='your_database';
Let's say we have a database named dirask
that contains users
table and admins
table, both with the following columns:
id
name
surname
email
and locations
table with columns:
id
address
In this example, we will display all the tables from dirask
database that contain name
OR surname
columns.
Query:
xxxxxxxxxx
1
SELECT DISTINCT TABLE_NAME
2
FROM INFORMATION_SCHEMA.COLUMNS
3
WHERE COLUMN_NAME IN ('name','surname')
4
AND TABLE_SCHEMA='dirask';
Output:

In this example, we will display all the tables from dirask
database that contain id
column.
Query:
xxxxxxxxxx
1
SELECT DISTINCT TABLE_NAME
2
FROM INFORMATION_SCHEMA.COLUMNS
3
WHERE COLUMN_NAME IN ('id')
4
AND TABLE_SCHEMA='dirask';
Output:
