8.8. Obtaining Table and Index Metadata

The SELECT statement retrieves the information contained in your tables. You can also ask MySQL to show you table metadata; that is, information about your tables. Metadata includes information such as table names or column or index definitions.

The INFORMATION_SCHEMA database has a TABLES table that contains table metadata. For example, to display information about the world.City table, use this statement:

 mysql> SELECT * FROM INFORMATION_SCHEMA.TABLES     -> WHERE TABLE_SCHEMA = 'world'     -> AND TABLE_NAME = 'City'\G *************************** 1. row ***************************   TABLE_CATALOG: NULL    TABLE_SCHEMA: world      TABLE_NAME: City      TABLE_TYPE: BASE TABLE          ENGINE: MyISAM         VERSION: 10      ROW_FORMAT: Fixed      TABLE_ROWS: 4079  AVG_ROW_LENGTH: 67     DATA_LENGTH: 273293 MAX_DATA_LENGTH: 18858823439613951    INDEX_LENGTH: 43008       DATA_FREE: 0  AUTO_INCREMENT: 4080     CREATE_TIME: 2005-05-28 20:20:22     UPDATE_TIME: 2005-05-29 20:54:51      CHECK_TIME: NULL TABLE_COLLATION: latin1_swedish_ci        CHECKSUM: NULL  CREATE_OPTIONS:    TABLE_COMMENT:  

Information about indexes is available from INFORMATION_SCHEMA in the STATISTICS table.

For further information about INFORMATION_SCHEMA, see Chapter 20, "Obtaining Database Metadata."

MySQL also supports a family of SHOW statements that display metadata. Some that pertain to tables are SHOW TABLES and SHOW CREATE TABLE.

To determine the tables that 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 (3D000): No database selected 

SHOW TABLES can take a LIKE 'pattern' clause. With LIKE, the statement performs a pattern-matching operation and displays information only about tables with names that match the pattern. Patterns are discussed in Section 10.3.2, "Using LIKE for Pattern Matching."

 mysql> SHOW TABLES FROM world LIKE '%tr%'; +------------------------+ | Tables_in_world (%tr%) | +------------------------+ | Country                | | 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` (   `CountryCode` char(3) NOT NULL default '',   `Language` char(30) NOT NULL default '',   `IsOfficial` enum('T','F') NOT NULL default 'F',   `Percentage` float(4,1) NOT NULL default '0.0',   PRIMARY KEY  (`CountryCode`,`Language`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 

DESCRIBE is another statement that displays table structure metadata. You're already familiar with DESCRIBE; its output format was discussed in the Introduction and it has been used in several examples earlier in this study guide. Here is an example of its output:

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

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; 

You can also use SHOW to obtain index information. To find out what indexes a table has, use SHOW CREATE TABLE to display the CREATE TABLE statement that corresponds to the table structure, including its indexes. For more detailed information about the indexes, use SHOW INDEX. For example, SHOW INDEX produces the following output for the Country table of the world database:

 mysql> SHOW INDEX FROM Country\G *************************** 1. row ***************************        Table: Country   Non_unique: 0     Key_name: PRIMARY Seq_in_index: 1  Column_name: Code    Collation: A  Cardinality: NULL     Sub_part: NULL       Packed: NULL         Null:   Index_type: BTREE      Comment: 

The output indicates that the table has a single index, a primary key on the Code column. For the CountryLanguage table, the output has two rows because the primary key includes two columns, Country and Language:

 mysql> SHOW INDEX FROM CountryLanguage\G *************************** 1. row ***************************        Table: CountryLanguage   Non_unique: 0     Key_name: PRIMARY Seq_in_index: 1  Column_name: Country    Collation: A  Cardinality: NULL     Sub_part: NULL       Packed: NULL         Null:   Index_type: BTREE      Comment: *************************** 2. row ***************************        Table: CountryLanguage   Non_unique: 0     Key_name: PRIMARY Seq_in_index: 2  Column_name: Language    Collation: A  Cardinality: NULL     Sub_part: NULL       Packed: NULL         Null:   Index_type: BTREE      Comment: 

The Seq_in_index values show the order of the columns within the index. They indicate that the primary key columns are Country first and Language second. That information corresponds to the following PRIMARY KEY declaration:

 PRIMARY KEY (Country, Language)