What if you forget the name of a database or table, or what the structure of a given table is (for example, what its columns are called)? MySQL addresses this problem through several statements that provide information about the databases and tables it supports.

You have previously seenSHOW DATABASES, which lists the databases managed by the server. To find out which database is currently selected, use theDATABASE()function:

mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | menagerie | +------------+

If you have not yet selected any database, the result isNULL.

To find out what tables the default database contains (for example, when you are not sure about the name of a table), use this statement:

mysql> SHOW TABLES; +---------------------+ | Tables_in_menagerie | +---------------------+ | event | | pet | +---------------------+

The name of the column in the output produced by this statement is alwaysTables_in_db_name, wheredb_nameis the name of the database. SeeSection 13.7.5.37, “SHOW TABLES Statement”, for more information.

If you want to find out about the structure of a table, theDESCRIBEstatement is useful; it displays information about each of a table's columns:

mysql> DESCRIBE pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+

Fieldindicates the column name,Typeis the data type for the column,NULLindicates whether the column can containNULLvalues,Keyindicates whether the column is indexed, andDefaultspecifies the column's default value.Extradisplays special information about columns: If a column was created with theAUTO_INCREMENToption, the value isauto_incrementrather than empty.

DESCis a short form ofDESCRIBE. SeeSection 13.8.1, “DESCRIBE Statement”, for more information.

You can obtain theCREATE TABLEstatement necessary to create an existing table using theSHOW CREATE TABLEstatement. SeeSection 13.7.5.10, “SHOW CREATE TABLE Statement”.

If you have indexes on a table,SHOW INDEX FROMtbl_nameproduces information about them. SeeSection 13.7.5.22, “SHOW INDEX Statement”, for more about this statement.