How to Find MySQL Tables Containing Specific Columns?

Finding Tables with Specific Columns in MySQL

If you're working with a large MySQL database and need to identify all tables that contain specific columns, there's an efficient method to accomplish this task.

To achieve this, you can utilize an SQL query that examines the INFORMATION_SCHEMA.COLUMNS table. This table provides information about all columns across all tables in the database.

Query to Find Tables

The following SQL query will retrieve all distinct table names from the database that have columns matching the specified column names:

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('<columnA>', '<columnB>')
AND TABLE_SCHEMA='<database_name>';

In this query:

• <columnA> and <columnB> represent the column names you want to search for.
• <database_name> is the name of the database you're searching within.

For example, if you want to find all tables that have either the "age" or "name" columns in the "users" database, you would use:

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('age', 'name')
AND TABLE_SCHEMA='users';

This query will return a list of all tables in the "users" database that contain either the "age" or "name" columns.

The above is the detailed content of How to Find MySQL Tables Containing Specific Columns?. For more information, please follow other related articles on the PHP Chinese website!