Estimated reading time: 2 minutes
The MySQL SHOW TABLES statement lists all tables in a database. This guide explains the syntax, options, and practical uses for SHOW TABLES, including how to filter results by table type and specific patterns.
Syntax
SHOW TABLES [FROM database_name] [LIKE 'pattern' | WHERE expression]; Parameters
- FROM database_name: Specifies the database to view tables from. If omitted, MySQL will show tables from the currently selected database.
- LIKE ‘pattern’: Filters the results to show only tables matching a specific pattern. Wildcards (e.g.,
%for multiple characters) are allowed. - WHERE expression: Uses an SQL expression to filter the results based on criteria like table names, types, and more.
Basic Usage
To view all tables in the currently selected database, simply use:
SHOW TABLES;This command lists every table in the active database, including tables, views, and other table-like objects.
Example
Suppose you are connected to a database named company_db. To see all tables in company_db, execute:
SHOW TABLES; This will return a list of all tables, as shown below:
mysql> SHOW TABLES;
+---------------------+
| Tables_in_company_db|
+---------------------+
| customers |
| orders |
| products |
+---------------------+This command lists all tables in the current database (company_db in this case).
Practical Examples
List Tables with a Specific Prefix
To view only tables with a specific prefix (e.g., all tables related to “employee”):
mysql> SHOW TABLES LIKE 'emp%';
+----------------------+
| Tables_in_company_db |
+----------------------+
| employees |
| employee_salaries |
+----------------------+View Tables in Multiple Databases
Use SHOW TABLES with FROM to view tables across different databases without changing the active database:
SHOW TABLES FROM database1;
SHOW TABLES FROM database2; View tables from specific database
mysql> SHOW TABLES FROM sales_db;
+-------------------+
| Tables_in_sales_db|
+-------------------+
| customers |
| orders |
| products |
+-------------------+ Here, SHOW TABLES FROM sales_db lists tables specifically from the sales_db database.
Using SHOW FULL TABLES; to View Table Types
mysql> SHOW FULL TABLES;
+----------------------+------------+
| Tables_in_company_db | Table_type |
+----------------------+------------+
| employees | BASE TABLE |
| departments | BASE TABLE |
| employee_view | VIEW |
+----------------------+------------+The SHOW FULL TABLES; command adds a Table_type column, showing if each item is a BASE TABLE or a VIEW.
SHOW FULL TABLES with Filtering by Table Type
mysql> SHOW FULL TABLES WHERE Table_type = 'VIEW';
+----------------------+------------+
| Tables_in_company_db | Table_type |
+----------------------+------------+
| employee_view | VIEW |
| department_view | VIEW |
+----------------------+------------+Using WHERE Table_type = 'VIEW' filters the results to show only views in the database.
Filtering with WHERE Clause for Partial Matches
mysql> SHOW TABLES WHERE Tables_in_company_db LIKE '%dept%';
+----------------------+
| Tables_in_company_db |
+----------------------+
| departments |
| department_salaries |
+----------------------+This command lists tables that contain “dept” in their names.
