This article will look into getting all the MySQL tables with specific column names from a database schema. We will be illustrating the concept using various examples.
Let us get started by preparing the sample data. We will be creating three tables, sale_details, sale_person_designation, and sale_city.
#create the table sale_details CREATE TABLE sale_details ( id INT , sale_person_id VARCHAR(255) , sale_person_name VARCHAR(255), no_products_sold INT, sales_department VARCHAR(255) ); #create the table sale_person_designation CREATE TABLE sale_person_designation ( id INT , sale_person_name VARCHAR(255), designation VARCHAR(255) ); #create the table sale_city CREATE TABLE sale_city ( sale_person_id VARCHAR(255), sale_person_name VARCHAR(255) );
Action Output:-

The above image shows that all the three tables got created successfully. Let us now look into the examples.
Example1: Get the names of all the tables with columns, either sale_person_id or sale_person_name or both.
Observe the below query for the solution demanded by the above example.
SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('sale_person_id' , 'sale_person_name') AND TABLE_SCHEMA = database();
Output:-
Frequently Asked:
- Select count(*) with distinct
- MySQL SELECT WHERE LIKE
- Create trigger on delete
- MySQL trigger example before insert

Explanation:-
The above query is returning DISTINCT table names from INFORMATION_SCHEMA, which contain columns named sale_person_id and/or sale_person_name. The results are filtered by schema to get the tables only from your schema using database() function.
Example2: Get the table names and respective column names that have column starting with “sales_p”.
Observe the below query for the solution demanded by the above example.
SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'sale_p%';
Output:-

Explanation:-
The above query is returning TABLE_NAME and COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where column_name is starting from “sale_p” using wildcard “%” and LIKE operator.
Example3: Get names of all the tables which have column sale_person_name.
Observe the below query for the solution demanded by the above example.
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'sale_person_name';
Output:-

Explanation:-
The above query is returning TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where column_name is sale_person_name using the EQUAL operator in WHERE clause.
NOTE: that there will be no changes in the solution queries even if the tables contain data.
READ MORE:
- MySQL get data by string length
- Mysql update set multiple rows
- Mysql update column with value from another table
- MySQL Update with Inner Join
- MySQL Insert with Join
- MySQL: Insert in a loop
We hope this article helped you get the names of MySQL tables having particular columns. Good Luck!!!