MySQL SHOW TABLES: List Tables In a MySQL Database

Summary: in this tutorial, you will learn how to use the MySQL SHOW TABLES command to query tables in a particular database.

To list tables in a MySQL database, you follow these steps:

Login to the MySQL database server using a MySQL client such as mysql
Switch to a specific database using the USE statement.
Use the SHOW TABLES command.

The following illustrates the syntax of the MySQL SHOW TABLES command:

SHOW TABLES;

Code language: SQL (Structured Query Language) (sql)

MySQL SHOW TABLES examples

The following example shows you how to list the table in the classicmodels database.

Step 1. Connect to the MySQL database server:

>mysql -u root -p
 Enter password: **********
 mysql>

Code language: SQL (Structured Query Language) (sql)

Step 2. Switch to classicmodels database:

mysql> use classicmodels;
 Database changed
 mysql>

Code language: SQL (Structured Query Language) (sql)

Step 3. Show tables in the classicmodels database:

> show tables;
 +-------------------------+
 | Tables_in_classicmodels |
 +-------------------------+
 | customers               |
 | employees               |
 | offices                 |
 | orderdetails            |
 | orders                  |
 | payments                |
 | productlines            |
 | products                |
 +-------------------------+
 8 rows in set (0.00 sec)

Code language: SQL (Structured Query Language) (sql)

The SHOW TABLES command allows you to show if a table is a base table or a view. To include the table type in the result, you use the following form of the SHOW TABLES statement.

SHOW FULL TABLES;

Code language: SQL (Structured Query Language) (sql)

Let’s create a view in the classicmodels database called contacts that includes first name, last name and phone from the employees and customers tables for the demonstration.

CREATE VIEW contacts
 AS
 SELECT lastName, firstName, extension as phone
 FROM employees
 UNION
 SELECT contactFirstName, contactLastName, phone
 FROM customers;

Code language: SQL (Structured Query Language) (sql)

Now, you issue the SHOW FULL TABLES command:

> SHOW FULL TABLES
 +-------------------------+------------+
 | Tables_in_classicmodels | Table_type |
 +-------------------------+------------+
 | contacts                | VIEW |
 | customers               | BASE TABLE |
 | employees               | BASE TABLE |
 | offices                 | BASE TABLE |
 | orderdetails            | BASE TABLE |
 | orders                  | BASE TABLE |
 | payments                | BASE TABLE |
 | productlines            | BASE TABLE |
 | products                | BASE TABLE |
 +-------------------------+------------+
 9 rows in set (0.00 sec)

Code language: SQL (Structured Query Language) (sql)

As you can see, all the tables are the base tables except for the contacts table which is a view.

For the database that has many tables, showing all tables at a time may not be intuitive.

Fortunately, the SHOW TABLES command provides you with an option that allows you to filter the returned tables using the LIKE operator or an expression in the WHERE clause as follows:

SHOW TABLES LIKE pattern;SHOW TABLES WHERE expression;

Code language: SQL (Structured Query Language) (sql)

For example, to shows all tables in the classicmodels database that start with the letter p, you use the following statement:

> SHOW TABLES LIKE 'p%';
 +------------------------------+
 | Tables_in_classicmodels (p%) |
 +------------------------------+
 | payments                     |
 | productlines                 |
 | products                     |
 +------------------------------+
 3 rows in set (0.00 sec)

Code language: SQL (Structured Query Language) (sql)

Or to show the tables that end with the string 'es', you use the following statement:

> SHOW TABLES LIKE '%es';
 +-------------------------------+
 | Tables_in_classicmodels (%es) |
 +-------------------------------+
 | employees                     |
 | offices                       |
 | productlines                  |
 +-------------------------------+
 3 rows in set (0.00 sec)

Code language: SQL (Structured Query Language) (sql)

The following statement illustrates how to use the WHERE clause in the SHOW TABLES statement to list all the views in the classicmodels database.

> SHOW FULL TABLES WHERE table_type = 'VIEW';
 +-------------------------+------------+
 | Tables_in_classicmodels | Table_type |
 +-------------------------+------------+
 | contacts                | VIEW       |
 +-------------------------+------------+
 1 row in set (0.00 sec)

Code language: SQL (Structured Query Language) (sql)

Sometimes, you want to see the tables in the database that you are not connected to. In this case, you can use the FROM clause of the SHOW TABLES statement to specify the database from which you want to show the tables.

The following example demonstrates how to show tables that start with 'time';

> SHOW TABLES FROM mysql LIKE 'time%';
 +---------------------------+
 | Tables_in_mysql (time%)   |
 +---------------------------+
 | time_zone                 |
 | time_zone_leap_second     |
 | time_zone_name            |
 | time_zone_transition      |
 | time_zone_transition_type |
 +---------------------------+
 5 rows in set (0.00 sec)

Code language: SQL (Structured Query Language) (sql)

The following statement is equivalent to the statement above but it uses IN instead of FROM.

SHOW TABLES IN mysql LIKE 'time%';

Code language: SQL (Structured Query Language) (sql)

It’s important to note that if you don’t have privileges for a base table or view, it won’t show up in the result set of the SHOW TABLES command.

In this tutorial, you have learned how to use the MySQL SHOW TABLES statement to list all tables in a particular database.

by python-tutorials.in
on 2nd September 2022

0

Leave a Reply Cancel reply