How to Find Tables with Column Name in MySQL

MySQL is a popular and easy to use database system deployed by many organizations and websites. Sometimes while using MySQL, you may need to find tables with one or more specific column names in MySQL. In this article, we will learn how to find tables with column name in MySQL.

How to Find Tables with Column Name in MySQL

The mapping of table name to column names is present in information_schema.columns table. Information_schema is created at the time of MySQL installation without user input. It is automatically updated as and when users create/modify their databases.

You can easily get a list of all tables that contain column column1 using the following query.

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

If you want to get a list of all tables that contains columns, say, column1 and column2, in a specific database, use the following query. Replace database_name with your database’s name.

SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('column1','column2')
        AND TABLE_SCHEMA='database_name';

Here is an alternative query that lists all tables with column names containing the string ‘column’ in them. We have used wildcard characters to demonstrate its use here.

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

The above query will list all tables across all databases on your server. If you want to get a list of tables present in a specific database, then you need to add ‘where TABLE_SCHEMA= ‘ clause.

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%column%'
AND TABLE_SCHEMA='database_name';

In this article, we have learnt how to find tables with column name.

Also read:

How to Find my.cnf file location in MySQL
How to Add Option to Select in jQuery
How to Use JavaScript Variables in jQuery Selectors
How to Select Element With Multiple Classes in jQuery
How to Show Last Queries Executed in PostgreSQL