<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>