Metadata (a very brief introduction)

One of the great features of Oracle is the amount of Metadata available. For those that don't know metadata is essentially data about data, in Oracles case this is large number of tables which detail every aspect of the database objects.

In general there are 2 groups, the user tables and the all tables. The user tables give information about the current users database objects and the all tables which detail information for every user (schema).

The metadata is stored in tables, which can be accessed using SQL in the same way your normal tables are accessed.

For example lets say you want to see all of the tables owner by your user.

SQL> select table_name from user_tables where rownum < 10;

TABLE_NAME
------------------------------
ADMIN_CHART
ADMIN_CHART_ITEM
.
.
.
SUBCATEGORY
SYSTEM_PARAMETER
ZENDOR_STOCK_FEED

63 rows selected.

If we want to see all of the tables for all of the users we can use the all_tables view (the metadata tables are infact views on far more complex metadata tables).

SQL> select table_name from all_tables where rownum < 10;

TABLE_NAME
------------------------------
SEG$
CLU$
OBJ$
.
.
.
WISHLIST
WISHLIST_EVENT_NAME
WISHLIST_ITEM

831 rows selected.

So how does MySQL handle metadata? It depends on your version, most people will be running verison 4.1 or under, In these versions metadata is accessed using the show command. The show command consists of 22 different types of information about the database. The full list can be found here.
http://dev.mysql.com/doc/mysql/en/show.html

The biggest difference between show and Oracles approach is that the show command offers very limited selection requirements, it's pretty much all or nothing. In our examples above we limited our selection to show the table name only, but there are over 40 columns we can use. MySQL's show command does not allow use to pick and choose which of the columns we would like to see. There is support for a like keyword on some of the show commands but this is limited to say the table name or session variable name.

To see a list of tables in MySQL we can issue the SHOW TABLES command.

mysql> show tables;
+----------------+
| Tables_in_pers |
+----------------+
| clientdata     |
| dept           |
.
.
.
| test_letters   |
| test_order     |
| test_select    |
+----------------+
14 rows in set (0.14 sec)

From version 5.0 (not currently a full release) MySQL have introduced the information_schema. This replicates the functionality of the metadata tables of Oracle. MySQL users can now use SQL statements against these new tables to select particular columns, use where clauses and group by. A list of the information schema tables cab be found here.
http://dev.mysql.com/doc/mysql/en/information-schema-tables.html

So we can see a list of tables using the information_schema like so.

mysql> select table_name from information_schema.tables;
+---------------------------------------+
| table_name                            |
+---------------------------------------+
| SCHEMATA                              |
| TABLES                                |
| COLUMNS                               |
.
.
.
| test_text                             |
| ts_data                               |
+---------------------------------------+
63 rows in set (1.74 sec)