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.

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.

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 match, which is the correct result.

To check whether a specific table exists, use a WHERE clause with a condition that names the table. Here’s a Ruby method that performs an existence test for a table in a given database:

def table_exists(dbh, db_name, tbl_name)
  return dbh.select_one(
                "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?",
                db_name, tbl_name) != nil
end

Some APIs provide a database-independent way to get database or table lists. In Perl DBI, the database handle tables() method returns a list of tables in the default database:

@tables = $dbh->tables ();

The Ruby method is similar:

tables = dbh.tables

In Java, you can use JDBC methods designed to return lists of databases or tables. For each method, invoke your connection object’s getMetaData() method and use the resulting DatabaseMetaData object to retrieve the information you want. Here’s how to produce a list of databases:

// get list of databases
DatabaseMetaData md = conn.getMetaData ();
ResultSet rs = md.getCatalogs ();
while (rs.next ())
  System.out.println (rs.getString (1));  // column 1 = database name
rs.close ();

A similar procedure lists the tables in a given database:

// get list of tables in database named by dbName; if
// dbName is the empty string, the default database is used
DatabaseMetaData md = conn.getMetaData ();
ResultSet rs = md.getTables (dbName, "", "%", null);
while (rs.next ())
  System.out.println (rs.getString (3));  // column 3 = table name
rs.close ();