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.
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:
For users of DbVisualizer, tables in a database instance look like so:
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):
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:
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:
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:
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:
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:
The query now looks much better, doesn’t it?
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.)
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.
