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:
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.
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:
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:
Explanation: As we can see below that the table whose types are Base will get listed.
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.
Contact Us