Recipe 9.5. 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 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 

NOTE

The results retrieved from INFORMATION_SCHEMA depend on your privileges. You'll see information only for those databases or tables for which you have some privileges. This means that an existence test will return false if the given object exists but you have no privileges for accessing it.

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 ();