How to Show Tables in MariaDB

This MariaDB tutorial will go over how to run a query to display tables in MariaDB. To assist you in better understanding the topic, we shall explore and conclude several situations in this section. The whole list of topics we’ll cover is given below.

  • How to show tables in MariaDB
  • How to check tables in MariaDB
  • How to check table size in MariaDB
  • How to check table lock in MariaDB
  • How to check Index on Table in MariaDB
  • How to view tables in MariaDB
  • How to see all tables in MariaDB
  • How to describe the table in MariaDB

How to show tables in MariaDB

In this MariaDB section, we will learn and understand how to show tables in MariaDB by the query, which will be explained with the help of an illustrated example and syntax.

The MariaDB SHOW TABLES statement allows listing the temporary tables, views, and sequences from the current database. Let’s see the syntax of the SHOW TABLES statement by the following query:

SYNTAX:

SHOW [FULL] TABLES [FROM CURRENT_DATABASENAME]
[LIKE PATTERNS];

SHOW TABLES FROM CURRENT_DATABASE;

In the syntax explanation:

  • In the first query, we have already selected the current database then the SHOW TABLES statement will return the tables, views, and sequences in the current database.
  • In the second query, if we are not connected to the particular database then we can use the FROM clause to specify the name of the database from which we want to show tables, views, and sequences.

Here is a sample example of using the MariaDB SHOW TABLES statement by the following query:

EXAMPLE:

SHOW FULL TABLES ;

SHOW TABLES FROM AIRBNB_DB;

In the aforementioned query, we have to use the SHOW TABLES statement to see the list of full tables in the current database which we are using right now. And suppose we are not connected to any database then we will use the second query as shown above for the result set.

How to see all tables in MariaDB example
Example of How to show tables in MariaDB

We hope that you have understood the subtopic “How to show tables in MariaDB” by using the MariaDB SHOW TABLES statement by the query. For a better understanding, we have used an example and explained it in depth.

Read: MariaDB Add Auto Increment Column

How to check tables in MariaDB

In this subtopic tutorial, we will learn and understand how to check tables in MariaDB by the following query:

EXAMPLE:

SHOW TABLES FROM AIRBNB_DB;

In this query, we have used the SHOW TABLES statement with the FROM clause on the current database as AIRBNB_DB. This means that we have removed the FULL keyword which will not show the view, sequence, and base tables from the particular database in the result set.

How to view tables in MariaDB example
Example of How to check tables in MariaDB

We hope that you have understood the subtopic “How to check tables in MariaDB” by using the MariaDB SHOW TABLES statement from the current database by the query. For a better explanation, we have used an example and defined it in depth.

Read: MariaDB Create Database And User

How to check table size in MariaDB

Here we will learn and understand how to check table size in MariaDB by the query, which will be explained with the help of an illustrated example.

EXAMPLE:

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "AIRBNB_DB"
ORDER BY (data_length + index_length) DESC;

In the above-mentioned query, we have used the SELECT statement to retrieve the records of the TABLE_NAME column and given the name as the TABLE column by using the ALIAS clause with the AS keyword.

Then we used the ROUND function to add the DATA_LENGTH and INDEX_LENGTH which will calculate the size of the form of “MEGABYTES” For, we again used the ALIAS clause with the AS keyword and gave the name as SIZE (MB) for the output result set.

This will retrieve all table names from the INFORMATION_SCHEMA.TABLES with the WHERE condition. In the WHERE condition, the TABLE_SCHEMA column is used with the EQUAL TO operator to find the current_Database as AIRBNB_DB and arrange the size of the DATA_LENGTH and INDEX_LENGTH column in descending order by using the ORDER BY expression DESC.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all tables_name with the size in MB from the INFORMATION_SCHEMA.TABLES table.

But if the SELECT statement gets executed successfully and retrieves an empty record set from the INFORMATION_SCHEMA.TABLE table only when the WHERE condition turns out to be FALSE.

How to check table size in MariaDB example
Example of How to check table size in MariaDB

We hope that you have understood the subtopic “How to check table size in MariaDB” by using the MariaDB SELECT statement on the INFORMATION_SCHEMA.TABLES by the query. For a better description, we have used an example and demonstrated it in depth.

Read: MariaDB Str_To_Date

How to check table lock in MariaDB

We will learn and understand how to check table lock in MariaDB by the query, which will be explained with the help of syntax and an illustrated example.

