MySQL SHOW TABLES

What is the SHOW TABLES Statement in MySQL and Why Do We Use It?

When you want to view the list of tables available inside a selected database, you type the SHOW TABLES statement. The SHOW TABLES statement is used to display the names of all tables available in the current database.

Syntax:

SHOW TABLES;

Example:

SHOW TABLES;

Output:

If the selected database contains tables, MySQL displays the list of available tables:

+----------------+
| Tables_in_DB   |
+----------------+
| Customers      |
| Orders         |
| Products       |
+----------------+

If no database is selected, MySQL displays the following error message:

ERROR 1046 (3D000): No database selected

How the SHOW TABLES Statement Works Internally

When you want to view tables in MySQL, you type the SHOW TABLES statement. After pressing the Enter key, the SHOW TABLES statement is sent to the MySQL server. The server checks the currently selected database and retrieves the list of available tables. After collecting the table information, MySQL displays the table names to the user.

Remember that the SHOW TABLES statement does not create, delete, move, or modify any table or data. It only displays the list of tables available in the current database.

Flowchart:

👤 User
   |
   ▼
⌨️ SHOW TABLES;
   |
   ▼
🖥️ MySQL Server
   |
   ▼
🔍 Check Selected Database
   |
 ┌───────┴───────┐
 ▼               ▼
✅ Found     ❌ Not Found
 |               |
 ▼               ▼
📁 Retrieve  ⚠️ Error
 Table List     Message
 |
 ▼
📄 Display Tables

Precautions While Using SHOW TABLES Statement:

1. Before using the SHOW TABLES statement, make sure that a database is selected.
2. You should know the correct database in which you want to view tables.
3. The SHOW TABLES statement only displays table names; it does not show table data.
4. Before executing queries, always check the selected database; otherwise, tables may not be displayed.

How to Use the SHOW TABLES Statement in MySQL: Step-by-Step Guide

If you use the SHOW TABLES statement in MySQL, you first need to select the database in which you want to view tables. After selecting the database, you use the SHOW TABLES statement to display the table list. The complete process is as follows:

Step 1: You connect to the MySQL server by using the MySQL login command.

mysql -u username -p

Step 2: You select the required database by using the USE statement.

USE database_name;

Step 3: You type the SHOW TABLES statement to view the list of tables.

SHOW TABLES;

Step 4: MySQL displays the list of tables available inside the selected database.

Note:

• The SHOW TABLES statement displays the list of tables available in the current database.
• The SHOW TABLES statement does not display the data stored inside tables.
• A database must be selected before using the SHOW TABLES statement.

Don't Confuse SHOW TABLES with the SHOW DATABASES Statement

Many beginners get confused between the SHOW TABLES statement and the SHOW DATABASES statement because both are used to display information. However, they perform different tasks. The SHOW DATABASES statement displays the list of available databases on the MySQL server, whereas the SHOW TABLES statement displays the list of tables available inside the selected database.

Therefore, when you want to view available databases, use the SHOW DATABASES statement. After selecting a database, use the SHOW TABLES statement to view the tables inside that database.