Listing Tables and Databases
Problem
You want a list of tables in a database or a list of databases hosted by the MySQL server.
Solution
Use SHOW
TABLES
or
SHOW
DATABASES
.
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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.