In MariaDB, the SHOW OPEN TABLES statement is used to list all temporary tables which are currently open in the table cache. If the table is locked, it will represent 1 which is the table is being dropped and renamed in the current_database.

SYNTAX:

SHOW OPEN TABLES [ FROM CURRENT_DATABASENAME] 
[ LIKE ' PATTERN'  | WHERE EXPRESSION ]. 

In the syntax explanation:

  • The SHOW OPEN TABLES statement list non-temporary tables that are currently open in the table store.
  • FROM: It presents and restricts tables that are shown to those present in the current_databasename.
  • LIKE: It presents on its own and indicates which table name should be a match.

EXAMPLE:

SHOW OPEN TABLE FROM AIRBNB_DB;

In the aforementioned query, we have used the SHOW OPEN TABLES with the FROM clause on the currrent_database as AIRBNB_DB which means that it will provide the tables which are locked in the current database. If the table is locked then it will present as 1 otherwise 0 in the table is not locked.

How to check table lock in MariaDB example
Example of How to check table lock in MariaDB

We hope that you have understood the subtopic “How to check table lock in MariaDB” by using the MariaDB SHOW OPEN TABLES statement in the current database by the query. For better learning, we have used an example and described it in depth.

Read: MariaDB If statement in Select

How to check Index on Table in MariaDB

Here we will learn and understand how to check the index on tables in MariaDB by the query, which will be explained with the help of syntax and an illustrated example.

In MariaDB, the SHOW INDEXES statement is used to allow query index from the table. If we want to show indexes from the table then we follow the following query:

SYNTAX:

SHOW INDEXES FROM YOUR_TABLENAME;

SHOW INDEXES FROM YOUR_TABLE_NAME
IN CURRENT_DATABASE_NAME;

SHOW INDEXES FROM DATABASE_NAME.YOUR_TABLENAME;

In the syntax explanation:

  • In the first query, we will use the table name from which we want to see all indexes list.
  • In the second query, if we are not connected to any database then we can specify the database name in the SHOW INDEXES statement.
  • In the third query, if we want to use the third way to show indexes from the table_name.

Here is a sample example of the MariaDB SHOW INDEXES statement to show indexes from the table by the following query:

EXAMPLE:

SHOW INDEXES FROM STATES_OF_USA;

In the previous query, we used the SHOW INDEXES statement to show the index from the STATES_OF_USA table. It will show index_name in the KEY_NAME column and the COLUMN_NAME column from the current_table.

How to check index on table in MariaDB example
Example of How to check the index on the table in MariaDB

We hope that you have understood the subtopic “How to check Index on Table in MariaDB” by using the MariaDB SHOW INDEXES statement on the table by the query. For a better illustration, we have used an example and defined it in depth.

Read: MariaDB Select Unique

How to describe the table in MariaDB

Here we will learn and understand how to describe a table in MariaDB by the query, which will be explained with the help of an illustrated example and a syntax.

In MariaDB, the DESCRIBE statement means to show information in detail. We can use the DESCRIBE statement to show the structure of the table such as column_name, constraints on column_name, etc. And the DESC statement is a short form of the DESCRIBE statement. Both the DESCRIBE and DESC statements are case-sensitive and equivalent.

SYNTAX:

[ DESCRIBE | DESC ] YOUR_TABLENAME;

EXAMPLE:

DESC STATES_OF_USA;

As we see in the above query, we have used the DESC statement to describe the current table as STATES_OF_USA by the query. It will provide the data type, NULL, and KEY column detail from the output result set.

How to describe table in MariaDB example
Example of How to describe the table in MariaDB

We hope that you have understood the subtopic “How to describe the table in MariaDB” by using the MariaDB DESC statement by the query. We have used a sample example and described it in depth, for a better description.

You may also like to read the following MariaDB tutorials.

By the end of this MariaDB tutorial to might understand the use of How to Show Tables in MariaDB statements after reading this lesson. We also discussed a few instances to help you comprehend the concept. Below is a list of all the topics we’ve covered.

  • How to show tables in MariaDB
  • How to check tables in MariaDB
  • How to check table size in MariaDB
  • How to check table lock in MariaDB
  • How to check Index on Table in MariaDB
  • How to view tables in MariaDB
  • How to see all tables in MariaDB
  • How to describe table in MariaDB