MariaDB See If Table Exists

In MariaDB, the SHOW TABLES and SHOW TABLE STATUS statements are used to retrieve information about the tables in a database. The SHOW TABLES statement lists the names of the tables in a database, while the SHOW TABLE STATUS statement returns detailed information about the tables, such as the engine, row format, and data size.

In addition to these statements, there are other ways you can check if a table exists in a MariaDB database, such as using the INFORMATION_SCHEMA in a SELECT statement. It is often useful to check if a table exists before inserting data into it, as this can prevent errors and ensure that your data is being inserted into the correct table.

In this MariaDB tutorial, we will look at how to check if a table exists in MariaDB using the SHOW TABLES, SHOW TABLE STATUS, and INFORMATION_SCHEMA options.

By the end of this MariaDB, you should have a good understanding of how to check if a table exists in MariaDB and how to use this information to prevent errors when inserting data into tables.

• Implementation of MariaDB see if a table exists
• Example of MariaDB test if a table exists
• MariaDB SHOW TABLE STATUS statement
• How to MariaDB check the table exists before inserting

MariaDB see if a table exists

There are a few different ways you can check if a table exists in a MariaDB database. One option is to use the SHOW TABLES statement, which lists all of the tables in the current database. You can use the LIKE clause to filter the list of tables based on a pattern.

The syntax is given below.

SHOW TABLES LIKE 'table_name';

Let’s take an example and show if table customers exist or not.

SHOW TABLES LIKE 'customers';

This SHOW TABLES statement will list all tables in the current database that have a name that matches the pattern ‘customers’. If the table exists, it will be included in the list. If the table does not exist, the list will be empty.

Read: MariaDB Order By Decreasing

MariaDB test if a table exists

Another option is to use the INFORMATION_SCHEMA database, which contains metadata about the tables in your MariaDB database. You can use the INFORMATION_SCHEMA.TABLES table to check if a table exists by querying it with a SELECT statement.

The syntax is given below.

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'table_name';

Let’s take an example using the below query.

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'customers';

In the above query, SELECT statement will return a row for the ‘customers’ table if it exists in the current database, and an empty result set if it does not exist.

The above output shows all the customer’s tables in each database available on the MariaDB server. This is how to use th INFORMATION_SCHEMA.TABLES to see if a table exists.

Read: MariaDB Display List of Databases

MariaDB SHOW TABLE STATUS statement

The SHOW TABLE STATUS statement in MariaDB is used to retrieve information about the tables in a database. It returns a result set with one row for each table, containing various details about the table, such as its name, engine, row format, and data size.

The syntax is given below on how to use the SHOW TABLE STATUS statement.

SHOW TABLE STATUS FROM `database_name`;

Where the SHOW TABLE STATUS statement will retrieve information about all of the tables in the database_name.

You can use the WHERE clause to filter the list of tables based on a condition. The syntax is given below.

SHOW TABLE STATUS FROM `database_name`
WHERE `Name` LIKE 'customers%';

In the above syntax SHOW TABLE STATUS statement will retrieve information about all tables in the database_name database that has a name that starts with ‘customers’.

The SHOW TABLE STATUS statement is useful for getting a quick overview of the tables in a database or for troubleshooting issues with tables. Some of the columns returned by the SHOW TABLE STATUS statement are:

• Name: the name of the table
• Engine: the storage engine used by the table
• Row_format: the format used to store rows in the table
• Rows: the number of rows in the table
• Avg_row_length: the average length of rows in the table
• Data_length: the size of the data in the table
• Max_data_length: the maximum size that the data in the table can grow to

Let’s take an example and view the status of all the tables in the specific database using the below query.

SHOW TABLE STATUS;

The above output shows the status of all the tables in the specific database. you can find the status of the tables according to your requirement with the help of the above-specified syntax for SHOW TABLE STATUS.

Read: MariaDB Show Column Data Type

MariaDB check the table exists before inserting

To check if a table exists before inserting data into it in MariaDB, you can use one of the methods described in the previous answer, such as using the SHOW TABLES statement or querying the INFORMATION_SCHEMA.TABLES table, or using the EXISTS operator.

For example, you could use the following SELECT statement with the EXISTS operator to check if the ‘customers’ table exists before inserting data into it.

IF NOT EXISTS (SELECT 1 FROM `customers`) THEN
    CREATE TABLE `customers` (
        `id` INT NOT NULL AUTO_INCREMENT,
        `name` VARCHAR(255) NOT NULL,
         `country` VARCHAR(255) NOT NULL,

        PRIMARY KEY (`id`)
    );
END IF;

INSERT INTO `customers` (`name`) VALUES ('John Smith','USA');

In the above example, we are using an IF NOT EXISTS clause to check if the ‘customers’ table exists in the current database. If the table does not exist, we use the CREATE TABLE statement to create the table with the following structure.

IF NOT EXISTS (SELECT 1 FROM `customers`) THEN
    CREATE TABLE `customers` (
        `id` INT NOT NULL AUTO_INCREMENT,
        `name` VARCHAR(255) NOT NULL,
         `country` VARCHAR(255) NOT NULL,

        PRIMARY KEY (`id`)
    );

The ‘customers’ table has three columns: id, name, and country. The id column is an integer that is marked as NOT NULL and set as the primary key of the table. The name and country column is a string with a maximum length of 255 characters and is also marked as NOT NULL.

After the IF NOT EXISTS clause, we use the INSERT INTO statement to insert a row into the ‘customers’ table. In this case, we are inserting a single row into the ‘customers’ table with the following values.

INSERT INTO `customers` (`name`) VALUES ('John Smith');

In conclusion, MariaDB provides several options for checking if a table exists in a database. The SHOW TABLES and SHOW TABLE STATUS statements can be used to retrieve a list of tables or detailed information about the tables in a database. The INFORMATION_SCHEMA database can also be queried to check if a table exists.

It is often useful to check if a table exists before inserting data into it, as this can prevent errors and ensure that your data is being inserted into the correct table. By using one of the options discussed in this MariaDB tutorial, you can easily check if a table exists in MariaDB and take appropriate action based on the result.

You may also like to read the following MariaDB tutorials.

• MariaDB Order By Multiple Columns
• MariaDB Alter Table Add Foreign Key
• Create Views In MariaDB Group By
• MariaDB Alter Table Compound Primary Key
• MariaDB Alter Table If Exists
• Add Data To Table MariaDB
• MariaDB If statement in Select
• MariaDB Select Unique

I hope this MariaDB tutorial has been helpful in explaining how to check if a table exists in MariaDB and how to use this information to prevent errors when inserting data into tables.

• Implementation of MariaDB see if a table exists
• Example of MariaDB test if a table exists
• MariaDB SHOW TABLE STATUS statement
• How to MariaDB check the table exists before inserting