Listing Tables and Databases

9.10.1 Problem

You want a list of tables in a database or a list of databases hosted by the MySQL server.

9.10.2 Solution

Use SHOW TABLES or SHOW DATABASES.

9.10.3 Discussion

To obtain a list of tables in the current database, use this query:

SHOW TABLES;

However, if no database has been selected, the query will fail. To avoid this problem, you should either make sure there is a current database or name a database explicitly:

SHOW TABLES FROM db_name;

Another form of SHOW returns a list of databases hosted by the server:

SHOW DATABASES;

If you're looking for a database-independent way to get table or database lists and you're using Perl or Java, try the following methods.

In Perl, DBI provides a tables( ) function that returns a list of tables. It works for the current database only:

my @tables = $dbh->tables ( );

In Java, you can use JDBC methods designed to return lists of tables or databases. 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 list the tables in a given database:

// get list of tables in database named by dbName; if // dbName is the empty string, the current 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 ( );

A similar procedure produces 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 ( );