<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Chapter 20. The INFORMATION_SCHEMA Information Database</title><meta name="generator" content="DocBook XSL Stylesheets V1.69.1"><link rel="start" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="up" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="prev" href="views.html" title="Chapter 19. Views"><link rel="next" href="precision-math.html" title="Chapter 21. Precision Math"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">Chapter 20. The <code class="literal">INFORMATION_SCHEMA</code> Information Database</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="views.html">Prev</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="precision-math.html">Next</a></td></tr></table><hr></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="information-schema"></a>Chapter 20. The <code class="literal">INFORMATION_SCHEMA</code> Information Database</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="information-schema.html#information-schema-tables">20.1. <code class="literal">INFORMATION_SCHEMA</code> Tables</a></span></dt><dd><dl><dt><span class="section"><a href="information-schema.html#schemata-table">20.1.1. The <code class="literal">INFORMATION_SCHEMA SCHEMATA</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#tables-table">20.1.2. The <code class="literal">INFORMATION_SCHEMA TABLES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#columns-table">20.1.3. The <code class="literal">INFORMATION_SCHEMA COLUMNS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#statistics-table">20.1.4. The <code class="literal">INFORMATION_SCHEMA STATISTICS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#user-privileges-table">20.1.5. The <code class="literal">INFORMATION_SCHEMA USER_PRIVILEGES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#schema-privileges-table">20.1.6. The <code class="literal">INFORMATION_SCHEMA SCHEMA_PRIVILEGES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#table-privileges-table">20.1.7. The <code class="literal">INFORMATION_SCHEMA TABLE_PRIVILEGES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#column-privileges-table">20.1.8. The <code class="literal">INFORMATION_SCHEMA COLUMN_PRIVILEGES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#character-sets-table">20.1.9. The <code class="literal">INFORMATION_SCHEMA CHARACTER_SETS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#collations-table">20.1.10. The <code class="literal">INFORMATION_SCHEMA COLLATIONS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#collation-character-set-applicability-table">20.1.11. The <code class="literal">INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#table-constraints-table">20.1.12. The <code class="literal">INFORMATION_SCHEMA TABLE_CONSTRAINTS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#key-column-usage-table">20.1.13. The <code class="literal">INFORMATION_SCHEMA KEY_COLUMN_USAGE</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#routines-table">20.1.14. The <code class="literal">INFORMATION_SCHEMA ROUTINES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#views-table">20.1.15. The <code class="literal">INFORMATION_SCHEMA VIEWS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#triggers-table">20.1.16. The <code class="literal">INFORMATION_SCHEMA TRIGGERS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#other-information-schema-tables">20.1.17. Other <code class="literal">INFORMATION_SCHEMA</code> Tables</a></span></dt></dl></dd><dt><span class="section"><a href="information-schema.html#extended-show">20.2. Extensions to <code class="literal">SHOW</code> Statements</a></span></dt></dl></div><a class="indexterm" name="id3055966"></a><a class="indexterm" name="id3055976"></a><a class="indexterm" name="id3055983"></a><a class="indexterm" name="id3055992"></a><a class="indexterm" name="id3056001"></a><p>
<code class="literal">INFORMATION_SCHEMA</code> provides access to database
metadata.
</p><p>
<em class="firstterm">Metadata</em> is data about the data, such as the
name of a database or table, the data type of a column, or access
privileges. Other terms that sometimes are used for this information
are <em class="firstterm">data dictionary</em> and <em class="firstterm">system
catalog</em>.
</p><p>
Here is an example:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT table_name, table_type, engine</code></strong>
-> <strong class="userinput"><code>FROM information_schema.tables</code></strong>
-> <strong class="userinput"><code>WHERE table_schema = 'db5'</code></strong>
-> <strong class="userinput"><code>ORDER BY table_name DESC;</code></strong>
+------------+------------+--------+
| table_name | table_type | engine |
+------------+------------+--------+
| v56 | VIEW | NULL |
| v3 | VIEW | NULL |
| v2 | VIEW | NULL |
| v | VIEW | NULL |
| tables | BASE TABLE | MyISAM |
| t7 | BASE TABLE | MyISAM |
| t3 | BASE TABLE | MyISAM |
| t2 | BASE TABLE | MyISAM |
| t | BASE TABLE | MyISAM |
| pk | BASE TABLE | InnoDB |
| loop | BASE TABLE | MyISAM |
| kurs | BASE TABLE | MyISAM |
| k | BASE TABLE | MyISAM |
| into | BASE TABLE | MyISAM |
| goto | BASE TABLE | MyISAM |
| fk2 | BASE TABLE | InnoDB |
| fk | BASE TABLE | InnoDB |
+------------+------------+--------+
17 rows in set (0.01 sec)
</pre><p>
Explanation: The statement requests a list of all the tables in
database <code class="literal">db5</code>, in reverse alphabetical order,
showing just three pieces of information: the name of the table, its
type, and its engine.
</p><p>
<code class="literal">INFORMATION_SCHEMA</code> is the information database,
the place that stores information about all the other databases that
the MySQL server maintains. Inside
<code class="literal">INFORMATION_SCHEMA</code> there are several read-only
tables. They are actually views, not base tables, so you won't
actually see any file associated with them.
</p><p>
Each MySQL user has the right to access these tables, but only the
rows in the tables that correspond to objects for which the user has
the proper access privileges.
</p><p>
<span class="bold"><strong>Advantages of
<code class="literal">SELECT</code></strong></span>
</p><p>
The <code class="literal">SELECT ... FROM INFORMATION_SCHEMA</code> statement
is intended as a more consistent way to provide access to the
information provided by the various <code class="literal">SHOW</code>
statements that MySQL supports (<code class="literal">SHOW DATABASES</code>,
<code class="literal">SHOW TABLES</code>, and so forth). Using
<code class="literal">SELECT</code> has these advantages, compared to
<code class="literal">SHOW</code>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
It conforms to Codd's rules. That is, all access is done on
tables.
</p></li><li><p>
Nobody needs to learn a new statement syntax. Because they
already know how <code class="literal">SELECT</code> works, they only need
to learn the object names.
</p></li><li><p>
The implementor need not worry about adding keywords.
</p></li><li><p>
There are millions of possible output variations, instead of
just one. This provides more flexibility for applications that
have varying requirements about what metadata they need.
</p></li><li><p>
Migration is easier because every other DBMS does it this way.
</p></li></ul></div><p>
However, because <code class="literal">SHOW</code> is popular with MySQL
employees and users, and because it might be confusing were it to
disappear, the advantages of conventional syntax are not a
sufficient reason to eliminate <code class="literal">SHOW</code>. In fact,
there are enhancements to <code class="literal">SHOW</code> in MySQL
5.0 as well. These are described in
<a href="information-schema.html#extended-show" title="20.2. Extensions to SHOW Statements">Section 20.2, “Extensions to <code class="literal">SHOW</code> Statements”</a>.
</p><p>
<span class="bold"><strong>Standards</strong></span>
</p><p>
The implementation for the <code class="literal">INFORMATION_SCHEMA</code>
table structures in MySQL follows the ANSI/ISO SQL:2003 standard
Part 11 <em class="citetitle">Schemata</em>. Our intent is approximate
compliance with SQL:2003 core feature F021 <em class="citetitle">Basic
information schema</em>.
</p><p>
Users of SQL Server 2000 (which also follows the standard) may
notice a strong similarity. However, MySQL has omitted many columns
that are not relevant for our implementation, and added columns that
are MySQL-specific. One such column is the <code class="literal">engine</code>
column in the <code class="literal">INFORMATION_SCHEMA.TABLES</code> table.
</p><p>
Although other DBMSs use a variety of names, like syscat or system,
the standard name is <code class="literal">INFORMATION_SCHEMA</code>.
</p><p>
In effect, we have a new database named
<code class="literal">INFORMATION_SCHEMA</code>, though there is never a need
to make a file by that name. It is possible to select
<code class="literal">INFORMATION_SCHEMA</code> as the default database with a
<code class="literal">USE</code> statement, but the only way to access the
contents of its tables is with <code class="literal">SELECT</code>. You cannot
insert into them, update them, or delete from them.
</p><p>
<span class="bold"><strong>Privileges</strong></span>
</p><p>
There is no difference between the current (<code class="literal">SHOW</code>)
privilege requirement and the <code class="literal">SELECT</code> requirement.
In either case, you have to have some privilege on an object in
order to see information about it.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="information-schema-tables"></a>20.1. <code class="literal">INFORMATION_SCHEMA</code> Tables</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="information-schema.html#schemata-table">20.1.1. The <code class="literal">INFORMATION_SCHEMA SCHEMATA</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#tables-table">20.1.2. The <code class="literal">INFORMATION_SCHEMA TABLES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#columns-table">20.1.3. The <code class="literal">INFORMATION_SCHEMA COLUMNS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#statistics-table">20.1.4. The <code class="literal">INFORMATION_SCHEMA STATISTICS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#user-privileges-table">20.1.5. The <code class="literal">INFORMATION_SCHEMA USER_PRIVILEGES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#schema-privileges-table">20.1.6. The <code class="literal">INFORMATION_SCHEMA SCHEMA_PRIVILEGES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#table-privileges-table">20.1.7. The <code class="literal">INFORMATION_SCHEMA TABLE_PRIVILEGES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#column-privileges-table">20.1.8. The <code class="literal">INFORMATION_SCHEMA COLUMN_PRIVILEGES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#character-sets-table">20.1.9. The <code class="literal">INFORMATION_SCHEMA CHARACTER_SETS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#collations-table">20.1.10. The <code class="literal">INFORMATION_SCHEMA COLLATIONS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#collation-character-set-applicability-table">20.1.11. The <code class="literal">INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#table-constraints-table">20.1.12. The <code class="literal">INFORMATION_SCHEMA TABLE_CONSTRAINTS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#key-column-usage-table">20.1.13. The <code class="literal">INFORMATION_SCHEMA KEY_COLUMN_USAGE</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#routines-table">20.1.14. The <code class="literal">INFORMATION_SCHEMA ROUTINES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#views-table">20.1.15. The <code class="literal">INFORMATION_SCHEMA VIEWS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#triggers-table">20.1.16. The <code class="literal">INFORMATION_SCHEMA TRIGGERS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#other-information-schema-tables">20.1.17. Other <code class="literal">INFORMATION_SCHEMA</code> Tables</a></span></dt></dl></div><a class="indexterm" name="id3056348"></a><p>
<span class="bold"><strong>Explanation of following sections</strong></span>
</p><p>
In the following sections, we take the tables and columns that are
in <code class="literal">INFORMATION_SCHEMA</code>. For each column, there
are three pieces of information:
</p><div class="itemizedlist"><ul type="disc"><li><p>
“<span class="quote">Standard Name</span>” indicates the standard SQL name
for the column.
</p></li><li><p>
“<span class="quote"><code class="literal">SHOW</code> name</span>” indicates what the
equivalent field name is in the closest
<code class="literal">SHOW</code> statement, if any.
</p></li><li><p>
“<span class="quote">Remarks</span>” provides additional information where
applicable.
</p></li></ul></div><p>
To avoid using any name that is reserved in the standard or in
DB2, SQL Server, or Oracle, we changed the names of columns marked
<span class="bold"><strong>MySQL extension</strong></span>. (For example, we
changed <code class="literal">COLLATION</code> to
<code class="literal">TABLE_COLLATION</code> in the
<code class="literal">TABLES</code> table.) See the list of reserved words
near the end of this article:
<a href="http://www.dbazine.com/gulutzan5.shtml" target="_top">http://www.dbazine.com/gulutzan5.shtml</a>.
</p><p>
The definition for character columns (for example,
<code class="literal">TABLES.TABLE_NAME</code>), is generally
<code class="literal">VARCHAR(<em class="replaceable"><code>N</code></em>) CHARACTER SET
utf8</code> where <em class="replaceable"><code>N</code></em> is at least 64.
</p><p>
Each section indicates what <code class="literal">SHOW</code> statement is
equivalent to a <code class="literal">SELECT</code> that retrieves
information from <code class="literal">INFORMATION_SCHEMA</code>, or else
that there is no such equivalent statement.
</p><p>
<span class="bold"><strong>Note</strong></span>: At present, there are some
missing columns and some columns out of order. We are working on
this and intend to update the documentation as changes are made.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="schemata-table"></a>20.1.1. The <code class="literal">INFORMATION_SCHEMA SCHEMATA</code> Table</h3></div></div></div><a class="indexterm" name="id3056521"></a><p>
A schema is a database, so the <code class="literal">SCHEMATA</code> table
provides information about databases.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Standard Name</strong></span></td><td><span class="bold"><strong><code class="literal">SHOW</code> name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">CATALOG_NAME</code></td><td>-</td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">SCHEMA_NAME</code></td><td> </td><td>Database</td></tr><tr><td><code class="literal">DEFAULT_CHARACTER_SET_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">DEFAULT_COLLATION_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">SQL_PATH</code></td><td> </td><td><code class="literal">NULL</code></td></tr></tbody></table></div><p>
Notes:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<span class="bold"><strong>Note</strong></span>: The value of the
<code class="literal">SQL_PATH</code> column is always
<code class="literal">NULL</code>.
</p></li><li><p>
<code class="literal">DEFAULT_COLLATION_NAME</code> was added in MySQL
5.0.6.
</p></li></ul></div><p>
The following statements are equivalent:
</p><pre class="programlisting">SELECT SCHEMA_NAME AS `Database
FROM INFORMATION_SCHEMA.SCHEMATA
[WHERE SCHEMA_NAME LIKE 'wild']
SHOW DATABASES
[LIKE 'wild']
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="tables-table"></a>20.1.2. The <code class="literal">INFORMATION_SCHEMA TABLES</code> Table</h3></div></div></div><a class="indexterm" name="id3056724"></a><p>
The <code class="literal">TABLES</code> table provides information about
tables in databases.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Standard Name</strong></span></td><td><span class="bold"><strong><code class="literal">SHOW</code> name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td><code class="literal">Table_</code>...</td><td> </td></tr><tr><td><code class="literal">TABLE_NAME</code></td><td><code class="literal">Table_</code>...</td><td> </td></tr><tr><td><code class="literal">TABLE_TYPE</code></td><td> </td><td> </td></tr><tr><td><code class="literal">ENGINE</code></td><td><code class="literal">Engine</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">VERSION</code></td><td><code class="literal">Version</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">ROW_FORMAT</code></td><td><code class="literal">Row_format</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">TABLE_ROWS</code></td><td><code class="literal">Rows</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">AVG_ROW_LENGTH</code></td><td><code class="literal">Avg_row_length</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">DATA_LENGTH</code></td><td><code class="literal">Data_length</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">MAX_DATA_LENGTH</code></td><td><code class="literal">Max_data_length</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">INDEX_LENGTH</code></td><td><code class="literal">Index_length</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">DATA_FREE</code></td><td><code class="literal">Data_free</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">AUTO_INCREMENT</code></td><td><code class="literal">Auto_increment</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">CREATE_TIME</code></td><td><code class="literal">Create_time</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">UPDATE_TIME</code></td><td><code class="literal">Update_time</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">CHECK_TIME</code></td><td><code class="literal">Check_time</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">TABLE_COLLATION</code></td><td><code class="literal">Collation</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">CHECKSUM</code></td><td><code class="literal">Checksum</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">CREATE_OPTIONS</code></td><td><code class="literal">Create_options</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">TABLE_COMMENT</code></td><td><code class="literal">Comment</code></td><td>MySQL extension</td></tr></tbody></table></div><p>
Notes:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">TABLE_SCHEMA</code> and
<code class="literal">TABLE_NAME</code> are a single field in a
<code class="literal">SHOW</code> display, for example
<code class="literal">Table_in_db1</code>.
</p></li><li><p>
<code class="literal">TABLE_TYPE</code> should be <code class="literal">BASE
TABLE</code> or <code class="literal">VIEW</code>. If table is
temporary, then <code class="literal">TABLE_TYPE</code> =
<code class="literal">TEMPORARY</code>. (There are no temporary views,
so this is not ambiguous.)
</p></li><li><p>
The <code class="literal">TABLE_ROWS</code> column is
<code class="literal">NULL</code> if the table is in the
<code class="literal">INFORMATION_SCHEMA</code> database. For
<code class="literal">InnoDB</code> tables, the row count is only a
rough estimate used in SQL optimization.
</p></li><li><p>
We have nothing for the table's default character set.
<code class="literal">TABLE_COLLATION</code> is close, because
collation names begin with a character set name.
</p></li></ul></div><p>
The following statements are equivalent:
</p><pre class="programlisting">SELECT table_name FROM INFORMATION_SCHEMA.TABLES
[WHERE table_schema = 'db_name']
[WHERE|AND table_name LIKE 'wild']
SHOW TABLES
[FROM db_name]
[LIKE 'wild']
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="columns-table"></a>20.1.3. The <code class="literal">INFORMATION_SCHEMA COLUMNS</code> Table</h3></div></div></div><a class="indexterm" name="id3057253"></a><p>
The <code class="literal">COLUMNS</code> table provides information about
columns in tables.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Standard Name</strong></span></td><td><span class="bold"><strong><code class="literal">SHOW</code> name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TABLE_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">COLUMN_NAME</code></td><td><code class="literal">Field</code></td><td> </td></tr><tr><td><code class="literal">ORDINAL_POSITION</code></td><td> </td><td>see notes</td></tr><tr><td><code class="literal">COLUMN_DEFAULT</code></td><td><code class="literal">Default</code></td><td> </td></tr><tr><td><code class="literal">IS_NULLABLE</code></td><td><code class="literal">Null</code></td><td> </td></tr><tr><td><code class="literal">DATA_TYPE</code></td><td><code class="literal">Type</code></td><td> </td></tr><tr><td><code class="literal">CHARACTER_MAXIMUM_LENGTH</code></td><td><code class="literal">Type</code></td><td> </td></tr><tr><td><code class="literal">CHARACTER_OCTET_LENGTH</code></td><td> </td><td> </td></tr><tr><td><code class="literal">NUMERIC_PRECISION</code></td><td><code class="literal">Type</code></td><td> </td></tr><tr><td><code class="literal">NUMERIC_SCALE</code></td><td><code class="literal">Type</code></td><td> </td></tr><tr><td><code class="literal">CHARACTER_SET_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">COLLATION_NAME</code></td><td><code class="literal">Collation</code></td><td> </td></tr><tr><td><code class="literal">COLUMN_TYPE</code></td><td><code class="literal">Type</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">COLUMN_KEY</code></td><td><code class="literal">Key</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">EXTRA</code></td><td><code class="literal">Extra</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">COLUMN_COMMENT</code></td><td><code class="literal">Comment</code></td><td>MySQL extension</td></tr></tbody></table></div><p>
Notes:
</p><div class="itemizedlist"><ul type="disc"><li><p>
In <code class="literal">SHOW</code>, the <code class="literal">Type</code>
display includes values from several different
<code class="literal">COLUMNS</code> columns.
</p></li><li><p>
<code class="literal">ORDINAL_POSITION</code> is necessary because you
might someday want to say <code class="literal">ORDER BY
ORDINAL_POSITION</code>. Unlike <code class="literal">SHOW</code>,
<code class="literal">SELECT</code> does not have automatic ordering.
</p></li><li><p>
<code class="literal">CHARACTER_OCTET_LENGTH</code> should be the same
as <code class="literal">CHARACTER_MAXIMUM_LENGTH</code>, except for
multi-byte character sets.
</p></li><li><p>
<code class="literal">CHARACTER_SET_NAME</code> can be derived from
<code class="literal">Collation</code>. For example, if you say
<code class="literal">SHOW FULL COLUMNS FROM t</code>, and you see in
the <code class="literal">Collation</code> column a value of
<code class="literal">latin1_swedish_ci</code>, the character set is
what's before the first underscore:
<code class="literal">latin1</code>.
</p></li></ul></div><p>
The following statements are nearly equivalent:
</p><pre class="programlisting">SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tbl_name'
[AND table_schema = 'db_name']
[AND column_name LIKE 'wild']
SHOW COLUMNS
FROM tbl_name
[FROM db_name]
[LIKE wild]
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="statistics-table"></a>20.1.4. The <code class="literal">INFORMATION_SCHEMA STATISTICS</code> Table</h3></div></div></div><a class="indexterm" name="id3057706"></a><p>
The <code class="literal">STATISTICS</code> table provides information
about table indexes.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Standard Name</strong></span></td><td><span class="bold"><strong><code class="literal">SHOW</code> name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td> </td><td>= Database</td></tr><tr><td><code class="literal">TABLE_NAME</code></td><td><code class="literal">Table</code></td><td> </td></tr><tr><td><code class="literal">NON_UNIQUE</code></td><td><code class="literal">Non_unique</code></td><td> </td></tr><tr><td><code class="literal">INDEX_SCHEMA</code></td><td> </td><td>= Database</td></tr><tr><td><code class="literal">INDEX_NAME</code></td><td><code class="literal">Key_name</code></td><td> </td></tr><tr><td><code class="literal">SEQ_IN_INDEX</code></td><td><code class="literal">Seq_in_index</code></td><td> </td></tr><tr><td><code class="literal">COLUMN_NAME</code></td><td><code class="literal">Column_name</code></td><td> </td></tr><tr><td><code class="literal">COLLATION</code></td><td><code class="literal">Collation</code></td><td> </td></tr><tr><td><code class="literal">CARDINALITY</code></td><td><code class="literal">Cardinality</code></td><td> </td></tr><tr><td><code class="literal">SUB_PART</code></td><td><code class="literal">Sub_part</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">PACKED</code></td><td><code class="literal">Packed</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">NULLABLE</code></td><td><code class="literal">Null</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">INDEX_TYPE</code></td><td><code class="literal">Index_type</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">COMMENT</code></td><td><code class="literal">Comment</code></td><td>MySQL extension</td></tr></tbody></table></div><p>
Notes:
</p><div class="itemizedlist"><ul type="disc"><li><p>
There is no standard table for indexes. The preceding list
is similar to what SQL Server 2000 returns for
<code class="literal">sp_statistics</code>, except that we replaced
the name <code class="literal">QUALIFIER</code> with
<code class="literal">CATALOG</code> and we replaced the name
<code class="literal">OWNER</code> with <code class="literal">SCHEMA</code>.
</p><p>
Clearly, the preceding table and the output from
<code class="literal">SHOW INDEX</code> are derived from the same
parent. So the correlation is already close.
</p></li></ul></div><p>
The following statements are equivalent:
</p><pre class="programlisting">SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_name = 'tbl_name'
[AND table_schema = 'db_name']
SHOW INDEX
FROM tbl_name
[FROM db_name]
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="user-privileges-table"></a>20.1.5. The <code class="literal">INFORMATION_SCHEMA USER_PRIVILEGES</code> Table</h3></div></div></div><a class="indexterm" name="id3058070"></a><p>
The <code class="literal">USER_PRIVILEGES</code> table provides
information about global privileges. This information comes from
the <code class="literal">mysql.user</code> grant table.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Standard Name</strong></span></td><td><span class="bold"><strong><code class="literal">SHOW</code> name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">GRANTEE</code></td><td> </td><td>e.g. 'user'@'host'</td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">PRIVILEGE_TYPE</code></td><td> </td><td> </td></tr><tr><td><code class="literal">IS_GRANTABLE</code></td><td> </td><td> </td></tr></tbody></table></div><p>
Notes:
</p><div class="itemizedlist"><ul type="disc"><li><p>
This is a non-standard table. It takes its values from the
<code class="literal">mysql.user</code> table.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="schema-privileges-table"></a>20.1.6. The <code class="literal">INFORMATION_SCHEMA SCHEMA_PRIVILEGES</code> Table</h3></div></div></div><a class="indexterm" name="id3058234"></a><p>
The <code class="literal">SCHEMA_PRIVILEGES</code> table provides
information about schema (database) privileges. This information
comes from the <code class="literal">mysql.db</code> grant table.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Standard Name</strong></span></td><td><span class="bold"><strong><code class="literal">SHOW</code> name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">GRANTEE</code></td><td> </td><td>e.g. 'user'@'host'</td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">PRIVILEGE_TYPE</code></td><td> </td><td> </td></tr><tr><td><code class="literal">IS_GRANTABLE</code></td><td> </td><td> </td></tr></tbody></table></div><p>
Notes:
</p><div class="itemizedlist"><ul type="disc"><li><p>
This is a non-standard table. It takes its values from the
<code class="literal">mysql.db</code> table.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="table-privileges-table"></a>20.1.7. The <code class="literal">INFORMATION_SCHEMA TABLE_PRIVILEGES</code> Table</h3></div></div></div><a class="indexterm" name="id3058409"></a><p>
The <code class="literal">TABLE_PRIVILEGES</code> table provides
information about table privileges. This information comes from
the <code class="literal">mysql.tables_priv</code> grant table.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Standard Name</strong></span></td><td><span class="bold"><strong><code class="literal">SHOW</code> name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">GRANTEE</code></td><td> </td><td>e.g. 'user'@'host'</td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TABLE_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">PRIVILEGE_TYPE</code></td><td> </td><td> </td></tr><tr><td><code class="literal">IS_GRANTABLE</code></td><td> </td><td> </td></tr></tbody></table></div><p>
The following statements are <span class="emphasis"><em>not</em></span>
equivalent:
</p><pre class="programlisting">SELECT ... FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
SHOW GRANTS ...
</pre><p>
<code class="literal">PRIVILEGE_TYPE</code> can contain one (and only one)
of these values: <code class="literal">SELECT</code>,
<code class="literal">INSERT</code>, <code class="literal">UPDATE</code>,
<code class="literal">REFERENCES</code>, <code class="literal">ALTER</code>,
<code class="literal">INDEX</code>, <code class="literal">DROP</code>,
<code class="literal">CREATE VIEW</code>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="column-privileges-table"></a>20.1.8. The <code class="literal">INFORMATION_SCHEMA COLUMN_PRIVILEGES</code> Table</h3></div></div></div><a class="indexterm" name="id3058627"></a><p>
The <code class="literal">COLUMN_PRIVILEGES</code> table provides
information about column privileges. This information comes from
the <code class="literal">mysql.columns_priv</code> grant table.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Standard Name</strong></span></td><td><span class="bold"><strong><code class="literal">SHOW</code> name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">GRANTEE</code></td><td> </td><td>e.g. 'user'@'host'</td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TABLE_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">COLUMN_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">PRIVILEGE_TYPE</code></td><td> </td><td> </td></tr><tr><td><code class="literal">IS_GRANTABLE</code></td><td> </td><td> </td></tr></tbody></table></div><p>
Notes:
</p><div class="itemizedlist"><ul type="disc"><li><p>
In the output from <code class="literal">SHOW FULL COLUMNS</code>, the
privileges are all in one field and in lowercase, for
example, <code class="literal">select,insert,update,references</code>.
In <code class="literal">COLUMN_PRIVILEGES</code>, there is one row
per privilege, and it's uppercase.
</p></li><li><p>
<code class="literal">PRIVILEGE_TYPE</code> can contain one (and only
one) of these values: <code class="literal">SELECT</code>,
<code class="literal">INSERT</code>, <code class="literal">UPDATE</code>,
<code class="literal">REFERENCES</code>.
</p></li><li><p>
If the user has <code class="literal">GRANT OPTION</code> privilege,
then <code class="literal">IS_GRANTABLE</code> should be
<code class="literal">YES</code>. Otherwise,
<code class="literal">IS_GRANTABLE</code> should be
<code class="literal">NO</code>. The output does not list
<code class="literal">GRANT OPTION</code> as a separate privilege.
</p></li></ul></div><p>
The following statements are <span class="emphasis"><em>not</em></span>
equivalent:
</p><pre class="programlisting">SELECT ... FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
SHOW GRANTS ...
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="character-sets-table"></a>20.1.9. The <code class="literal">INFORMATION_SCHEMA CHARACTER_SETS</code> Table</h3></div></div></div><a class="indexterm" name="id3058906"></a><p>
The <code class="literal">CHARACTER_SETS</code> table provides information
about available character sets.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Standard Name</strong></span></td><td><span class="bold"><strong><code class="literal">SHOW</code> name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">CHARACTER_SET_NAME</code></td><td><code class="literal">Charset</code></td><td> </td></tr><tr><td><code class="literal">DEFAULT_COLLATE_NAME</code></td><td><code class="literal">Default collation</code></td><td> </td></tr><tr><td><code class="literal">DESCRIPION</code></td><td><code class="literal">Description</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">MAXLEN</code></td><td><code class="literal">Maxlen</code></td><td>MySQL extension</td></tr></tbody></table></div><p>
Notes:
</p><div class="itemizedlist"><ul type="disc"><li><p>
We have added two non-standard columns corresponding to the
<code class="literal">Description</code> and <code class="literal">Maxlen</code>
columns in the output from <code class="literal">SHOW CHARACTER
SET</code>.
</p></li></ul></div><p>
The following statements are equivalent:
</p><pre class="programlisting">SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS
[WHERE name LIKE 'wild']
SHOW CHARACTER SET
[LIKE 'wild']
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="collations-table"></a>20.1.10. The <code class="literal">INFORMATION_SCHEMA COLLATIONS</code> Table</h3></div></div></div><a class="indexterm" name="id3059091"></a><p>
The <code class="literal">COLLATIONS</code> table provides information
about collations for each character set.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Standard Name</strong></span></td><td><span class="bold"><strong><code class="literal">SHOW</code> name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">COLLATION_NAME</code></td><td><code class="literal">Collation</code></td><td> </td></tr></tbody></table></div><p>
Notes:
</p><div class="itemizedlist"><ul type="disc"><li><p>
We have added five non-standard columns corresponding to the
<code class="literal">Charset</code>, <code class="literal">Id</code>,
<code class="literal">Default</code>, <code class="literal">Compiled</code>, and
<code class="literal">Sortlen</code> columns in the output from
<code class="literal">SHOW COLLATION</code>.
</p></li></ul></div><p>
The following statements are equivalent:
</p><pre class="programlisting">SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS
[WHERE collation_name LIKE 'wild']
SHOW COLLATION
[LIKE 'wild']
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="collation-character-set-applicability-table"></a>20.1.11. The <code class="literal">INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY</code> Table</h3></div></div></div><a class="indexterm" name="id3059241"></a><p>
The <code class="literal">COLLATION_CHARACTER_SET_APPLICABILITY</code>
table indicates what character set is applicable for what
collation. The columns are equivalent to the first two display
fields that we get from <code class="literal">SHOW COLLATION</code>.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Standard Name</strong></span></td><td><span class="bold"><strong><code class="literal">SHOW</code> name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">COLLATION_NAME</code></td><td><code class="literal">Collation</code></td><td> </td></tr><tr><td><code class="literal">CHARACTER_SET_NAME</code></td><td><code class="literal">Charset</code></td><td> </td></tr></tbody></table></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="table-constraints-table"></a>20.1.12. The <code class="literal">INFORMATION_SCHEMA TABLE_CONSTRAINTS</code> Table</h3></div></div></div><a class="indexterm" name="id3059364"></a><p>
The <code class="literal">TABLE_CONSTRAINTS</code> table describes which
tables have constraints.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Standard Name</strong></span></td><td><span class="bold"><strong><code class="literal">SHOW</code> name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">CONSTRAINT_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">CONSTRAINT_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">CONSTRAINT_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TABLE_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">CONSTRAINT_TYPE</code></td><td> </td><td> </td></tr></tbody></table></div><p>
Notes:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <code class="literal">CONSTRAINT_TYPE</code> value can be
<code class="literal">UNIQUE</code>, <code class="literal">PRIMARY KEY</code>,
or <code class="literal">FOREIGN KEY</code>.
</p></li><li><p>
The <code class="literal">UNIQUE</code> and <code class="literal">PRIMARY
KEY</code> information is about the same as what you get
from the <code class="literal">Key_name</code> field in the output
from <code class="literal">SHOW INDEX</code> when the
<code class="literal">Non_unique</code> field is <code class="literal">0</code>.
</p></li><li><p>
The <code class="literal">CONSTRAINT_TYPE</code> column can contain
one of these values: <code class="literal">UNIQUE</code>,
<code class="literal">PRIMARY KEY</code>, <code class="literal">FOREIGN
KEY</code>, <code class="literal">CHECK</code>. This is a
<code class="literal">CHAR</code> (not <code class="literal">ENUM</code>)
column. The <code class="literal">CHECK</code> value is not available
until we support <code class="literal">CHECK</code>.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="key-column-usage-table"></a>20.1.13. The <code class="literal">INFORMATION_SCHEMA KEY_COLUMN_USAGE</code> Table</h3></div></div></div><a class="indexterm" name="id3059629"></a><p>
The <code class="literal">KEY_COLUMN_USAGE</code> table describes which
key columns have constraints.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Standard Name</strong></span></td><td><span class="bold"><strong><code class="literal">SHOW</code> name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">CONSTRAINT_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">CONSTRAINT_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">CONSTRAINT_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TABLE_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">COLUMN_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">ORDINAL_POSITION</code></td><td> </td><td> </td></tr><tr><td><code class="literal">POSITION_IN_UNIQUE_CONSTRAINT</code></td><td> </td><td> </td></tr><tr><td><code class="literal">REFERENCED_TABLE_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">REFERENCED_TABLE_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">REFERENCED_COLUMN_NAME</code></td><td> </td><td> </td></tr></tbody></table></div><p>
Notes:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If the constraint is a foreign key, then this is the column
of the foreign key, not the column that the foreign key
references.
</p></li><li><p>
The value of <code class="literal">ORDINAL_POSITION</code> is the
column's position within the constraint, not the column's
position within the table. Column positions are numbered
beginning with 1.
</p></li><li><p>
The value of
<code class="literal">POSITION_IN_UNIQUE_CONSTRAINT</code> is
<code class="literal">NULL</code> for unique and primary-key
constraints. For foreign-key constraints, it is the ordinal
position in key of the table that is being referenced.
</p><p>
For example, suppose that there are two tables name
<code class="literal">t1</code> and <code class="literal">t3</code> that have
the following definitions:
</p><pre class="programlisting">CREATE TABLE t1
(
s1 INT,
s2 INT,
s3 INT,
PRIMARY KEY(s3)
) ENGINE=InnoDB;
CREATE TABLE t3
(
s1 INT,
s2 INT,
s3 INT,
KEY(s1),
CONSTRAINT CO FOREIGN KEY (s2) REFERENCES t1(s3)
) ENGINE=InnoDB;
</pre><p>
For those two tables, the
<code class="literal">KEY_COLUMN_USAGE</code> table has two rows:
</p><div class="itemizedlist"><ul type="circle"><li><p>
One row with
<code class="literal">CONSTRAINT_NAME</code>='PRIMARY',
<code class="literal">TABLE_NAME</code>='t1',
<code class="literal">COLUMN_NAME</code>='s3',
<code class="literal">ORDINAL_POSITION</code>=1,
<code class="literal">POSITION_IN_UNIQUE_CONSTRAINT</code>=NULL.
</p></li><li><p>
One row with <code class="literal">CONSTRAINT_NAME</code>='CO',
<code class="literal">TABLE_NAME</code>='t3',
<code class="literal">COLUMN_NAME</code>='s2',
<code class="literal">ORDINAL_POSITION</code>=1,
<code class="literal">POSITION_IN_UNIQUE_CONSTRAINT</code>=1.
</p></li></ul></div></li><li><p>
<code class="literal">REFERENCED_TABLE_SCHEMA</code>,
<code class="literal">REFERENCED_TABLE_NAME</code>, and
<code class="literal">REFERENCED_COLUMN_NAME</code> were added in
MySQL 5.0.6.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="routines-table"></a>20.1.14. The <code class="literal">INFORMATION_SCHEMA ROUTINES</code> Table</h3></div></div></div><a class="indexterm" name="id3060019"></a><p>
The <code class="literal">ROUTINES</code> table provides information about
stored routines (both procedures and functions). The
<code class="literal">ROUTINES</code> table does not include user-defined
functions (UDFs) at this time.
</p><p>
The column named “<span class="quote"><code class="literal">mysql.proc</code>
name</span>” indicates the <code class="literal">mysql.proc</code> table
column that corresponds to the
<code class="literal">INFORMATION_SCHEMA.ROUTINES</code> table column, if
any.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Standard Name</strong></span></td><td><span class="bold"><strong><code class="literal">mysql.proc</code> name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">SPECIFIC_NAME</code></td><td><code class="literal">specific_name</code></td><td> </td></tr><tr><td><code class="literal">ROUTINE_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">ROUTINE_SCHEMA</code></td><td><code class="literal">db</code></td><td> </td></tr><tr><td><code class="literal">ROUTINE_NAME</code></td><td><code class="literal">name</code></td><td> </td></tr><tr><td><code class="literal">ROUTINE_TYPE</code></td><td><code class="literal">type</code></td><td><code class="literal">{PROCEDURE|FUNCTION}</code></td></tr><tr><td><code class="literal">DTD_IDENTIFIER</code></td><td> </td><td>(data type descriptor)</td></tr><tr><td><code class="literal">ROUTINE_BODY</code></td><td> </td><td><code class="literal">SQL</code></td></tr><tr><td><code class="literal">ROUTINE_DEFINITION</code></td><td><code class="literal">body</code></td><td> </td></tr><tr><td><code class="literal">EXTERNAL_NAME</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">EXTERNAL_LANGUAGE</code></td><td><code class="literal">language</code></td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">PARAMETER_STYLE</code></td><td> </td><td><code class="literal">SQL</code></td></tr><tr><td><code class="literal">IS_DETERMINISTIC</code></td><td><code class="literal">is_deterministic</code></td><td> </td></tr><tr><td><code class="literal">SQL_DATA_ACCESS</code></td><td><code class="literal">sql_data_access</code></td><td> </td></tr><tr><td><code class="literal">SQL_PATH</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">SECURITY_TYPE</code></td><td><code class="literal">security_type</code></td><td> </td></tr><tr><td><code class="literal">CREATED</code></td><td><code class="literal">created</code></td><td> </td></tr><tr><td><code class="literal">LAST_ALTERED</code></td><td><code class="literal">modified</code></td><td> </td></tr><tr><td><code class="literal">SQL_MODE</code></td><td><code class="literal">sql_mode</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">ROUTINE_COMMENT</code></td><td><code class="literal">comment</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">DEFINER</code></td><td><code class="literal">definer</code></td><td>MySQL extension</td></tr></tbody></table></div><p>
Notes:
</p><div class="itemizedlist"><ul type="disc"><li><p>
MySQL calculates <code class="literal">EXTERNAL_LANGUAGE</code> thus:
</p><div class="itemizedlist"><ul type="circle"><li><p>
If <code class="literal">mysql.proc.language='SQL'</code>, then
<code class="literal">EXTERNAL_LANGUAGE</code> is
<code class="literal">NULL</code>
</p></li><li><p>
Otherwise, <code class="literal">EXTERNAL_LANGUAGE</code> is
what's in <code class="literal">mysql.proc.language</code>.
However, we don't have external languages yet, so it's
always <code class="literal">NULL</code>.
</p></li></ul></div></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="views-table"></a>20.1.15. The <code class="literal">INFORMATION_SCHEMA VIEWS</code> Table</h3></div></div></div><a class="indexterm" name="id3060490"></a><p>
The <code class="literal">VIEWS</code> table provides information about
views in databases.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Standard Name</strong></span></td><td><span class="bold"><strong><code class="literal">SHOW</code> name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TABLE_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">VIEW_DEFINITION</code></td><td> </td><td> </td></tr><tr><td><code class="literal">CHECK_OPTION</code></td><td> </td><td> </td></tr><tr><td><code class="literal">IS_UPDATABLE</code></td><td> </td><td> </td></tr><tr><td><code class="literal">DEFINER</code></td><td> </td><td> </td></tr><tr><td><code class="literal">SECURITY_TYPE</code></td><td> </td><td> </td></tr></tbody></table></div><p>
Notes:
</p><div class="itemizedlist"><ul type="disc"><li><p>
There is a new privilege, <code class="literal">SHOW VIEW</code>,
without which you cannot see the <code class="literal">VIEWS</code>
table.
</p></li><li><p>
The <code class="literal">VIEW_DEFINITION</code> column has most of
what you see in the <code class="literal">Create Table</code> field
that <code class="literal">SHOW CREATE VIEW</code> produces. Skip the
words before <code class="literal">SELECT</code> and skip the words
<code class="literal">WITH CHECK OPTION</code>. For example, if the
original statement was:
</p><pre class="programlisting">CREATE VIEW v AS
SELECT s2,s1 FROM t
WHERE s1 > 5
ORDER BY s1
WITH CHECK OPTION;
</pre><p>
then the view definition is:
</p><pre class="programlisting">SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1
</pre></li><li><p>
The <code class="literal">CHECK_OPTION</code> column always has a
value of <code class="literal">NONE</code>.
</p></li><li><p>
The <code class="literal">IS_UPDATABLE</code> column is
<code class="literal">YES</code> if the view is updatable,
<code class="literal">NO</code> if the view is not updatable.
</p></li><li><p>
The <code class="literal">DEFINER</code> and
<code class="literal">SECURITY_TYPE</code> columns were added in MySQL
5.0.14. <code class="literal">DEFINER</code> indicates who defined the
view. <code class="literal">SECURITY_TYPE</code> has a value of
<code class="literal">DEFINER</code> or <code class="literal">INVOKER</code>.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="triggers-table"></a>20.1.16. The <code class="literal">INFORMATION_SCHEMA TRIGGERS</code> Table</h3></div></div></div><a class="indexterm" name="id3060807"></a><p>
The <code class="literal">TRIGGERS</code> table provides information about
triggers.
</p><p>
This table was first implemented in MySQL 5.0.10.
</p><p>
You must have the <code class="literal">SUPER</code> privilege to view
this table.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Standard Name</strong></span></td><td><span class="bold"><strong><code class="literal">SHOW</code> name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">TRIGGER_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">TRIGGER_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TRIGGER_NAME</code></td><td><code class="literal">Trigger</code></td><td> </td></tr><tr><td><code class="literal">EVENT_MANIPULATION</code></td><td><code class="literal">Event</code></td><td> </td></tr><tr><td><code class="literal">EVENT_OBJECT_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">EVENT_OBJECT_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">EVENT_OBJECT_TABLE</code></td><td><code class="literal">Table</code></td><td> </td></tr><tr><td><code class="literal">ACTION_ORDER</code></td><td> </td><td><code class="literal">0</code></td></tr><tr><td><code class="literal">ACTION_CONDITION</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">ACTION_STATEMENT</code></td><td><code class="literal">Statement</code></td><td> </td></tr><tr><td><code class="literal">ACTION_ORIENTATION</code></td><td> </td><td><code class="literal">ROW</code></td></tr><tr><td><code class="literal">ACTION_TIMING</code></td><td><code class="literal">Timing</code></td><td> </td></tr><tr><td><code class="literal">ACTION_REFERENCE_OLD_TABLE</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">ACTION_REFERENCE_NEW_TABLE</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">ACTION_REFERENCE_OLD_ROW</code></td><td> </td><td><code class="literal">OLD</code></td></tr><tr><td><code class="literal">ACTION_REFERENCE_NEW_ROW</code></td><td> </td><td><code class="literal">NEW</code></td></tr><tr><td><code class="literal">CREATED</code></td><td> </td><td><code class="literal">NULL</code> (<code class="literal">0</code>)</td></tr><tr><td><code class="literal">SQL_MODE</code></td><td> </td><td> </td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <code class="literal">TRIGGER_SCHEMA</code> and
<code class="literal">TRIGGER_NAME</code> columns contain the name of
the database in which the trigger occurs, and the trigger
name, respectively.
</p></li><li><p>
The <code class="literal">EVENT_MANIPULATION</code> column contains
one of the values <code class="literal">'INSERT'</code>,
<code class="literal">'DELETE'</code>, or <code class="literal">'UPDATE'</code>.
</p></li><li><p>
As noted in <a href="triggers.html" title="Chapter 18. Triggers">Chapter 18, <i>Triggers</i></a>, every trigger is
associated with exactly one table. The
<code class="literal">EVENT_OBJECT_SCHEMA</code> and
<code class="literal">EVENT_OBJECT_TABLE</code> columns contain the
database in which this table occurs, and the table's name.
</p></li><li><p>
The <code class="literal">ACTION_ORDER</code> statement contains the
ordinal position of the trigger's action within the list of
all similar triggers on the same table. Currently, this
value is always <code class="literal">0</code>, because it is not
possible to have more than one trigger with the same
<code class="literal">EVENT_MANIPULATION</code> and
<code class="literal">ACTION_TIMING</code> on the same table.
</p></li><li><p>
The <code class="literal">ACTION_STATEMENT</code> column contains the
statement to be executed when the trigger is invoked. This
is the same as the text displayed in the
<code class="literal">Statement</code> column of the output from
<code class="literal">SHOW TRIGGERS</code>. Note that this text uses
UTF-8 encoding.
</p></li><li><p>
The <code class="literal">ACTION_ORIENTATION</code> column always
contains the value <code class="literal">'ROW'</code>.
</p></li><li><p>
The <code class="literal">ACTION_TIMING</code> column contains one of
the two values <code class="literal">'BEFORE'</code> or
<code class="literal">'AFTER'</code>.
</p></li><li><p>
The columns <code class="literal">ACTION_REFERENCE_OLD_ROW</code> and
<code class="literal">ACTION_REFERENCE_NEW_ROW</code> contain the old
and new column identifiers, respectively. This means that
<code class="literal">ACTION_REFERENCE_OLD_ROW</code> always contains
the value <code class="literal">'OLD'</code> and
<code class="literal">ACTION_REFERENCE_NEW_ROW</code> always contains
the value <code class="literal">'NEW'</code>.
</p></li><li><p>
The <code class="literal">SQL_MODE</code> column shows the server SQL
mode that was in effect at the time when the trigger was
created (and thus which remains in effect for this trigger
whenever it is invoked, <span class="emphasis"><em>regardless of the current
server SQL mode</em></span>). The possible range of values
for this column is the same as that of the
<code class="literal">sql_mode</code> system variable. See
<a href="database-administration.html#server-sql-mode" title="5.3.2. The Server SQL Mode">Section 5.3.2, “The Server SQL Mode”</a>.
</p></li><li><p>
The following columns currently always contain
<code class="literal">NULL</code>: <code class="literal">TRIGGER_CATALOG</code>,
<code class="literal">EVENT_OBJECT_CATALOG</code>,
<code class="literal">ACTION_CONDITION</code>,
<code class="literal">ACTION_REFERENCE_OLD_TABLE</code>,
<code class="literal">ACTION_REFERENCE_NEW_TABLE</code>, and
<code class="literal">CREATED</code>.
</p></li></ul></div><p>
Example, using the <code class="literal">ins_sum</code> trigger defined in
<a href="triggers.html#using-triggers" title="18.3. Using Triggers">Section 18.3, “Using Triggers”</a>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM INFORMATION_SCHEMA.TRIGGERS\G</code></strong>
*************************** 1. row ***************************
TRIGGER_CATALOG: NULL
TRIGGER_SCHEMA: test
TRIGGER_NAME: ins_sum
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: NULL
EVENT_OBJECT_SCHEMA: test
EVENT_OBJECT_TABLE: account
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: SET @sum = @sum + NEW.amount
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
1 row in set (1.54 sec)
</pre><p>
See also <a href="sql-syntax.html#show-triggers" title="13.5.4.20. SHOW TRIGGERS Syntax">Section 13.5.4.20, “<code class="literal">SHOW TRIGGERS</code> Syntax”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="other-information-schema-tables"></a>20.1.17. Other <code class="literal">INFORMATION_SCHEMA</code> Tables</h3></div></div></div><p>
We intend to implement additional
<code class="literal">INFORMATION_SCHEMA</code> tables. In particular, we
acknowledge the need for
<code class="literal">INFORMATION_SCHEMA.PARAMETERS</code> and for
<code class="literal">INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS</code>.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="extended-show"></a>20.2. Extensions to <code class="literal">SHOW</code> Statements</h2></div></div></div><a class="indexterm" name="id3061540"></a><a class="indexterm" name="id3061549"></a><a class="indexterm" name="id3061558"></a><p>
Some extensions to <code class="literal">SHOW</code> statements accompany
the implementation of <code class="literal">INFORMATION_SCHEMA</code>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">SHOW</code> can be used to get information about
the structure of <code class="literal">INFORMATION_SCHEMA</code> itself.
</p></li><li><p>
Several <code class="literal">SHOW</code> statements accept a
<code class="literal">WHERE</code> clause that provides more flexibility
in specifying which rows to display.
</p></li></ul></div><p>
These extensions are available beginning with MySQL 5.0.3.
</p><p>
<code class="literal">INFORMATION_SCHEMA</code> is an information database,
so its name is included in the output from <code class="literal">SHOW
DATABASES</code>. Similarly, <code class="literal">SHOW TABLES</code> can
be used with <code class="literal">INFORMATION_SCHEMA</code> to obtain a
list of its tables:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW TABLES FROM INFORMATION_SCHEMA;</code></strong>
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| SCHEMATA |
| TABLES |
| COLUMNS |
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| ROUTINES |
| STATISTICS |
| VIEWS |
| TRIGGERS |
| USER_PRIVILEGES |
| SCHEMA_PRIVILEGES |
| TABLE_PRIVILEGES |
| COLUMN_PRIVILEGES |
| TABLE_CONSTRAINTS |
| KEY_COLUMN_USAGE |
+---------------------------------------+
</pre><p>
<code class="literal">SHOW COLUMNS</code> and <code class="literal">DESCRIBE</code>
can display information about the columns in individual
<code class="literal">INFORMATION_SCHEMA</code> tables.
</p><p>
Several <code class="literal">SHOW</code> statement have been extended to
allow a <code class="literal">WHERE</code> clause:
</p><pre class="programlisting">SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW DATABASES
SHOW FUNCTION STATUS
SHOW KEYS
SHOW OPEN TABLES
SHOW PROCEDURE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW VARIABLES
</pre><p>
The <code class="literal">WHERE</code> clause, if present, is evaluated
against the column names displayed by the <code class="literal">SHOW</code>
statement. For example, the <code class="literal">SHOW COLLATION</code>
statement produces these output columns:
</p><p>
For example, the <code class="literal">SHOW CHARACTER SET</code> statement
produces these output columns:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW CHARACTER SET;</code></strong>
+----------+-----------------------------+---------------------+--------+
| 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 |
...
</pre><p>
To use a <code class="literal">WHERE</code> clause with <code class="literal">SHOW
CHARACTER SET</code>, you would refer to those column names. As
an example, the following statement displays information about
character sets for which the default collation contains the string
<code class="literal">"japanese"</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW CHARACTER SET WHERE `Default collation` LIKE '%japanese%';</code></strong>
+---------+---------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------------------+---------------------+--------+
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+---------+---------------------------+---------------------+--------+
</pre><p>
This statement displays the multi-byte character sets:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW CHARACTER SET WHERE Maxlen > 1;</code></strong>
+---------+---------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+---------+---------------------------+---------------------+--------+
</pre></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="views.html">Prev</a> </td><td width="20%" align="center"> </td><td width="40%" align="right"> <a accesskey="n" href="precision-math.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 19. Views </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 21. Precision Math</td></tr></table></div></body></html>
