How To List MySQL Tables with “show tables” SQL Query? – POFTUT

How To List MySQL Tables with “show tables” SQL Query?


MySQL is a popular relational data that stores information in tables. While using tables we generally need to check and list existing tables. We can use different ways to list tables. In this tutorial, we will learn how to use show tables and Select table_name in order to list and print table names.

Connect MySQL Interactive Shell

We need to connect or run commands in MySQL to list table names. We will connect to the interactive MySQL server to run the given commands. We will use mysql command to connect local or remote MySQL interactive shell.

$ mysql -u root -p
Connect MySQL Interactive Shell
Connect MySQL Interactive Shell

Select MySQL Database

In order to run given SQL commands and queries, we need to specify the database we want to work on. We will use show databases and use DATABASENAME command. In the following example, we will select the database named mysql which is a default database provided by the MySQL database server.

> use mysql;
Select Database
Select Database

show tables SQL Query

Now we will run or show tables query in this example. We can also use SHOW TABLES which is the uppercase version and have the same effect as the lowercase version. Do not skip to add a semicolon to the end of the command.

> show tables;
show tables SQL Query
show tables SQL Query

From the output we can see that a lot of tables exist for the given database like columns_priv, db, engine_cost, event etc.

Show All Tables of MySQL Server with “Select table_name”  Query

We have another alternative to show tables query.  This will list all tables created in connected MySQL instance.

> SELECT table_name FROM information_schema.tables;
Show All Tables of MySQL Server with "Select table_name Query"
Show All Tables of MySQL Server with “Select table_name” Query

Show Tables Of Given Database with “Select table_name” Query

In this exmaple we will use Select table_name query but we will filter for given database by using where statement for table_name column. We will only list database named mysql in this example.

> SELECT table_name FROM information_schema.tables where table_schema='mysql';
Show Tables Of Given Database with "Select table_name" Query
Show Tables Of Given Database with “Select table_name” Query

Check MySQL Performance and Benchmark

MySQL table creation is first steps for database management. As a talented database administrator you should also check the MySQL performance and benchmark. The following post provides information about how to check and benchmark MySQL database server performance.

LEARN MORE  How To Center Table In HTML?

Leave a Comment