How to use SHOW TABLES in MariaDB

If you need an overview of all the tables in a MariaDB database, you can use SHOW TABLES . Starting from version 11.2.0, this administrative statement also includes temporary tables. Make sure you have the necessary user permissions to execute this statement.

What is the syntax for SHOW TABLES?

The basic syntax for MariaDB’s SHOW TABLES looks like this:

SHOW TABLES [FROM name_of_database] [LIKE 'pattern'];

After the statement, specify the database that you want all the tables to be listed for. The parameter LIKE is optional and helps filter the results according to a pattern that you define.

Maria DB SHOW TABLES example

To see how SHOW TABLES works, you can try the following example. First, we are going to create a new database using the MariaDB statement CREATE DATABASE:

CREATE DATABASE City_Country_River;

Next, we are going to create some tables using MariaDB CREATE TABLE. You can do this using the following code:

CREATE TABLE city
(
PostalCode INT,
Name VARCHAR(50)
);

CREATE TABLE country
(
CountryCode INT,
Name VARCHAR(50)
);

CREATE TABLE river
(
Name VARCHAR(50),
Length INT
);

Now, you can use MariaDB’s SHOW TABLES to get a list of all tables in the City_Country_River database.

SHOW TABLES;

If you have multiple databases and want to list tables from a specific one, you can specify which database using FROM:

SHOW TABLES FROM City_Country_River;

How to filter results with LIKE

You can use the parameter LIKE to filter the results from SHOW TABLES. This can be especially useful when working with large databases that contain lots of tables. Even though our example only contains three tables, we can still use it to see how LIKE works. For example, if you want to find all tables that start with the word ‘river’, you could enter the following code:

SHOW TABLES LIKE 'river%';

In our example, the only table that will be listed is the table named ‘river’.

How to display table types with FULL

If, in addition to the names of the tables, you also want to find out what types of tables you have in your database, you can use MariaDB’s SHOW TABLES together with FULL. This adds an additional column to the output named ‘table_type’. This column provides information about the table type using one of three categories: BASE TABLE, VIEW and SEQUENCE. You can use the code below to see how it works:

SHOW FULL TABLES FROM City_Country_River;

What is SHOW TABLES in MariaDB used for?

SHOW TABLES is a crucial tool for managing databases in MariaDB, allowing you to keep track of all tables and plan your next steps. Once you have an overview of all the tables in a database, you can easily access a specific table or delete a table using the MariaDB statement DROP TABLE.