Save Big on Cyber Monday! Up to 40% Off
ends in   {{days}}
Days
{{timeFormat.hours}}
:
{{timeFormat.minutes}}
:
{{timeFormat.seconds}}

How to Show/List Tables in a MySQL Database

When working with MySQL or MariaDB, database developers, designers, and administrators often need to manage and modify database structures while ensuring security through proper access controls and safe structural changes.

A common task is quickly listing all tables in a database to verify their existence, naming, and user permissions. In MySQL, the fastest way to do this is with the SHOW TABLES command.

This article explains its syntax and demonstrates its use from both the command line and dbForge Studio for MySQL.

Read on to find out about:

How to show a list of all tables in a MySQL database

Let us begin with the most basic syntax. To show tables in MySQL databases, we run the following query: 

SHOW TABLES;

You can run this statement from MySQL Command Line Client, MySQL Shell, or any GUI tool that supports SQL and see all tables in a MySQL database. This approach is similar to the one used to show tables in SQL Server.

MySQL returns the results in a table with one column — Tables_in_DatabaseName. The names of the tables are listed in alphabetical order. The summary line indicates the total number of tables in the database.

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:
USE databasename;
  1. Run the SHOW TABLES command to see all the tables in the selected database.

Show tables with the LIKE pattern

Your server may store large databases with many thousands of tables. What if you need to know how to show a table in MySQL — a specific table? You can use the LIKE expression along with the MySQL SHOW TABLES command.

SHOW TABLES [LIKE 'pattern']

This way, you will only see a filtered list of tables instead of looking through all of them. Suppose, we want the statement to return only the names of those databases that begin with the letter 'a': 

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:

SHOW FULL TABLES;

MySQL lists tables in a database along with their types. The output is a table with two columns: Tables_in_DatabaseName and Table_Type. The tables are ordered in alphabetical order. The bottom line defines their total number in the database.

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 user -p command. Enter the password and execute the SHOW TABLES; command.

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

mysql -u username -p -e "use databasename;
show tables;"

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

mysqlshow -u user -p

SHOW TABLES vs INFORMATION_SCHEMA queries

Another way to show tables in MySQL is by querying the information_schema.tables view within the MySQL instance. A basic query looks like this:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'db_name';

Have a look at the following comparison table that highlights the most essential features of the SHOW TABLES command vs. querying the information_schema.tables view.

SHOW TABLES INFORMATION_SCHEMA queries
Purpose Lists all tables in the currently selected database Provides detailed metadata about objects via standard SQL queries
SQL standard compliance MySQL-specific ANSI SQL standard
Database selection Works on the currently selected database only Can query metadata from any database
Filtering Limited (LIKE or WHERE clauses for table names) Advanced (filtering on multiple columns)
Metadata details Only lists table names Can retrieve table type, creation time, engine, row count, etc.
Cross-database queries No Can query metadata for multiple databases in one query
Performance Fast for simple table listing operations Slower for large metadata queries but more informative
Use case Quick check of existing tables in a database Detailed analysis/reporting about tables and other database objects

Common errors and troubleshooting tips

Some problems occur frequently when running SHOW TABLES:

  • Access denied or insufficient privileges
  • No database selected
  • Wrong database in use
  • Corrupted or inaccessible database
  • Empty result returned

Tips to resolve these issues:

  • Check the command syntax to ensure accuracy
  • Verify the database name and connection
  • Consult your DBA to request the necessary privileges
  • Use information_schema.tables to check if the database contains any tables

Permissions required to use SHOW TABLES

The SHOW TABLES command retrieves the list of table names, which are part of the database metadata. To access it, MySQL requires the SELECT privilege on the database. The alternative is the SHOW privilege granted at the database or table level.

To grant access to all tables in a database, use an asterisk (*): 

GRANT SELECT, SHOW VIEW ON world.* TO 'testuser'@'localhost';

To grant access to a specific table, define its name:

GRANT SELECT, SHOW VIEW ON sakila.film TO 'testuser'@'localhost';

Note: Grant access to each table individually using separate GRANT statements.

The user will only be able to access the specified tables.

Use cases for SHOW TABLES

The SHOW TABLES command in MySQL provides a quick way to inspect and troubleshoot databases. It helps developers and database administrators (DBAs) handle several common tasks:

Use cases for SHOW TABLES

Database exploration and onboarding

