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 seen SHOW DATABASES, which
lists the databases managed by the server. To find out which
database is currently selected, use the
DATABASE() function:
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie |
+------------+
If you have not yet selected any database, the result is
NULL.
To find out what tables the default database contains (for example, when you are not sure about the name of a table), use this command:
mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| event |
| pet |
+---------------------+
If you want to find out about the structure of a table, the
DESCRIBE command 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 | |
+---------+-------------+------+-----+---------+-------+
Field indicates the column name,
Type is the data type for the column,
NULL indicates whether the column can contain
NULL values, Key indicates
whether the column is indexed, and Default
specifies the column's default value.
If you have indexes on a table, SHOW INDEX FROM
tbl_name produces information
about them.