MySQL table and index metadata

MySQL has many ways to obtain table metadata. Use SHOW TABLES FROM database to obtain a list of the tables in a database (omitting the FROM will use the default database). SHOW TABLES can also take a LIKE 'pattern' clause as follows:

mysql> SHOW TABLES FROM mydatabase LIKE '%users%';
+---------------------------------+
| Tables_in_mydatabase (%users%)  |
+---------------------------------+
| admin_users                     | 
| registered_users                | 
| registered_users_groups         | 
+---------------------------------+
3 rows in set (0.00 sec)

Another statement that comes in handy is SHOW CREATE TABLE, which will display the CREATE TABLE statement associated with a given table:

mysql> SHOW CREATE TABLE mydatabase.admin_users\G
*************************** 1. row ***************************
       Table: admin_users
Create Table: CREATE TABLE `admin_users` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `email` varchar(64) NOT NULL default '',
  `password` varchar(40) NOT NULL default '',
  `first_name` varchar(32) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `AU_email_idx` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=32 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

SHOW INDEX will display index information:

mysql> SHOW INDEX FROM  mydatabase.admin_users\G
*************************** 1. row ***************************
       Table: admin_users
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: id
   Collation: A
 Cardinality: 18
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
*************************** 2. row ***************************
       Table: admin_users
  Non_unique: 0
    Key_name: AU_email_idx
Seq_in_index: 1
 Column_name: email
   Collation: A
 Cardinality: 18
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
2 rows in set (0.00 sec)

To see column information from a table use one of the following:

DESCRIBE mydatabase.admin_users
SHOW COLUMNS FROM mydatabase.admin_users 
SHOW FIELDS FROM mydatabase.admin_users

Any of these statements would yield output similar to this:

+--------------+----------------------+------+-----+--------+----------------+
| Field        | Type                 | Null | Key | Default| Extra          |
+--------------+----------------------+------+-----+--------+----------------+
| id           | smallint(5) unsigned | NO   | PRI | NULL   | auto_increment | 
| email        | varchar(64)          | NO   | UNI |        |                | 
| password     | varchar(40)          | NO   |     |        |                |
| first_name   | varchar(32)          | YES  |     | NULL   |                | 
| last_name    | varchar(64)          | YES  |     | NULL   |                |
+--------------+----------------------+------+-----+--------+----------------+

The information_shema database also includes a TABLES table with table metadata, a STATISTICS table for table index metadata and other metadata tables.