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 the Index on Table in MariaDB
• How to describe the table in MariaDB

How to show tables in MariaDB

To show the tables in the MariaDB database, we are going to use the SHOW TABLES statement in this section.

• The MariaDB SHOW TABLES statement allows listing the temporary tables, views, and sequences from the current database.

The syntax is given below.

SHOW [FULL] TABLES [FROM DATABASE_NAME]

• SHOW TABLES: In a particular database, SHOW TABLES displays a list of all non-TEMPORARY tables, views, and sequences.
• SHOW FULL TABLES: If we use the SHOW FULL TABLES statement, then it displays a second output column, the FULL modifier is supported. The table type’s second column accepts the following values: BASE TABLE for tables, VIEW for views, and SEQUENCE for sequences.
• SHOW TABLES FROM DATABASE_NAME: The FROM DATABASE_NAME clause can be used to indicate the name of the database from which we wish to display tables, views, and sequences if we are not already connected to that particular database.

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

Check the available databases in the MariaDB server using the below code.

SHOW DATABASES;

Select the specific database whose table you want to show.

USE classiccars

The above query selects the classicars database from the available databases on the MariaDB server.

Use the below statement or query to view all tables in MariaDB.

SHOW TABLES ;

Suppose we are not connected to any database to show the tables, then we will use the below statement.

SHOW TABLES FROM classiccars;

From the above output, we have fetched all the tables in classiccars database directly using the FROM clause without connecting to that database.

We hope that you have understood the subtopic “How to show tables in MariaDB” by using the SHOW TABLES statement in 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

We have learned how to show tables from the above subsection, In this section, we will see how to check tables in MariaDB. The syntax to check the table is the same as SHOW TABLES FROM DATABASE_NAME.

Let’s check the table in MariaDB by using the below query.

SHOW TABLES FROM AIRBNB_DB;

In the above query, we have used the SHOW TABLES statement with the FROM clause on the database AIRBNB_DB.

The above output shows the all tables of the database AIRBNB_DB as we can see in the output of the query SHOW TABLES FROM AIRBNB_DB.

With the help of the MariaDB SHOW TABLES statement, we hope you have grasped the subtopic “How to check tables in MariaDB”. We have provided a detailed definition and an example to aid in the explanation.

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, similar to the majority of relational databases, MariaDB offers helpful metadata regarding the database itself. While the majority of other databases refer to this metadata as a catalog, the INFORMATION.SCHEMA metadata are actually referred to as tables in the official MariaDB manual.

• What important is the data this INFORMATION.SCHEMA tables give, regardless of their name. The INFORMATION.SCHEMA contains all information, including views, user privileges, columns, and tables.
• For our purposes, the tables’ metadata is important, which we can search to actually extract information on the sizes of different tables in the database.

The INFORMATION SCHEMA.TABLES table has about 19 or 20 columns, but we’ll concentrate on two in particular: INDEX_LENGTH and DATA_LENGTH to estimate how much disc space is consumed by tables.

• INDEX_LENGTH: The index file size for the table is indicated by the variable INDEX LENGTH.
• DATA_LENGTH: DATA LENGTH is a table’s total data length.

Let’s see an example using the below query.

SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024) AS `Size (KB)`
FROM
  information_schema.TABLES
WHERE
    TABLE_SCHEMA = "classiccars"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
ASC;

In this example, which uses the classiccars database, the DATA LENGTH and INDEX LENGTH are combined as bytes, and then their respective values are divided by 1024 to produce kilobytes.

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 non-TEMPORARY tables that are open at the moment in the table cache are listed via SHOW OPEN TABLES. If the table is locked, it will represent 1

The syntax is given below.

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

In the syntax explanation:

• SHOW OPEN TABLES: 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.

Take an example and check the lock tables in the database classiccars.

SHOW OPEN TABLE FROM classiccars;

In the above output,

column In_use displays the number of threads that are now accessing a particular table, either to lock it or to wait for the table lock to lock it. As we can see the customers and payments tables of the classiccars database are locked by a thread because its value is 1.

Note: If the table is locked then it will present as 1 otherwise 0 in the table is not locked.

By utilizing the MariaDB SHOW OPEN TABLES statement, we hope you have comprehended the subtopic “How to check table lock in MariaDB”. We gave a detailed explanation and presented an example to aid in learning.

Read: MariaDB If statement in Select

How to check the Index on Table in MariaDB

We are going to learn and understand how to check the index on tables in MariaDB using the statement SHOW INDEXES. In MariaDB, Table index information is returned by SHOW INDEX.

The syntax is given below.

SHOW INDEXES FROM TABLE_NAME;

SHOW INDEXES FROM TABLE_NAME FROM
DATABASE_NAME;

SHOW INDEXES FROM DATABASE_NAME.TABLE_NAME;

In the syntax explanation:

• SHOW INDEXES FROM TABLE_NAME: In the first query, we will use the table name from which we want to see all indexes list.
• SHOW INDEXES FROM TABLE_NAME FROM DATABASE_NAME: In the second query, if we are not connected to any database then we can specify the database name in the SHOW INDEXES statement.
• SHOW INDEXES FROM DATABASE_NAME.TABLE_NAME: 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 above 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 STATES_OF_USA table.

Using the MariaDB SHOW INDEXES statement, we hope you have grasped the subtopic “How to check Index on Table in MariaDB”. We have provided a detailed definition and an example to serve as a better illustration.

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 table’s structure is displayed by the Describe command, including the name of each column, its data type, and its nullability, which indicates whether or not it can hold null values. At the time the table was created, each of these properties was described.

The syntax is given below.

[ DESCRIBE | DESC ] YOUR_TABLENAME;

Let’s take an example and describe any table of the database in the MariaDB server by following the below steps:

DESC STATES_OF_USA;

As we see in the above query, we have used the DESC statement to describe the STATES_OF_USA table. It shows the information about the data type, null, and key column from the STATES_OF_USA table in the below output.

We hope that you have understood the subtopic “How to describe the table in MariaDB” by using the MariaDB DESC statement in 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.

• MariaDB Date Greater Than
• MariaDB See If Table Exists
• MariaDB Display List of Databases
• MariaDB Select Where Not Empty
• MariaDB Check String Length
• MariaDB Check If Rows Exists

By the end of this MariaDB tutorial How to Show Tables in MariaDB, now you have a better understanding of how to show the tables, check the lock and size of the tables, and how to describe or find the index of any tables. 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 the Index on Table in MariaDB
• How to describe a table in MariaDB