Meta-Data:
Data about data.  MySQL has several commands for accessing meta-data:

Using information_schema :
 

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'INFORMATION_SCHEMA'
ORDER BY table_name;

+---------------------------------------+
| table_name                            |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| KEY_COLUMN_USAGE                      |
| PROFILING                             |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
17 rows in set (0.03 sec)

To display the name of each column for a table in the information_schema:

SELECT column_name FROM information_schema.columns
WHERE table_schema = 'INFORMATION_SCHEMA'
AND table_name = 'VIEWS';

+-----------------+
| column_name     |
+-----------------+
| TABLE_CATALOG   |
| TABLE_SCHEMA    |
| TABLE_NAME      |
| VIEW_DEFINITION |
| CHECK_OPTION    |
| IS_UPDATABLE    |
| DEFINER         |
| SECURITY_TYPE   |
+-----------------+
8 rows in set (0.02 sec)

NOTE: All standard SQL works with the information_schema, so statements and literals are NOT case sensitive. You can use any valid SQL command, but should only use SELECT unless you really know what you are doing!

Display Storage Engines:

SELECT table_name, engine FROM information_schema.tables
WHERE table_schema = 'test';


+----------------------+--------+
| table_name           | engine |
+----------------------+--------+
| 123                  | MyISAM |
| allergy              | MyISAM |
| attendee             | MyISAM |
| auth_info            | InnoDB |
| authorisbn           | MyISAM |
| authors              | MyISAM |
| city                 | InnoDB |
| citycopy1            | MyISAM |
| citycopy2            | MyISAM |
| citycopy3            | MyISAM |
| citycopy4            | InnoDB |
| cityshort            | MyISAM |
| contact_info         | InnoDB |
| countries            | InnoDB |
| country              | InnoDB |
| countrylangcount     | NULL   |
| countrylanguage      | MyISAM |


| unique_names         | MyISAM |
| v                    | NULL   |
| zipcodes             | MyISAM |
| zipstaken            | MyISAM |
+----------------------+--------+

Find all tables/columns that have a data-type of SET

SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE data_type='set';

+--------------+--------------+-------------+
| table_schema | table_name   | column_name |
+--------------+--------------+-------------+
| mysql        | columns_priv | Column_priv |
| mysql        | proc         | sql_mode    |
| mysql        | procs_priv   | Proc_priv   |
| mysql        | tables_priv  | Table_priv  |
| mysql        | tables_priv  | Column_priv |
| test         | allergy      | symptom     |
+--------------+--------------+-------------+
6 rows in set, 1 warning (0.27 sec)

Display Default Character set and Collation:

SELECT character_set_name, collation_name
FROM information_schema.collations
WHERE is_default='Yes';

+--------------------+---------------------+
| character_set_name | collation_name      |
+--------------------+---------------------+
| big5               | big5_chinese_ci     |
| dec8               | dec8_swedish_ci     |
| cp850              | cp850_general_ci    |
| hp8                | hp8_english_ci      |
| koi8r              | koi8r_general_ci    |
| latin1             | latin1_swedish_ci   |
| latin2             | latin2_general_ci   |
| swe7               | swe7_swedish_ci     |
| ascii              | ascii_general_ci    |
| ujis               | ujis_japanese_ci    |
| sjis               | sjis_japanese_ci    |
| hebrew             | hebrew_general_ci   |
| tis620             | tis620_thai_ci      |
| euckr              | euckr_korean_ci     |
| koi8u              | koi8u_general_ci    |
| gb2312             | gb2312_chinese_ci   |
| greek              | greek_general_ci    |
| cp1250             | cp1250_general_ci   |
| gbk                | gbk_chinese_ci      |
| latin5             | latin5_turkish_ci   |
| armscii8           | armscii8_general_ci |
| utf8               | utf8_general_ci     |
| ucs2               | ucs2_general_ci     |
| cp866              | cp866_general_ci    |
| keybcs2            | keybcs2_general_ci  |
| macce              | macce_general_ci    |
| macroman           | macroman_general_ci |
| cp852              | cp852_general_ci    |
| latin7             | latin7_general_ci   |
| cp1251             | cp1251_general_ci   |
| cp1256             | cp1256_general_ci   |
| cp1257             | cp1257_general_ci   |
| binary             | binary              |
| geostd8            | geostd8_general_ci  |
| cp932              | cp932_japanese_ci   |
| eucjpms            | eucjpms_japanese_ci |
+--------------------+---------------------+
36 rows in set (0.00 sec)

