How to Show/List Tables in MySQL Database

If you are a MySQL database administrator or developer it is crucial to keep a close eye on all the processes and changes in all the database objects. Among the other frequently used commands, SHOW TABLES allows you to get all the tables at your fingertips.

In this article, we will talk about how to show the tables in a MySQL database: we will dive into MySQL SHOW TABLE syntax and describe how to use it from the command line and a GUI for MySQL. Read on to find out about:

MySQL SHOW TABLES syntax
MySQL SHOW TABLES examples
How to use SHOW TABLES with the LIKE pattern
SHOW FULL TABLES in MySQL
How to show tables in the command line
How to get and manage tables list with MySQL GUI tool

How to show all-tables list in a MySQL database

Let us begin with the most basic syntax. The following query will show all tables in a MySQL database:

SHOW TABLES;

To see all the tables, you can run this statement from MySQL Command Line Client, MySQL Shell, as well as from any GUI tool that supports SQL—for example, dbForge Studio for MySQL.

MySQL returns the results in a table with one column—Tables_in_DatabaseName. The tables are ordered in alphabetical order. The summary line tells you how many rows (or tables) there are in the database in question.

MySQL SHOW TABLES command example

To use the SHOW TABLES command, you need to log on to the MySQL server first.

On opening the MySQL Command Line Client, enter your password.
Select the specific database.
Run the SHOW TABLES command to see all the tables in the database that has been selected.

Show tables with the LIKE pattern

There might be quite large databases stored on your server. In such cases, you can use the LIKE expression along with the MySQL SHOW TABLES command. This way, you will only see a filtered list of tables instead of looking through all of them:

SHOW TABLES [LIKE 'pattern']

Suppose, we want the statement to return only the names of those databases that begin with the letter 'a'. The query will look as follows:

SHOW TABLES LIKE 'a%';

SHOW FULL TABLES in MySQL

In case you need more information about the tables in your database, use the following query to list all the tables along with their types:

SHOW FULL TABLES;

MySQL returns the results in a table with two columns: Tables_in_DatabaseName and Table_Type. The tables are ordered in alphabetical order. Also, the last line of the prompt displays how many rows there are in total.

List all tables using MySQL command line

You can also list all databases using the command line. There are three common methods for this.

1. Open the Command Prompt and navigate to the bin folder of your MySQL Server installation directory. Then connect to the server using the mysql -u root -p command. Enter the password and execute the SHOW TABLES; command we have discussed above.

2. Open the Command Prompt and navigate to the bin folder of your MySQL Server installation directory. Then run the following query:

mysql -u user -p -e "show tables;"

3. Open the Command Prompt and navigate to the bin folder of your MySQL Server installation directory. Then run the query:

mysqlshow -u user -p

Show tables in MySQL database command line

Display all tables in MySQL database with dbForge Studio for MySQL

To find out which database is currently selected, use the following query:

SELECT DATABASE();

In dbForge Studio, the selected database will be shown in the menu ribbon. You can easily switch between the databases using the dropdown.

To find out what tables the selected database contains, execute:

SHOW TABLES;

In dbForge Studio, the database tables can be displayed by expanding the nodes in Database Explorer.

dbForge Studio for MySQL - get all tables in MySQL database

Conclusion

dbForge Studio for MySQL is one of the best MySQL GUI tools on the market today. It encompasses dozens of useful features that allow you to perform any database-related tasks: show all databases in MySQL, copy a MySQL database, back up a table in MySQL, etc. Moreover, dbForge Studio offers a convenient MySQL administrator tool. However, a picture paints a thousand words, so we welcome you to download a FREE 30-day trial!