You want a list of tables in a database or a list of databases hosted by the MySQL server.
Use SHOW TABLES or SHOW DATABASES.
To obtain a list of tables in the current database, use this query:
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:
Be Careful with SHOW Statements
Be careful how you interpret the results from SHOW TABLES and SHOW DATABASES. The result from SHOW TABLES will be empty if you don't have permission to access the table. The result from SHOW DATABASES may be empty as well. If the server was started with the --safe-show-database or --skip-show-database option, you may not be able to get much information with the SHOW DATABASES statement.
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 ( );
Using the mysql Client Program
Writing MySQL-Based Programs
Record Selection Techniques
Working with Strings
Working with Dates and Times
Sorting Query Results
Modifying Tables with ALTER TABLE
Obtaining and Using Metadata
Importing and Exporting Data
Generating and Using Sequences
Using Multiple Tables
Introduction to MySQL on the Web
Incorporating Query Resultsinto Web Pages
Processing Web Input with MySQL
Using MySQL-Based Web Session Management
Appendix A. Obtaining MySQL Software
Appendix B. JSP and Tomcat Primer
Appendix C. References