Listing tables in a MySQL database

Posted by Kyle Hankinson June 24th, 2020

---

Listing tables

Have you ever needed to see the tables in a MySQL database? MySQL provides a simple and easy query for listing them.

SHOW TABLES

The SHOW TABLES command is the quickest and easiest way to get the list of tables available. You can run this from the MySQL CLI or from any MySQL GUI.

Example

In the example northwind database, running SHOW TABLES will give you results similar to the following:

+--------------------------------+
| Tables_in_northwind            |
+--------------------------------+
| Alphabetical list of products  |
| Categories                     |
| Category Sales for 1997        |
| Current Product List           |
| Customer and Suppliers by City |
| CustomerCustomerDemo           |
| CustomerDemographics           |
| ...                            |
| Products by Category           |
| Quarterly Orders               |
| Region                         |
| Sales Totals by Amount         |
| Sales by Category              |
| Shippers                       |
| Summary of Sales by Quarter    |
| Summary of Sales by Year       |
| Suppliers                      |
| Territories                    |
+--------------------------------+

Tables Only?

By default when running SHOW TABLES you will receive a list of both Tables and Views. If you want to narrow down to just showing objects which are a table, you will want the following:

SHOW FULL TABLES WHERE Table_Type = 'BASE TABLE'

Similar, if you want just views then you will want:

SHOW FULL TABLES WHERE Table_Type = 'VIEW'

Additional options

There are a few other options available with the SHOW TABLES command. You can find additional details at the official MySQL Documentation.

---