MySQL Cookbook, 2nd Edition

Listing or Checking Existence of Databases or Tables

Problem

You want a list of databases hosted by the MySQL server or a list of tables in a database. Or you want to check whether a particular database or table exists.

Solution

Use INFORMATION_SCHEMA to get this information. The SCHEMATA table contains a row for each database, and the TABLES table contains a row for each table in each database.

Discussion

To retrieve the list of databases hosted by the server, use this statement:

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

Add an ORDER BY SCHEMA_NAME clause if you want a sorted result.

To check whether a specific database exists, use a WHERE clause with a condition that names the database. If you get a row back, the database exists. If not, it doesn’t. The following Ruby method shows how to perform an existence test for a database:

def database_exists(dbh, db_name)
  return dbh.select_one("SELECT SCHEMA_NAME
                         FROM INFORMATION_SCHEMA.SCHEMATA
                         WHERE SCHEMA_NAME = ?", db_name) != nil
end

To obtain a list of tables in a database, name the database in the WHERE clause of a statement that selects from the TABLES table:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'cookbook';

Add an ORDER BY TABLE_NAME clause if you want a sorted result.

To obtain a list of tables in the default database, use this statement instead:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE();

If no database has been selected, DATABASE() returns NULL, and no rows ...