Display a list of databases and a count of all tables:

SELECT table_schema, COUNT(*)
FROM information_schema.tables
GROUP BY table_schema;

+--------------------+----------+
| table_schema       | COUNT(*) |
+--------------------+----------+
| information_schema |       17 |
| mysql              |       18 |
| test               |       53 |
+--------------------+----------+
3 rows in set (0.03 sec)

SHOW & DESCRIBE Command:

Now just a list of databases:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

SHOW all tables in all databases:

mysql> SHOW TABLES;
+----------------------+
| Tables_in_test       |
+----------------------+
| 123                  |
| allergy              |
| attendee             |
| auth_info            |
| authorisbn           |
| authors              |

SHOW all tables in the mysql database:

mysql> SHOW TABLES FROM mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
| user_info                 |
+---------------------------+
18 rows in set (0.00 sec)

Wild Cards can also be used:


mysql> SHOW DATABASES LIKE 'm%';
+---------------+
| Database (m%) |
+---------------+
| mysql         |
+---------------+
1 row in set (0.00 sec)

SHOW COLUMNS FROM the CountryLanguage table:

mysql> SHOW COLUMNS FROM CountryLanguage;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3)       | NO   | PRI |         |       |
| Language    | char(30)      | NO   | PRI |         |       |
| IsOfficial  | enum('T','F') | NO   |     | F       |       |
| Percentage  | float(4,1)    | NO   |     | 0.0     |       |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

SHOW COLUMNS FROM the CountryLanguage table in a NICER format:

mysql> SHOW COLUMNS FROM CountryLanguage\G
*************************** 1. row ***************************
  Field: CountryCode
   Type: char(3)
   Null: NO
    Key: PRI
Default:
  Extra:
*************************** 2. row ***************************
  Field: Language
   Type: char(30)
   Null: NO
    Key: PRI
Default:
  Extra:
*************************** 3. row ***************************
  Field: IsOfficial
   Type: enum('T','F')
   Null: NO
    Key:
Default: F
  Extra:
*************************** 4. row ***************************
  Field: Percentage
   Type: float(4,1)
   Null: NO
    Key:
Default: 0.0
  Extra:
4 rows in set (0.00 sec)

SHOW Indexes for a table:

mysql> SHOW KEYS FROM city\G
*************************** 1. row ***************************
       Table: city
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: ID
   Collation: A
 Cardinality: 4321
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: city
  Non_unique: 1
    Key_name: FK_city_1
Seq_in_index: 1
 Column_name: CountryCode
   Collation: A
 Cardinality: 432
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
2 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM Country WHERE 'Default' IS NULL;
Empty set (0.00 sec)

SHOW also supports a WHERE clause:

mysql> SHOW COLUMNS FROM Country WHERE 'Default' IS NULL;
Empty set (0.00 sec)

SHOW CHARACTER SET:

mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
36 rows in set (0.00 sec)

SHOW COLLATION:


