In MariaDB, SHOW TABLES is an administrative statement that lists the non-TEMPORARY tables, sequences and views in a given database.

Syntax

The syntax goes like this:

SHOW [FULL] TABLES [FROM db_name]
    [LIKE 'pattern' | WHERE expr]

Example

Here’s an example to demonstrate:

SHOW TABLES;

Result:

+------------------------+
| Tables_in_krankykranes |
+------------------------+
| Customers              |
| Dogs                   |
| Gameshow               |
| OrderItems             |
| Orders                 |
| PetShow                |
| Pets                   |
| Products               |
| Vendors                |
| t1                     |
+------------------------+

This shows us the tables in the current database, which in this case is the KrankyKranes database.

Show the Table Type

We can use the FULL modifier to return the table type:

USE sakila;
SHOW FULL TABLES;

Result:

+----------------------------+------------+
| Tables_in_sakila           | Table_type |
+----------------------------+------------+
| actor                      | BASE TABLE |
| address                    | BASE TABLE |
| category                   | BASE TABLE |
| city                       | BASE TABLE |
| country                    | BASE TABLE |
| customer                   | BASE TABLE |
| customer_list              | VIEW       |
| film                       | BASE TABLE |
| film_actor                 | BASE TABLE |
| film_category              | BASE TABLE |
| film_list                  | VIEW       |
| film_text                  | BASE TABLE |
| inventory                  | BASE TABLE |
| language                   | BASE TABLE |
| nicer_but_slower_film_list | VIEW       |
| payment                    | BASE TABLE |
| rental                     | BASE TABLE |
| sales_by_film_category     | VIEW       |
| sales_by_store             | VIEW       |
| staff                      | BASE TABLE |
| staff_list                 | VIEW       |
| store                      | BASE TABLE |
+----------------------------+------------+

Here, I switched to the Sakila database and then ran SHOW FULL TABLES. We can see that some of the tables returned are actually views.

As mentioned, the statement returns tables, sequences and views.

The LIKE Clause

The LIKE clause, if present on its own, indicates which table names to match:

SHOW FULL TABLES
LIKE 'f%';

Result:

+-----------------------+------------+
| Tables_in_sakila (f%) | Table_type |
+-----------------------+------------+
| film                  | BASE TABLE |
| film_actor            | BASE TABLE |
| film_category         | BASE TABLE |
| film_list             | VIEW       |
| film_text             | BASE TABLE |
+-----------------------+------------+

The WHERE Clause

The WHERE clause can be used to filter the results based on a given criteria:

SHOW FULL TABLES
WHERE Table_type = 'BASE TABLE';

Result:

+------------------+------------+
| Tables_in_sakila | Table_type |
+------------------+------------+
| actor            | BASE TABLE |
| address          | BASE TABLE |
| category         | BASE TABLE |
| city             | BASE TABLE |
| country          | BASE TABLE |
| customer         | BASE TABLE |
| film             | BASE TABLE |
| film_actor       | BASE TABLE |
| film_category    | BASE TABLE |
| film_text        | BASE TABLE |
| inventory        | BASE TABLE |
| language         | BASE TABLE |
| payment          | BASE TABLE |
| rental           | BASE TABLE |
| staff            | BASE TABLE |
| store            | BASE TABLE |
+------------------+------------+

We can also use the WHERE clause against the first column by using the Tables_in_dbname convention, where dbname is the name of the database:

SHOW FULL TABLES
WHERE Tables_in_sakila = 'customer';

Result:

+------------------+------------+
| Tables_in_sakila | Table_type |
+------------------+------------+
| customer         | BASE TABLE |
+------------------+------------+