How to Find Tables in MySQL With Specific Column Names in Them

1. Schema in MySQL
2. Create Tables in MySQL Schema and List Them
3. List Tables With Specific Column Names in MySQL

This tutorial shows how to find all tables in MySQL with specific column names, but before that, it is essential to understand the schema (database) where these tables are stored. So let’s start with that.

Schema in MySQL

A database/schema specifies the logical restrictions, including table names, fields, data types, and the connections between these entities, that govern how data is arranged in a relational database.

Create Schema / Database:

mysql> CREATE SCHEMA schema_name;

OR

mysql> CREATE DATABASE schema_name;

These above queries are used to create schema/database, showing similar output. You must first select the schema you just created to use and run queries on that schema.

Use Schema:

mysql> USE schema_name;

However, if you want to drop a particular schema or database, you must run the following query.

Drop Schema:

mysql> DROP SCHEMA schema_name;

Create Tables in MySQL Schema and List Them

All data in a database is stored in tables, known as database objects.

Data is logically arranged in tables using a row-and-column layout akin to a spreadsheet. Each column denotes a record field, and each row indicates a distinct record.

Create a Table:

mysql> CREATE TABLE table_name(
            column_name datatype,
            column_name datatype,
            ...
        );

In MySQL, the table and column names are not case-sensitive. It means if you create a table named MY_TABLE and my_table, both are the same.

If you don’t need a table, you can use the following query to remove it from the schema.

mysql> DROP TABLE table_name;

A schema/database can hold many tables. You can imagine a schema as a folder you create on your Operating System.

Sometimes, it becomes overwhelming, and you can lose track of the tables you have created in your schema and want to list all the tables in the particular schema.

In that case following query is very useful. First, ensure you have selected your schema using the USE schema_name; query.

Show All the Tables:

mysql> SHOW TABLES;

If you also want to know, what are the types of tables in your schema, you can further modify the above query:

mysql> SHOW FULL TABLES;

Moreover, if you want the list of tables from another schema without switching your current schema. MySQL provides this feature; you can use the FORM or IN clause in the query:

mysql> SHOW TABLES FROM schema_name;

OR

mysql> SHOW TABLES IN schema_name;

These are the basic queries that help you to fetch basic details from a schema. However, you also modify this query more, which will fetch the exact result you want as well as you can get fancier with it.

List Tables With Specific Column Names in MySQL

If you want to find a table from any schema that contains some specific columns, you are looking.

With the help of a MySQL view named the INFORMATION_SCHEMA.COLUMNS, we can fetch and view all the columns or specific columns from all the tables.

By default, it will fetch columns from every TABLE in the schema. However, you can filter these columns with the SELECT, FROM, and WHERE clauses.

mysql> SELECT DISTINCT table_name
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE COLUMN_NAME IN ('column_1', 'column_2', ...)
           AND TABLE_SCHEMA = 'schema_name';

Or, a simpler version of the above query:

mysql> SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
       WHERE column_name = 'column_names';

If you are unsure of the column’s exact name you want to fetch, there is an alternative option: regular expressions.

MySQL supports regular expressions from one of the many combinations of regular expressions. So, for example, you can use the WHERE and LIKE clauses to find your desired column with just a single word.

Subsequently, using the '%column_word%' regular expression. The '%column_word%' finds any value with the mentioned word in any position.

mysql> SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
       WHERE COLUMN_NAME LIKE '%column_word%';