mysql> SHOW COLLATION;
+----------------------+----------+-----+---------+----------+---------+
| Collation            | Charset  | Id  | Default | Compiled | Sortlen |
+----------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci      | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin             | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci      | dec8     |   3 | Yes     |          |       0 |
| dec8_bin             | dec8     |  69 |         |          |       0 |
| cp850_general_ci     | cp850    |   4 | Yes     |          |       0 |
| cp850_bin            | cp850    |  80 |         |          |       0 |
| hp8_english_ci       | hp8      |   6 | Yes     |          |       0 |
| hp8_bin              | hp8      |  72 |         |          |       0 |
| koi8r_general_ci     | koi8r    |   7 | Yes     |          |       0 |
| koi8r_bin            | koi8r    |  74 |         |          |       0 |
| latin1_german1_ci    | latin1   |   5 |         | Yes      |       1 |
| latin1_swedish_ci    | latin1   |   8 | Yes     | Yes      |       1 |
| latin1_danish_ci     | latin1   |  15 |         | Yes      |       1 |
| latin1_german2_ci    | latin1   |  31 |         | Yes      |       2 |
| latin1_bin           | latin1   |  47 |         | Yes      |       1 |
| latin1_general_ci    | latin1   |  48 |         | Yes      |       1 |
| latin1_general_cs    | latin1   |  49 |         | Yes      |       1 |
| latin1_spanish_ci    | latin1   |  94 |         | Yes      |       1 |
| latin2_czech_cs      | latin2   |   2 |         | Yes      |       4 |
| latin2_general_ci    | latin2   |   9 | Yes     | Yes      |       1 |
| latin2_hungarian_ci  | latin2   |  21 |         | Yes      |       1 |
| latin2_croatian_ci   | latin2   |  27 |         | Yes      |       1 |
| latin2_bin           | latin2   |  77 |         | Yes      |       1 |
| swe7_swedish_ci      | swe7     |  10 | Yes     |          |       0 |

SHOW TABLES FROM information_schema:


mysql> SHOW TABLES FROM information_schema;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| KEY_COLUMN_USAGE                      |
| PROFILING                             |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
17 rows in set (0.00 sec)

DESCRIBE information_schema.character_sets:


mysql> DESCRIBE information_schema.character_sets;
+----------------------+-------------+------+-----+---------+-------+
| Field                | Type        | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| CHARACTER_SET_NAME   | varchar(64) | NO   |     |         |       |
| DEFAULT_COLLATE_NAME | varchar(64) | NO   |     |         |       |
| DESCRIPTION          | varchar(60) | NO   |     |         |       |
| MAXLEN               | bigint(3)   | NO   |     | 0       |       |
+----------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Using mysqlshow to obtain Meta-Data:


C:\WINNT>mysqlshow
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| test               |
+--------------------+




C:\WINNT>mysqlshow test
Database: test
+----------------------+
|        Tables        |
+----------------------+
| 123                  |
| allergy              |
| attendee             |
| auth_info            |
| authorisbn           |
| authors              |
| city                 |
| citycopy1            |
| citycopy2            |
| citycopy3            |
| citycopy4            |
| cityshort            |
| contact_info         |
| countries            |
| country              |
| countrylangcount     |




C:\WINNT>mysqlshow test City
Database: test  Table: City
+-------------+----------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field       | Type     | Collation         | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------------+----------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| ID          | int(11)  |                   | NO   | PRI |         | auto_increment | select,insert,update,references |         |
| Name        | char(35) | latin1_swedish_ci | NO   |     |         |                | select,insert,update,references |         |
| CountryCode | char(3)  | latin1_swedish_ci | NO   | MUL |         |                | select,insert,update,references |         |
| District    | char(20) | latin1_swedish_ci | NO   |     |         |                | select,insert,update,references |         |
| Population  | int(11)  |                   | NO   |     | 0       |                | select,insert,update,references |         |
+-------------+----------+-------------------+------+-----+---------+----------------+---------------------------------+---------+




C:\WINNT>mysqlshow test City CountryCode
Database: test  Table: City  Wildcard: CountryCode
+-------------+---------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field       | Type    | Collation         | Null | Key | Default | Extra | Privileges                      | Comment |
+-------------+---------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| CountryCode | char(3) | latin1_swedish_ci | NO   | MUL |         |       | select,insert,update,references |         |
+-------------+---------+-------------------+------+-----+---------+-------+---------------------------------+---------+


C:\WINNT>mysqlshow "t%"
Wildcard: t%
+-----------+
| Databases |
+-----------+
| test      |
+-----------+