How to Show Tables in MariaDB

This MariaDB tutorial will cover how to show tables in MariaDB. To help you better understand the topic, we will explore and conclude several situations in this section.

How to show tables in MariaDB

To show tables in MariaDB, we will use the SHOW TABLES statement in this section.

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

The syntax is given below.

SHOW [FULL] TABLES [FROM DATABASE_NAME]

• SHOW TABLES: In a particular database, SHOW TABLES lists 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 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 on the MariaDB server using the code below.

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 ;

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

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 you have understood the subtopic “How to show tables in MariaDB” by using the SHOW TABLES statement in the query. We have used an example and explained it in depth to help you better understand.

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 using the query below.

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 all the tables of the database AIRBNB_DB, as we can see in the output of the query SHOW TABLES FROM AIRBNB_DB.

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

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 most other databases refer to this metadata as a catalog, the INFORMATION.SCHEMA metadata are referred to as tables in the official MariaDB manual.

• What is important is the data these 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, and we can search for it to 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: 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 you understand the subtopic “How to check table size in MariaDB” by using the MariaDB SELECT statement on the INFORMATION_SCHEMA.TABLES by the query. We have used an example and demonstrated it in depth for a better description.

Read: MariaDB Str_To_Date

How to check table lock in MariaDB

We will learn and understand how to check table lock in MariaDB using a query, which will be explained using syntax and an illustrated example.

In MariaDB, the non-TEMPORARY tables currently open 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 lists non-temporary tables that are currently open in the table store.
• FROM: It presents and restricts tables shown to those present in the current_databasename.
• LIKE: It presents itself and indicates which table name should 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 now accessing a particular table, either to lock it or 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 their value is 1.

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

We hope you have comprehended the subtopic “How to check table lock in MariaDB” by utilizing the MariaDB SHOW OPEN TABLES statement. 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 will 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 a list of all indexes.
• SHOW INDEXES FROM TABLE_NAME FROM DATABASE_NAME: In the second query, if we are not connected to any database, 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 and COLUMN_NAME columns.

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 better illustrate.

Read: MariaDB Select Unique

How to describe the table in MariaDB

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

• In MariaDB, the Describe command displays the table’s structure, including the name of each column, its data type, and its nullability, which indicates whether or not it can hold null values. Each of these properties was described when the table was created.

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. The output below shows information about the data type, null, and key column from the STATES_OF_USA table.

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, you now 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.