MySQL SHOW TABLES: A Detailed Guide

The MySQL SHOW tables command is very appropriately named since it allows you to list tables on a MySQL database.

 By the end of the post, you’ll have learned:

• The definition of the command
• How you can use it
• Why it’s useful in the first place, and
• An alternative way to list the tables in a database without using the show tables command

We’ll also cover how listing tables in MySQL compare with other RDBMSs (relational database management systems.)

Let’s get started.

Requirements

There are only two prerequisites if you want to follow along with the examples I’ll give. First, I expect some basic MySQL and SQL knowledge. Also, you should have access to a MySQL local installation.

A great way to do that is to use the CoderPad MySQL Sandbox to quickly and easily get started writing SQL as it’ll be your MySQL client for this article.

MySQL SHOW tables: what and why

In MySQL, the show tables command is what you use to list the tables in a database. That’s pretty much it when it comes to defining the command. But what are the uses cases for it? After all, when using a MySQL GUI (graphical user interface) client, you can easily display the tables inside a database, typically in a tree.

First, you won’t always have access to a GUI when interacting with MySQL. If the command line is all you have, show tables comes in handy. Also, keep in mind that this command is super flexible. When using it, you can leverage clauses such as WHERE and LIKE to further filter the output.

Finally, since this is a command, you can use it in scripts. So, there’s an opportunity to automate some tasks that simply wouldn’t be possible using a GUI.

How do I view tables in MySQL?

Let’s start with the most basic example. In the CoderPad MySQL sandbox, let’s create two tables:

CREATE TABLE students (

    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(250),

    email VARCHAR(100));

CREATE TABLE teachers (

    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(250),

    email VARCHAR(100));Code language: SQL (Structured Query Language) (sql)

Run show tables in the sandbox. You should see the tables being listed:

+--------------------+
| Tables_in_coderpad |
+--------------------+
| students           |
| teachers           |
+--------------------+Code language: SQL (Structured Query Language) (sql)

MySQL SHOW tables: some practical examples

You’ve just seen the most basic way you can use the show tables command. Now we’re ready to proceed to more interesting examples.

How to tell tables and views apart

Does the SHOW tables command also show views? Despite the name, yes. First, run the following command to create a view:

CREATE VIEW students_onlyname AS SELECT name FROM students;Code language: SQL (Structured Query Language) (sql)

Now, run show tables again, and you’ll see that the view is included. However, there’s no way to distinguish the view from the table. For that, you can use a different command:

SHOW full tables;Code language: SQL (Structured Query Language) (sql)

The command above returns the following output, which distinguishes between “base table” and “view”.

+--------------------+------------+
| Tables_in_coderpad | Table_type |
+--------------------+------------+
| students           | BASE TABLE |
| students_onlyname | VIEW        |
| teachers           | BASE TABLE |
+--------------------+------------+Code language: SQL (Structured Query Language) (sql)

Filtering the results with a LIKE clause

You often don’t want to get all of the existing tables, but a subset of them. You can use the LIKE clause to filter the results, in the same way, you’d do in a SELECT statement. For instance, running show tables LIKE 'stu%' returns the student table and the student_onlyname view, but not the teachers table.

Filtering the results with a WHERE clause

Similarly, you can filter your results using a WHERE clause. As you’ve already observed, when we ask MySQL to list its tables, the results themselves are presented in a tabular form, and the column which contains the table name is called “Tables_in_<database-name>”. So, in our case, “Tables_in_school”. You can use that name to filter that column using the WHERE clause. Examples:

SHOW tables WHERE length(Tables_in_school) <= 8;

SHOW tables WHERE Tables_in_school = 'students';Code language: SQL (Structured Query Language) (sql)

There’s nothing stopping you from using the SHOW full tables command as well, in which case you can also filter using the Table_type column:

SHOW full tables WHERE Table_type != 'VIEW'Code language: SQL (Structured Query Language) (sql)

Showing tables from a different database

Up until now, all of the examples you’ve seen show how to list tables from the database you’re connected to. It’s also possible to list tables from a different database, and it’s extremely easy to do so.

First, create a new database, running CREATE database myblog;. Then, select it by running use myblog;. Finally, you can list the tables from the first database by running show tables from school;.

Keep in mind that, for that to work, you must have the necessary privileges on the other database.

An additional way of listing tables in MySQL: The metadata tables

The SHOW tables command is non-standard and unique to MySQL, which means it’s not transferable to other database engines. However, there’s another you to fetch the list of tables in MySQL, which is transferable to (at least some) other RDBMSs: the INFORMATION_SCHEMA views.

In relational databases, the information schema (information_schema) is an ANSI-standard set of read-only views that provide information about all of the tables, views, columns, and procedures in a database.

– Wikipedia.

Here’s how to query the tables view to fetch the tables from the school database:

SELECT table_name, table_type 

FROM INFORMATION_SCHEMA.tables 

WHERE table_schema = 'school';Code language: SQL (Structured Query Language) (sql)

Keep in mind that the INFORMATION_SCHEMA views contain much more information than just the names of tables. There are views for columns, indexes, views, and all other artifacts inside a database.

For instance, the following query displays data about the columns of the students table:

SELECT table_name, column_name, is_nullable, data_type, character_maximum_length

FROM INFORMATION_SCHEMA.columns 

WHERE TABLE_SCHEMA = 'school' AND TABLE_NAME = 'students';Code language: SQL (Structured Query Language) (sql)

MySQL SHOW tables: add it to your toolkit

MySQL SHOW tables is sort of a niche command. As you’ve seen, it’s unique to MySQL, since other database engines offer different ways to achieve the same results. Thus, it doesn’t qualify as a SQL command everyone should know. However, that doesn’t mean it isn’t useful.

If you’re accessing a MySQL database through the command line, this command should be a crucial part of your toolkit. With it, you can quickly and easily see and even filter the tables inside a given database. Even if you use a GUI-based MySQL client, learning how to use the filtering options when listing tables can help you better navigate complex database schemas.

In the post, you’ve also learned that, despite being useful, the MySQL SHOW tables command is non-standard, which means you can use it on different database engines. A viable alternative is to use the INFORMATION_SCHEMA views: they’re an ANSI standard, implemented by several database vendors.

Also, feel free to explore the CoderPad blog for more database-oriented posts, such as these:

• SQL Functions and Techniques Every Data Person Should Know
• Optimize MySQL Database Schema
• MySQL LIKE Operator: A Detailed Guide

Try out the SHOW command learned in this article in the MySQL sandbox below:

Thanks for reading,  until next time! This post was written by Carlos Schults. Carlos is a consultant and software engineer with experience in desktop, web, and mobile development. Though his primary language is C#, he has experience with a number of languages and platforms. His main interests include automated testing, version control, and code quality.