Geek Tips

The information schema in MySQL is a great tool for obtaining just about any type of meta-data about your MySQL server. It is a schema consisting of views that provide just about any information you would want to know about your MySQL installation. For instance, you can find out status and system variables, process listing, database object listing - schemas, tables, triggers, stored procedures and their organization within MySQL, user privileges. .

Originally, people would use SHOW commands to obtain this information. For instance, to see all the schemas (databases) within your MySQL instance:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| admin              |
| aps                |
| contacts_db        |
| fed                |
| memc               |
| metatalk           |
| myschema           |
| mysql              |
| sakila             |
| syscp              |
| test               |
| userdb             |
| webapp             |
| webapps            |
+--------------------+

With information schema, you can do the same thing, but with more control of what is displayed:

mysql> select schema_name from schemata;
+--------------------+
| schema_name        |
+--------------------+
| information_schema |
| admin              |
| aps                |
| contacts_db        |
| fed                |
| memc               |
| metatalk           |
| myschema           |
| mysql              |
| sakila             |
| syscp              |
| test               |
| userdb             |
| webapp             |
| webapps            |
+--------------------+

mysql> select schema_name, default_character_set_name from schemata where schema_name = 'webapp';
+-------------+----------------------------+
| schema_name | default_character_set_name |
+-------------+----------------------------+
| webapp      | latin1                     |
+-------------+----------------------------+

As well, tables, but unlike ‘SHOW TABLES’, you get even more information, such as what storage engine the table is created using!

mysql> select table_name, engine from tables where table_schema = 'webapps';
+----------------------+-----------+
| table_name           | engine    |
+----------------------+-----------+
| children             | MyISAM    |
| children_of_children | MyISAM    |
| comment_log          | ARCHIVE   |
| comments             | MyISAM    |
| deadend              | BLACKHOLE |
| employees            | MyISAM    |
| lengths              | MyISAM    |
| parent               | MyISAM    |
| sessions             | InnoDB    |
| states               | MyISAM    |
| stats                | InnoDB    |
| t1                   | InnoDB    |
| t2                   | InnoDB    |
| users                | InnoDB    |
| users_states         | NULL      |
| v_protected_users    | NULL      |
| v_users              | NULL      |
| v_users_states       | NULL      |
| weblog               | MyISAM    |
+----------------------+-----------+

And now the very reason for this post, there is a really helpful query that Sheeri Cabral and I talked about in our talk yesterday “Understanding How MySQL Works by Understanding Metadata” , is a query that lists what tables relationally depend on other tables (with a foreign key constraint). This is a really useful query!

mysql> select concat(table_name, ' depends on ', referenced_table_name)
        -> from referential_constraints
        -> where constraint_schema = 'sakila'
        -> order by referenced_table_name;

+-----------------------------------------------------------+
| concat(table_name, ' depends on ', referenced_table_name) |
+-----------------------------------------------------------+
| film_actor depends on actor                               |
| store depends on address                                  |
| customer depends on address                               |
| staff depends on address                                  |
| film_category depends on category                         |
| address depends on city                                   |
| city depends on country                                   |
| rental depends on customer                                |
| payment depends on customer                               |
| inventory depends on film                                 |
| film_category depends on film                             |
| film_actor depends on film                                |
| rental depends on inventory                               |
| film depends on language                                  |
| film depends on language                                  |
| payment depends on rental                                 |
| store depends on staff                                    |
| payment depends on staff                                  |
| rental depends on staff                                   |
| customer depends on store                                 |
| staff depends on store                                    |
| inventory depends on store                                |
+-----------------------------------------------------------+

This shows you in an easy-to-read view of what tables depend on other tables. This is using the sakila schema that you can obtain here.

I hope this tip was useful to you!

Tags: information schema, meta data, MySQL

This entry was posted on Tuesday, April 21st, 2009 at 8:45 am and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.