How to check if a table already exists in the database with MySQL with INFORMATION_SCHEMA.TABLES.?

In order to check if a table already exists in the database by using MySQL, you can use INFORMATION_SCHEMA.TABLES. Following is the syntax −

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘yourDatabaseName’
AND TABLE_NAME = ’yourTableName’;

Let us implement the above syntax in order to check if a table already exists in the database.

Case 1: When the table is present −

mysql> SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'sample'
AND TABLE_NAME = 'DemoTable';

This will produce the following output −

+--------------+
| TABLE_NAME   |
+--------------+
| DemoTable    |
+--------------+
1 row in set (0.01 sec)

Case 2: When the table is not present −

mysql> SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA= 'sample'
AND TABLE_NAME= 'DemoTable2';

This will produce the following output −

Empty set (0.01 sec)

Chandu yadav

Updated on: 30-Jul-2019

820 Views

Get certified by completing the course

Get Started