Checking if tables exist in a MySQL database

I recently needed to create an add-on for a shopping cart system which required it’s own database table and relied on database tables from other add-ons.

It needed an install method to create it’s own tables and needed to check for the existence of other tables.

Previously, I’ve needed to check for the existence of one table, which is easy enough:

SHOW TABLES LIKE 'TABLE_NAME'

And, retrieving all tables allows you to check the result for multiple tables in the application layer:

SHOW TABLES

But I wondered if there might be a more elegant solution to checking for multiple tables.

The answer is to query the information_schema database directly which provides a bit more flexibility:

SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'DB_NAME' 
AND (TABLE_NAME = 'TABLE1_NAME' OR TABLE_NAME = 'TABLE2_NAME')

The application can use the result for a simple boolean check if all required tables exist by counting the number of results and can also determine which tables need creating from the list.

The query can also be modified with more criteria, such as whether the table is a view or base table.