MySQL: How to Check if a Table Exists with SQL CodeUnit 09 FEB 2011

To check if a table exists with SQL in a MySQL database is pretty easy, thanks to the nifty SHOW TABLES command.

To find if a specific table exists, we simply extend the standard SHOW TABLES statement with a LIKE parameter, which allows us to search for a specific string or for a partial string using the standard % modifier. If the statement returns a non-empty result set, we know the table exists.

For example:

SHOW TABLES LIKE 'activity-log';

will return a empty result set if your database doesn’t have a table named ‘activity-log’ in it. Similarly,

SHOW TABLES LIKE '%activity-log';

will return a result set containing all tables that end with ‘activity-log’, e.g. ‘sms-activity-log’ and ’email-activity-log’ as well as plain old ‘activity-log’ of course!


About Craig Lotter

Software developer, husband and dad to two little girls. Writer behind An Exploring South African. I don't have time for myself any more.