Posted by Kyle Hankinson June 24th, 2020
Have you ever needed to see the tables in a MySQL database? MySQL provides a simple and easy query for listing them.
SHOW TABLESThe 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.
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 | +--------------------------------+
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'There are a few other options available with the SHOW TABLES command. You can find additional details at the official MySQL Documentation.