4.11 Using SHOW and DESCRIBE to Review Table Structures

The SELECT statement retrieves the information containedin your databases. You can also ask MySQL to show you information aboutyour databases, such as database and table names or information about the columns or indexes in a table. This section discusses the SHOW and DESCRIBE statements, which provide the following types of information:

SHOW DATABASES;
SHOW TABLES [FROM db_name];
SHOW CREATE TABLE table_name;
DESCRIBE table_name;

You're already familiar with the DESCRIBE statement. Its output format was discussed in the "Introduction" and it has been used in several examples earlier in this study guide.

To determine the databases or tables that exist on your server, use the SHOW statement. SHOW is a versatile statement that has several variations for displaying many types of information.

SHOW DATABASES displays a list of the databases that your server manages:

mysql>
SHOW DATABASES;
+-------------+
|  Database   |
+-------------+
| menagerie   |
| mysql       |
| test        |
| world       |
+-------------+

The mysql and test databases are created during MySQL installation, so you're likely to see both of them in the output from the SHOW DATABASES statement. The mysql database contains the grant tables and should always be present because the grant tables contain user account information that the server uses to control access to the databases. The test database will be present unless someone has removed it.

The output of the SHOW DATABASES statement depends on whether you have the SHOW DATABASES privilege. If you have the privilege, the statement shows the names of all existing databases. Otherwise, it shows only those databases to which you have access.

To determine the tables a particular database contains, use SHOW TABLES:

mysql>
SHOW TABLES FROM world;
+-----------------+
| Tables_in_world |
+-----------------+
| City            |
| Country         |
| CountryLanguage |
+-----------------+

The FROM clause names the database whose table names you want to determine. With no FROM clause, SHOW TABLES displays the names of the tables in the default database. If there is no default database, an error occurs:

mysql>
SHOW TABLES;ERROR 1046: No Database Selected

SHOW DATABASES and SHOW TABLES can each take a LIKE 'pattern 'clause (note the quotes). With LIKE, the statement performs a pattern-matching operation and displays information only about databases or tables with names that match the pattern. Pattern matching is discussed in section 6.2, "Using LIKE for Pattern Matching."

mysql> S
HOW DATABASES LIKE 'm%';
+---------------+
| Database (m%) |
+---------------+
| menagerie     |
| mysql         |
+---------------+
mysql>
SHOW TABLES FROM world LIKE '%tr%';
+------------------------+
| Tables_in_world (%tr%) |
+------------------------+
| Country                |
| CountryLanguage        |
+------------------------+

To obtain information about the structure of a given table, use DESCRIBE or one of the forms of the SHOW statement that displays the kind of table information in which you're interested.

Information about a table's columns can be obtained using DESCRIBE. For example:

mysql>
DESCRIBE CountryLanguage;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| Country    | char(3)       |      | PRI |         |       |
| Language   | char(30)      |      | PRI |         |       |
| IsOfficial | enum('T','F') |      |     | F       |       |
| Percentage | float(3,1)    |      |     | 0.0     |       |
+------------+---------------+------+-----+---------+-------+

The format of DESCRIBE was discussed in the "Introduction." DESCRIBE table_name is a synonym for SHOW COLUMNS FROM table_name or SHOW FIELDS FROM table_name. These statements are equivalent:

DESCRIBE CountryLanguage;
SHOW COLUMNS FROM CountryLanguage;
SHOW FIELDS FROM CountryLanguage;

SHOW CREATE TABLE shows the CREATE TABLE statement that corresponds to a table's definition, including its columns, indexes, and any table options the table has:

mysql>
SHOW CREATE TABLE CountryLanguage\G*************************** 
1. row ***************************
    Table: CountryLanguage
Create Table: CREATE TABLE ´countrylanguage´ (
 ´Country´ char(3) NOT NULL default '',
 ´Language´ char(30) NOT NULL default '',
 ´IsOfficial´ enum('T','F') NOT NULL default 'F',
 ´Percentage´ float(3,1) NOT NULL default '0.0',
 PRIMARY KEY (´Country´,´Language´)
) TYPE=MyISAM

This chapter excerpt is from MySQL Certification Guide by Paul Dubois et al. (Sams, 2004, ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy this book now.