Cry MySQL How to...

List all the tables in a database

To list all of the tables in the current database use:

show tables

To list all of the tables in a database other than the current one, use the following SQL:

show tables from database

where database is the name of the database to query. This will return a query result containing the names of all of each of the database tables. It does not list temporary tables or tables that your account does not have the necessary privilege to view.

for example:

show tables from census

will list all of the tables in the database called "census".

As an alternative, the INFORMATION_SCHEMA table can be queried directly, so:

select table_name from INFORMATION_SCHEMA.TABLES
where table_schema = Database()

is equivalent to:

show tables

and

select table_name from INFORMATION_SCHEMA.TABLES
where table_schema = 'dbname'

is equivalent to:

show tables from dbname

These notes have been tested against MySQL version 4 and 5, and may apply to other versions as well.