MySQL

MySQL SHOW TABLES Statement: What it is, How It Works, What It Means for You

intro

All database administrators have a special interest in the data inside their database. Data resides in tables — those tables have a distinct structure that directly impacts your database operations. The MySQL SHOW TABLES is a special statement suitable for everyone who wants to observe the structure of their tables and in this blog, we’re walking you through it.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
mariadb MARIADB
THE MARIADB DATABASE
MySQL logo MySQL
THE MYSQL DATABASE

If you’ve ever worked with MySQL, you already know that it’s not an easy beast to tame. MySQL consists of a file system, storage engines, query optimizer and parser, caches, buffers, and many other things that need to be accounted for to achieve maximum database performance. One thing that needs to be accounted for is the tables inside your database instance. This is where the MySQL SHOW TABLES statement comes in!

The Task of SHOW TABLES

Users of MySQL will know that the tables existing in any database can be observed in a couple of different ways:

  1. Use SQL clients like DbVisualizer or “default” options like phpMyAdmin
  2. Use SQL queries like the MySQL SHOW TABLES statement to explore the database structure

For users of DbVisualizer, tables in a database instance look like so:

Tables in a database instance in DbVisualizer
Tables in a database instance in DbVisualizer

Users of phpMyAdmin will see a list of databases and tables inside of them in the following way (the “bowling” icon near the demo database denotes that multiple databases start with the same name):

Observing databases and tables in phpMyAdmin
Observing databases and tables in phpMyAdmin

With that being said, always keep in mind that there is another way to observe what tables exist in a given database — and that’s by using the MySQL SHOW TABLES query after heading over to your database of choice:

How does SHOW TABLES work internally?
How does SHOW TABLES work internally?

Attentive readers will quickly notice that TABLES only works if we’re located in a database, and that can be explained simply: MySQL can only list the available tables in a database if we provide it with a database. That means that the same query wouldn’t work out of thin air:

MySQL SHOW TABLES Erroring Out
MySQL SHOW TABLES Erroring Out

Showing tables in MySQL via SHOW TABLES is also very similar to the SQL query that provides us with a list of databases: in that case, we need to replace TABLES with DATABASES and we’d see a list of available databases as well (see examples above.)

Similarly, follow our guide on how to list databases in PostgreSQL.

MySQL SHOW TABLES: Options and Syntax

In the MySQL realm, the MySQL SHOW TABLES query is a relatively simple one — while it wouldn’t work if we don’t find ourselves in a database, SHOW TABLES would work perfectly if we find ourselves in a database. It can even be used with wildcards with the SQL LIKE operator to find specific tables:

SHOW TABLES with a wildcard
SHOW TABLES with a wildcard

Keep in mind that here, some ordinary tasks of wildcards don’t apply — a % sign would return all tables in a database (its function would be the same as that of SHOW TABLES without any arguments) and act as a wildcard if you need to find tables starting with a certain name, but it wouldn’t slow the query down.

Other types of wildcards like the _ sign denoting a missing character would be applicable as well, but keep in mind that you wouldn’t be able to use the AS clause as with other statements when SHOW TABLES is being used:

MySQL SHOW TABLES in action
MySQL SHOW TABLES in action

Almost the same applies to SHOW DATABASES too, just that SHOW DATABASES would only work if we are not already in a database: this query lists databases instead of tables within them.

That’s about it — the MySQL SHOW TABLES statement isn’t a panacea. It helps you obtain a list of tables in a database but doesn’t go further than that. To go further than that, you would need to employ SQL clients like DbVisualizer.

The Task of DbVisualizer

Unlike SHOW TABLES, the top-rated data visualization tool — DbVisualizer — can be used not only to observe the databases in your database server or the tables within them, but also act as an SQL client and data Jedi.

To begin with, the SQL client is able to format and auto-complete SQL queries for you. Click Ctrl + Shift + F or mark your query and click Format Buffer once you right-click:

Formatting queries within DbVisualizer
Formatting queries within DbVisualizer

The query now looks much better, doesn’t it?

DbVisualizer and formatted queries
DbVisualizer and formatted queries

DbVisualizer also enables you to turn on auto-commit capabilities if you hover over the Transaction part within the SQL Commander (find the SQL Commander at the top.)

The auto-commit capability in DbVisualizer
The auto-commit capability in DbVisualizer

With that said, one can also commit (Ctrl+Alt+C) or rollback (Ctrl+Alt+R) transactions at the click of a couple of keys too. Isn’t that convenient?

DbVisualizer has many other awesome features — its extensive support for the most popular data sources means that no matter what kind of database you use — PostgreSQL, MongoDB, MySQL, SQLite, it supports them all. DbVisualizer also supports data warehouses like ClickHouse — isn’t that amazing?

Give DbVisualizer a try today: you won’t regret it. After you’ve familiarized yourself with the infrastructure of DbVisualizer, come back to MySQL, inspect your databases and tables with the MySQL SHOW TABLES and SHOW DATABASES SQL queries, and make sound decisions for your queries for everything to be alright.

Summary

The MySQL SHOW TABLES query is a very useful companion when you find yourself inspecting the structure of your tables — this SQL query is used when we log into our database instances and navigate to our database of choice to know what tables it contains. It does come with a couple of additions — namely the fact that one can use the LIKE clause to use wildcards to find necessary tables.

Regardless if you’re a power user of MySQL or just a novice, the SHOW TABLES statement will be very useful both now and in the future. Don’t forget to optimize your databases as a whole, and make good use of it!

FAQ

How to show the list of tables in MySQL?

The MySQL SHOW TABLES query enables us to obtain a list of tables existing in a database when running MySQL on any storage engine. This is one of the many ways for showing tables in MySQL, which also includes using a database client like DbVisualizer or phpMyAdmin.

Why should I use DbVisualizer?

Consider using DbVisualizer if you’re looking for a tool that makes your database management simple: with support for tens of the most popular data sources from SQLite to ClickHouse, DbVisualizer can help you unleash the power of your database instances in an instant.

What else should I know when working with MySQL?

Once you know how to display the databases and the tables inside of them, always keep in mind that your databases will only be performant if they’re properly optimized for performance, availability, and security. Look into the settings existing in my.cnf, read the documentation, and optimize as necessary.

Dbvis download link img
About the author
LukasVileikisPhoto
Lukas Vileikis
Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

Counter in MySQL: Counting Rows with Ease

author Lukas Vileikis tags MySQL SQL 8 min 2024-10-03
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

The MySQL RENAME COLUMN Statement: How, What, and Why

author Lukas Vileikis tags MySQL SQL 6 min 2024-09-26
title

SQL FETCH: Retrieving Data In Database Cursors

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-09-24
title

MySQL Binary Logs – Walkthrough

author Lukas Vileikis tags Binary Log MySQL 6 min 2024-09-18
title

Distributing Data in a Database: A Guide to Database Sharding

author Lukas Vileikis tags MySQL OPTIMIZATION SQL 7 min 2024-09-09
title

MariaDB Docker: Server Setup Guide

author Lukas Vileikis tags MySQL SQL 5 min 2024-08-29
title

Everything You Need to Know About MySQL Full-Text Search

author Lukas Vileikis tags Full text search MySQL 6 min 2024-08-28
title

MySQL Error #1045 Explained: Everything You Need to Know

author Lukas Vileikis tags MySQL SQL 5 min 2024-08-26
title

Index Creation in SQL

author Lukas Vileikis tags MySQL POSTGRESQL SQL SERVER 7 min 2024-08-15

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