JIYIK CN >

This article explains how to find all tables with a specific column name in MySQL, but before that, you must understand the schema (database) in which these tables are stored. Let's start with that.

Schemas in MySQL

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

Create the schema/database:

mysql> CREATE SCHEMA schema_name;

or

mysql> CREATE DATABASE schema_name;

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

Using Schema:

mysql> USE schema_name;

However, if you want to drop a specific schema or database, then you have to run the following query.

Delete the schema:

mysql> DROP SCHEMA schema_name;

Create tables in MySQL Schema and list them

All data in a database is stored in tables, which are called database objects.

Data is logically arranged in a table using a spreadsheet-like layout of rows and columns. Each column represents a record field, and each row represents a different record.

Create the table:

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

In MySQL, table and column names are not case sensitive. This means that if you create a table named MY_TABLE and my_table, both are identical.

If you do not need a table, you can remove it from the schema using the following query.

mysql> DROP TABLE table_name;

A schema/database can contain many tables. You can imagine a schema like the folders you create on your operating system.

Sometimes, it becomes overwhelming and you may forget about the tables you created in a schema and want to list all the tables in a particular schema.

In this case, the following query is very useful. First, make sure you have selected your schema using USE schema_name; ask.

Show all tables:

mysql> SHOW TABLES;

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

mysql> SHOW FULL TABLES;

In addition, if you want to get a list of tables in another schema without switching the current schema, MySQL provides this functionality; you can use the FORM or IN clause in your query:

mysql> SHOW TABLES FROM schema_name;

or

mysql> SHOW TABLES IN schema_name;

These are the basic queries that help you to get the basic details from the schema. However, you can also modify this query more which will fetch the exact results you want and you can like it more.

List tables with a specific column name in MySQL

If you want to find tables from any schema that contain some specific columns, you are looking for that.

With the help of MySQL view called INFORMATION_SCHEMA.COLUMNS, we can get and view all columns or specific columns from all tables.

By default, it will get columns from every TABLE in the schema. However, you can filter these columns using 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 not sure about the exact names of the columns you want to get, there is an alternative option: regular expressions.

MySQL supports regular expressions from one of many regular expression combinations. So, for example, you can use the WHERE and LIKE clauses to find the columns you want with just one word.

Subsequently, using '%column_word%'the regular expression, '%column_word%'any value containing the mentioned word at any position will be found.

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