Find all tables with specific column names in MySQL

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:-

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!!!