Show Tables in MariaDB

MariaDB is an open-source relational database management system (RDBMS). MariaDB is a very successful RDBMS that is known for its performance, scalability, and ease of use. When dealing with databases, understanding the structure and organization of their table type becomes important. MariaDB provides several powerful storage engines like XtraDB, Aria, etc.

In this article, we will learn the SHOW TABLES command in MariaDB, along with the syntax, examples, and so on.

SHOW TABLES in MariaDB

The SHOW TABLES statement in MariaDB offers a list of tables that are associated with a given database. It provides an immediate and quick way to view the current list of tables that are available in the database without using complicated queries or interfaces.

Syntax of SHOW TABLES:

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

Explanation:

• SHOW TABLES: This is the main clause to list the tables.
• [FROM database_name]: Indicates the name of the database from which you want to retrieve the list of tables.
• [LIKE ‘pattern’]: This is an optional clause. It allows you to filter the results based on a pattern.

MariaDB Show Tables Statement Examples

To understand of SHOW TABLE in MariaDB, we need some table through which we will understand better.

Let’s create some tables in the management studio.

cities Table:

CREATE TABLE cities
(
city_id INT,
city_name varchar(255)
);

actors Table:

CREATE TABLE actors
(
actor_id INT,
actor_name varchar(255)
);

customers Table:

CREATE TABLE customers
(
id INT,
first_name varchar(255),
first_name varchar(255)
);

Query:

SHOW TABLES;

This is the simple command will return a list of all tables within the currently selected database.

Output:

All tables

2. Display Tables with a Like Clause

The LIKE clause is used in SQL queries to search for a specified pattern in a column. It is particularly useful when you want to retrieve data that matches a certain character pattern or string. In the below syntax, we are searching for the table whose name is starting with ‘cities’.

Query:

SHOW TABLES LIKE 'cities%';

Output:

Explanation: In the Output as we can see that the name of the table starting with cities get out.

3. Display Tables from Specific Database

Suppose you have multiple databases and in that there are so many tables. So if you want to get the list of tables from any specific database then the syntax for that is given below:

Syntax:

SHOW TABLES FROM minal;

Output:

Here all the tables which are in minal database will get listed.

Table in specific database

4. Show the Table Type

Base Table

When we will create a table and insert data into it, actually we are working with a base table. Base tables define the structure of the data, including the columns, data types, and constraints.

Example:

CREATE TABLE Books 
(
  id INT PRIMARY KEY,
  book_name VARCHAR(50),
  price DECIMAL(10, 2)
);

Query:

The query to show the types of table is as follows:

SHOW FULL TABLES;

Output:

Tables with their type

5. Display Tables with a WHERE Clause

The WHERE Clause filters the rows based on specific conditions and it is the statement that details the standards by which the data will be filtered. In the below statement, We are searching for the tables whose type are Base Table.

Query:

SHOW FULL TABLES WHERE table_type = 'BASE TABLE';

Output:

Base Tables

Explanation: As we can see below that the table whose types are Base will get listed.

Use Cases of SHOW TABLES

Database Exploration: In many cases, the starting point of database structure understanding is SHOW TABLES. It helps us to quickly see the tables that are offered and get an idea of the data structure.

Query Planning: However, before creating complex queries or joining tables it is necessary to know what tables are available. SHOW TABLES helps to formulating queries by information about the available resources.

Pattern Matching: The LIKE clause in SHOW TABLES is very useful when we need to locate tables that have a particular naming convention or have a prefix in common.

Conclusion

In database management, it is crucial to comprehend the architecture of your database. SHOW TABLES command in MariaDB makes this easier by providing a straightforward and fast way to view the tables within a database. If you are a database administrator, developer, or data analysts, including SHOW TABLES in your workflow will help you work with MariaDB databases easily. Considering further details about MariaDB, certainly, learning the basics, such as the SHOW TABLES command will become one of the expertise in terms of managing and querying databases.