MySQL check if table exists

At times there is an essential requirement to find out if a table exists or not in a database, and the same, we will be looking into this article particularly:

1. MySQL check if table exists : SHOW TABLES.
2. MySQL check if table exists : Information Schema.
3. Function to check if the table exists or not in MySQL.
4. MySQL check if table exists before creating.

We will be going through each section but let us create the table first by running the below query. We are creating table customer_data, and we will verify through different methods if this table exists in the database or not.

CREATE TABLE customer_data (
    customerId INT ,
    customerName VARCHAR(255),
    customerPlace VARCHAR(255)
);

We got a message verifying that the table customer_data is created.

Action Output Message : 14:03:00 CREATE TABLE customer_data ( customerId INT , customerName VARCHAR(255), customerPlace VARCHAR(255) ) 0 row(s) affected 0.020 sec

MySQL check if table exists : SHOW TABLES

Let us see if we can trace our table customer_data or not using SHOW TABLES. Running the query with SHOW TABLES without specifying the table name will give us all the current database tables.

Observe the below query along with the message and the output.

SHOW TABLES;

Action Output Message : 14:12:50 SHOW TABLES 11 row(s) returned 0.0019 sec / 0.000013 sec.

Output:-

What if we want to find out if a particular table exists or not in our database?In this case, we will do SHOW TABLES LIKE <table_name>. Notice below query along with the output.

SHOW TABLES LIKE "customer_data";

Action Output Message : 15:58:49 SHOW TABLES LIKE “customer_data” 1 row(s) returned 0.0015 sec / 0.000012 sec

Output:- The output returns only one row with customer_data table.

Let us replace customer_data with any other random table, say patients_data, which does not exist in our database, and observe the output.

SHOW TABLES LIKE "patients_data";

Though no error occurred, the output message shows that 0 rows returned but none table displayed in the output.

Action Output Message: 16:04:49 SHOW TABLES LIKE “patients_data” 0 row(s) returned 0.0012 sec / 0.0000079 sec

MySQL check if table exists : Information Schema

There is another way to determine if a table exists or not, and that is through information schema. Notice the below query and its output.

SELECT COUNT(*)
FROM information_schema.tables 
WHERE table_schema = DATABASE()
AND table_name = "customer_data";

Here we are trying to get the number of tables from the information schema where the table name is customer_data, and the database is the working database. The DATABASE() function gets the value of our current database. 

PRO TIP: Instead of the DATABASE() function, we can explicitly write our database's name.

Output:-

Action Output Message: 16:12:41 SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = database() AND table_name = “customer_data” LIMIT 0, 1000 1 row(s) returned 0.0046 sec / 0.000010 sec

Function to check if the table exists or not in MySQL

At times the requirement is to write a function that can return a value to determine if the table exists or not. Below is one such example:

• Name of function :  tableExistsOrNot
• Input Parameters :  _tableName
• Returns : 1 or 0
• Functionality:  functionality is to check whether the table passed as input exists in the database or not. If the table exists, then it will return 1 else 0.

DELIMITER $$
CREATE FUNCTION tableExistsOrNot (_tableName varchar(255))
RETURNS BOOLEAN
BEGIN
  IF 
  (SELECT COUNT(*)FROM information_schema.tables WHERE table_schema =  DATABASE() AND table_name = _tableName)= 1
  THEN 
  RETURN TRUE;
   ELSE
     RETURN FALSE;
     END IF;
  
END
$$
DELIMITER ;

We will be calling this function by passing table names customer_data and patients_data successively. The former table will return 1, and the latter will return 0.

SELECT tableExistsOrNot("customer_data");

Output:-

select tableExistsOrNot("patients_data");

Output:-

MySQL check if table exists before creating

Sometimes, the requirement is to check if the table exists or not and create it only if it does not exist. Below is the query displaying how to do that:

CREATE TABLE IF NOT EXISTS customer_data (
    customerId INT ,
    customerName VARCHAR(255),
    customerPlace VARCHAR(255)
);

In this example, we are creating table customer_data. Still, by adding the clause IF NOT EXISTS in the CREATE statement, we ensure that table customer_data will only be created if it does not already exist in our current working database.

We hope this article helped you with finding out if the table exists in the database or not. Good Luck !!!