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
  • SHOW FULL TABLES in MySQL
  • How to show tables in the command line
  • How to use SHOW TABLES with the LIKE pattern
  • 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.

List of all tables in MySQL

MySQL SHOW TABLES command example

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

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

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 TABLES with name like

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 full set of tables in MySQL

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. 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!

Availability in the editions of dbForge Studio for MySQL

Feature

Enterprise
Professional
Standard
Express
Context-sensitive code completion
Yes
Yes
Yes
Automatic SQL syntax check
Yes
Yes
No
No
Security Manager to administer user accounts and privileges
Yes
Yes
Yes
Yes

dbForge Studio for MySQL

Cutting-edge MySQL IDE for database development and management