MySQL SHOW TABLES: List Tables In a MySQL Database

MySQL SHOW TABLES: List Tables in a MySQL Database

The SHOW TABLES statement in MySQL is used to display all tables in the currently selected database. This command is useful for verifying the existence of tables, debugging, or exploring a database structure.

Syntax

SHOW TABLES;

This command lists all tables in the currently selected database.

Example 1: Show All Tables in a Database

Before listing tables, ensure you have selected a database using the USE statement.

USE my_database;
SHOW TABLES;

Output Example:

+--------------------+
| Tables_in_my_database |
+--------------------+
| customers         |
| orders           |
| products         |
| employees        |
+--------------------+

Example 2: Show Tables Matching a Pattern

To filter tables by a specific pattern, use the LIKE clause:

SHOW TABLES LIKE 'emp%';

This command lists all tables that start with "emp".

Output Example:

+----------------+
| Tables_in_db  |
+----------------+
| employees     |
| emp_salaries  |
+----------------+

Example 3: Show Tables with a WHERE Clause

Alternatively, you can use WHERE with the Table_type column when used with INFORMATION_SCHEMA:

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'my_database';

This query fetches table names specifically from the my_database schema.

Example 4: Show Temporary Tables

If you want to list only temporary tables, use:

SHOW TABLES WHERE Tables_in_my_database LIKE 'tmp_%';

This helps in identifying temporary tables that follow a specific naming convention.

Example 5: Show Tables from Another Database

Even if you're in a different database, you can check tables from another database using:

SHOW TABLES FROM another_database;

Permissions Required

To run SHOW TABLES, you need the SELECT privilege on the database. Without sufficient privileges, you may not see all tables.

Conclusion

• SHOW TABLES lists all tables in the currently selected database.
• Use LIKE or WHERE to filter table names.
• You need appropriate permissions to view tables.

Would you like additional details or examples on specific database queries? 🚀