Get Table Names Using SELECT Statement in MySQL

In the MySQL database, data is stored in tabular form. As a database administrator, it is difficult to keep an eye on all the executing processes and modifications in all database objects. In MySQL, multiple commands are available for getting the table names from all databases to manage them efficiently.

This post will talk about:

How to Determine All Table Names Using “SELECT” Statement in MySQL?
How to Determine Table Names of Multiple Databases Using “SELECT” Statement in MySQL?

How to Determine All Table Names Using “SELECT” Statement in MySQL?

To display the MySQL databases table names, use the “SELECT” statement. For this purpose, check out the provided steps.

Step 1: Connect Terminal

At first, connect the Windows terminal with the MySQL server by running the below-listed command:

mysql -u root -p

As a result, you will be asked for the password:

Step 2: Display All Tables Names

Now, run the “SELECT” command along with the “information_schema.tables” to get the information about all existing tables and shows within a database:

SELECT Table_name as TablesName from information_schema.tables;

How to Determine Table Names of Multiple Databases Using “SELECT” Statement in MySQL?

If you want to display multiple databases table at once, the “UNION” operator can be used:

SELECT Table_name as TablesName from information_schema.tables where table_schema = 'mariadb' UNION SELECT Table_name as TablesName from information_schema.tables where table_schema = 'mynewdb';

Here:

“SELECT” command is utilized for selecting the record from the databases.
“Table_name” is the default variable.
“TablesName” is the name of the resultant table column.
“information_schema.tables” operator combines the results of two or multiple “SELECT” statements.
“where” clause extracts the records that meet the provided condition.
“table_schema” variable will be used to save the database name.
“mariadb” and “mynewdb” are our database names.

As you can see, the tables of the above-listed two databases are shown in the same table:

Another way to get the results of multiple “SELECT” queries in individual tables, the semicolon “;” can be used:

SELECT Table_name as TablesName from information_schema.tables where table_schema = 'mariadb'; SELECT Table_name as TablesName from information_schema.tables where table_schema = 'mynewdb';

That’s all! We have provided the different queries for getting table names using MySQL’s “SELECT” statement.

Conclusion

To get table names using the “SELECT” statement in MySQL, the “SELECT Table_name as TablesName FROM information_schema.tables;” command can be used. To get the results of the multiple “SELECT” statements at once, the “UNION” operator can be used. On the other hand, the “;” shows the table names of different databases individually. This post demonstrated the procedure of getting table names using the “SELECT” statement in MySQL.