Testing Whether a Table Exists

9.11.1 Problem

You want to know whether a table exists.

9.11.2 Solution

Use SHOW TABLES to see if the table is listed.

9.11.3 Discussion

You can use the SHOW TABLES statement to test whether a specific table exists by adding a LIKE clause that matches the name of the table:

SHOW TABLES LIKE 'tbl_name';
SHOW TABLES FROM db_name LIKE 'tbl_name';

If you get a row back, the table exists. If not, it doesn't. Here's a Perl function that performs an existence test for a table:

sub table_exists
{
my ($dbh, $tbl_name) = @_;
my $db_clause = "";

 ($db_clause, $tbl_name) = (" FROM $1", $2) if $tbl_name =~ /(.*).(.*)/;
 $tbl_name =~ s/([%_])/\$1/g; # escape any special characters
 return ($dbh->selectrow_array ("SHOW TABLES $db_clause LIKE '$tbl_name'"));
}

The function checks the table name argument to see if it's in db_name.tbl_name form. If so, it strips off the database name and uses it to add a FROM clause to the statement. Otherwise, the test is against the current database. Note that the function returns false if the table exists but you have no privileges for accessing it.

There are other ways to check whether or not a table exists besides SHOW TABLES. Either of the following SELECT statements will execute successfully if the table exists, and fail if it does not:

SELECT * FROM tbl_name WHERE 1=0;
SELECT COUNT(*) FROM tbl_name;

To use these statements within a program, first set your API's error trapping not to terminate your program on an error. Then attempt to execute the statement and test whether it succeed or fails.

The SELECT * statement is preferable to SELECT COUNT(*) for some table types such as BDB or InnoDB, which require a full table scan to evaluate COUNT(*). For ISAM and MyISAM tables, the COUNT(*) statement is optimized to use the record count stored in the table.