mysql gets database and table information

What if you forget the name of the database or table, or what the structure of a given table is (for example, what are its columns called)? MySQL solves this problem with a few statements that provide information about the database and its supporting tables.

You have 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 haven't selected any database yet, the result is NULL.

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

mysql> SHOW TABLES; +---------------------+
| Tables in menagerie |
+---------------------+
| event               |
| pet                 |
+---------------------+

If you want to know the structure of a table, use the DESCRIBE command; it displays the contents of the table Information for each column:

mysql> DESCRIBE pet;

+---------+-------------+------+-- ---+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-- -----------+------+-----+---------+-------+
| name | varchar(20 ) | YES | | NULL |
| owner | varchar(20) | YES | | NULL | varchar(20) | YES | | NULL | YES | | NULL | birth | date | YES | NULL | death | date | YES | NULL --+------+-----+---------+-------+

Field displays the column name, Type is the data type of the column, Null means Whether the column can contain NULL values, Key shows whether the column is indexed and Default specifies the default value of the column.

If the table has an index, SHOW INDEX FROM tbl_name generates information about the index.