2007/12/19更新

対応バージョン: 5.0.51

当該サーバで定義されている全てのオブジェクトを表示するには以下のように3種類のINFORMATION_SCHEMAをUNION ALLで結合すればよい。

mysql> SELECT
    ->    TABLE_SCHEMA AS object_schema
    ->   ,TABLE_NAME   AS object_name
    ->   ,TABLE_TYPE   AS object_type
    -> FROM INFORMATION_SCHEMA.TABLES
    -> 
    -> UNION ALL
    -> 
    -> SELECT
    ->    ROUTINE_SCHEMA
    ->   ,ROUTINE_NAME
    ->   ,ROUTINE_TYPE
    -> FROM INFORMATION_SCHEMA.ROUTINES
    -> 
    -> UNION ALL
    -> 
    -> SELECT
    ->    TRIGGER_SCHEMA
    ->   ,TRIGGER_NAME
    ->   ,'TRIGGER'
    -> FROM INFORMATION_SCHEMA.TRIGGERS
    -> 
    -> ORDER BY object_schema, object_type, object_name;

+------------------+-------------------------------------+-----------+
|object_schema     |object_name                          |object_type|
+------------------+-------------------------------------+-----------+
|information_schema|CHARACTER_SETS                       |SYSTEM VIEW| 
|information_schema|COLLATIONS                           |SYSTEM VIEW| 
|information_schema|COLLATION_CHARACTER_SET_APPLICABILITY|SYSTEM VIEW| 
|information_schema|COLUMNS                              |SYSTEM VIEW| 
|information_schema|COLUMN_PRIVILEGES                    |SYSTEM VIEW| 
|information_schema|KEY_COLUMN_USAGE                     |SYSTEM VIEW| 
|information_schema|PROFILING                            |SYSTEM VIEW| 
|information_schema|ROUTINES                             |SYSTEM VIEW| 
|information_schema|SCHEMATA                             |SYSTEM VIEW| 
|information_schema|SCHEMA_PRIVILEGES                    |SYSTEM VIEW| 
|information_schema|STATISTICS                           |SYSTEM VIEW| 
|information_schema|TABLES                               |SYSTEM VIEW| 
|information_schema|TABLE_CONSTRAINTS                    |SYSTEM VIEW| 
|information_schema|TABLE_PRIVILEGES                     |SYSTEM VIEW| 
|information_schema|TRIGGERS                             |SYSTEM VIEW| 
|information_schema|USER_PRIVILEGES                      |SYSTEM VIEW| 
|information_schema|VIEWS                                |SYSTEM VIEW| 
|mysql             |columns_priv                         |BASE TABLE | 
|mysql             |db                                   |BASE TABLE | 
|mysql             |func                                 |BASE TABLE | 
|mysql             |help_category                        |BASE TABLE | 
|mysql             |help_keyword                         |BASE TABLE | 
|mysql             |help_relation                        |BASE TABLE | 
|mysql             |help_topic                           |BASE TABLE | 
|mysql             |host                                 |BASE TABLE | 
|mysql             |proc                                 |BASE TABLE | 
|mysql             |procs_priv                           |BASE TABLE | 
|mysql             |tables_priv                          |BASE TABLE | 
|mysql             |time_zone                            |BASE TABLE | 
|mysql             |time_zone_leap_second                |BASE TABLE | 
|mysql             |time_zone_name                       |BASE TABLE | 
|mysql             |time_zone_transition                 |BASE TABLE | 
|mysql             |time_zone_transition_type            |BASE TABLE | 
|mysql             |user                                 |BASE TABLE | 
|test              |pet                                  |BASE TABLE | 
|test              |test_proc                            |PROCEDURE  | 
|test              |test_trigger                         |TRIGGER    | 
|test              |test_view                            |VIEW       | 
+------------------+-------------------------------------+-----------+

関連資料・記事