Developers or DBAs new to a database often need a fast overview of available tables. SHOW TABLES gives them an immediate list without complex queries.

Scripting and automation

Scripts and maintenance jobs often need to verify table existence before running tasks such as backups, data inserts, or schema updates. SHOW TABLES helps automate this check.

Development and testing

During development or migration, it's important to verify that certain tables were created, dropped, or modified. Use SHOW TABLES in development or staging environments for that.

Privilege and access verification

You can use SHOW TABLES to determine if a user has SELECT privileges on a database or specific database objects. If the output doesn't include tables, it may indicate restricted access.

Troubleshooting and debugging

When a query fails due to missing or misnamed tables, checking whether the table exists can resolve the issue. SHOW TABLES simplifies this by listing all tables in the database.

Monitoring and health checks

Routine database monitoring includes checking for the presence and correct naming of critical tables. SHOW TABLES allows teams to quickly confirm that all required tables exist.

Display all tables in MySQL database with dbForge Studio for MySQL

Previously, we demonstrated how to list tables in a MySQL database using specific SQL queries, both via the command-line utility and in dbForge Studio for MySQL.

The Studio also offers a more visual approach, allowing you to view tables, views, triggers, functions, and other database objects without writing any code.

  • 1. Select the desired database in the Database Explorer pane.
  • 2. Expand the database node.
  • 3. Expand the Tables section to see a complete list of all tables in the database.

Conclusion

This article explained how to show tables in MySQL databases using different options and tools. Among them, dbForge Studio for MySQL stands out as one of the best MySQL GUI tools on the market today. It offers 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!

dbForge Edge

dbForge Studio for MySQL

The best MySQL GUI tool for effective DB development

Download 30-day free trial

Frequently Asked Questions

Can I use a WHERE or LIKE clause with SHOW TABLES in MySQL?

Yes, you can filter the output of SHOW TABLES using the LIKE or WHERE clause. These filters help narrow down results in large databases when you make MySQL show tables in a database that you need.

Use LIKE for pattern matching—for example, SHOW TABLES LIKE 'user%'. Use WHERE to search for a specific table—for example, SHOW TABLES WHERE Tables_in_dbname = 'users' (replace dbname with your database name).

Note: Although you can use the WHERE clause with SHOW TABLES, it's more commonly used in queries against information_schema.tables for complex filtering. When using SHOW TABLES to find a specific table, the LIKE clause is typically the simplest and most effective option.

Why is SHOW TABLES returning an empty result set?

SHOW TABLES returns an empty result if the database has no tables, or if you lack SELECT privileges on the database or its tables. It can also happen if you're not connected to any database or if you're querying the wrong one.

How do I list tables from a different database in MySQL?

To list tables from another database, use SHOW TABLES FROM database_name;. Alternatively, switch to that database with USE database_name; and then run SHOW TABLES.

How do I check if a specific table exists using SHOW TABLES?

Use SHOW TABLES LIKE 'table_name'; to check if a table exists. If the result includes the table name, it exists in the current database. If it doesn't appear, the table may not exist, or you may lack the required SELECT privileges. Make sure you're connected to the correct database.

What permissions are needed to execute SHOW TABLES in MySQL?

You need at least the SELECT privilege on the database to run SHOW TABLES. Without it, the command returns an empty result, even if tables exist.

How do I list only base tables and exclude views in MySQL?

Query the information_schema.tables view and filter by the TABLE_TYPE column: 

SELECT
  TABLE_NAME
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'your_db_name'
AND TABLE_TYPE = 'BASE TABLE';

This returns only base tables and excludes views.

Does dbForge Studio for MySQL have a graphical way to list all tables in a database?

Yes, you can use the Database Explorer pane on the left. Select your database and expand the Tables node to view all tables. Expand individual table nodes to see column details.

Is there a way to view both tables and views visually in dbForge Studio?

Yes. In the Database Explorer pane, navigate to your database and expand it. You'll see Tables and Views nodes. Expand each to view the list of all tables and all views.

Can dbForge AI Assistant help show MySQL tables from a database?

Yes. The primary purpose of dbForge AI Assistant is to help you create customized SQL queries for your specific needs. It can convert plain language into SQL, allowing you to simply describe what you want, such as viewing all tables in a database or applying specific conditions. Then the AI Assistant will generate the corresponding SQL query for you.

Availability in the editions of dbForge Studio for MySQL

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