MySQL Tutorial/Data Dictionary/INFORMATION SCHEMA

The INFORMATION_SCHEMA.CHARACTER_SETS Table

The CHARACTER_SETS table provides information about available character sets.

The following statements are equivalent:

   <source lang="sql">

mysql> desc INFORMATION_SCHEMA.CHARACTER_SETS; +----------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+-------------+------+-----+---------+-------+ | CHARACTER_SET_NAME | varchar(64) | NO | | | | | DEFAULT_COLLATE_NAME | varchar(64) | NO | | | | | DESCRIPTION | varchar(60) | NO | | | | | MAXLEN | bigint(3) | NO | | 0 | | +----------------------+-------------+------+-----+---------+-------+ 4 rows in set (0.02 sec) mysql> mysql> SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS; +--------------------+----------------------+-----------------------------+--------+ | CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN | +--------------------+----------------------+-----------------------------+--------+ | big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 | | dec8 | dec8_swedish_ci | DEC West European | 1 | | cp850 | cp850_general_ci | DOS West European | 1 | | hp8 | hp8_english_ci | HP West European | 1 | | koi8r | koi8r_general_ci | KOI8-R Relcom Russian | 1 | | latin1 | latin1_swedish_ci | cp1252 West European | 1 | | latin2 | latin2_general_ci | ISO 8859-2 Central European | 1 | | swe7 | swe7_swedish_ci | 7bit Swedish | 1 | | ascii | ascii_general_ci | US ASCII | 1 | | ujis | ujis_japanese_ci | EUC-JP Japanese | 3 | | sjis | sjis_japanese_ci | Shift-JIS Japanese | 2 | | hebrew | hebrew_general_ci | ISO 8859-8 Hebrew | 1 | | tis620 | tis620_thai_ci | TIS620 Thai | 1 | | euckr | euckr_korean_ci | EUC-KR Korean | 2 | | koi8u | koi8u_general_ci | KOI8-U Ukrainian | 1 | | gb2312 | gb2312_chinese_ci | GB2312 Simplified Chinese | 2 | | greek | greek_general_ci | ISO 8859-7 Greek | 1 | | cp1250 | cp1250_general_ci | Windows Central European | 1 | | gbk | gbk_chinese_ci | GBK Simplified Chinese | 2 | | latin5 | latin5_turkish_ci | ISO 8859-9 Turkish | 1 | | armscii8 | armscii8_general_ci | ARMSCII-8 Armenian | 1 | | utf8 | utf8_general_ci | UTF-8 Unicode | 3 | | ucs2 | ucs2_general_ci | UCS-2 Unicode | 2 | | cp866 | cp866_general_ci | DOS Russian | 1 | | keybcs2 | keybcs2_general_ci | DOS Kamenicky Czech-Slovak | 1 | | macce | macce_general_ci | Mac Central European | 1 | | macroman | macroman_general_ci | Mac West European | 1 | | cp852 | cp852_general_ci | DOS Central European | 1 | | latin7 | latin7_general_ci | ISO 8859-13 Baltic | 1 | | cp1251 | cp1251_general_ci | Windows Cyrillic | 1 | | cp1256 | cp1256_general_ci | Windows Arabic | 1 | | cp1257 | cp1257_general_ci | Windows Baltic | 1 | | binary | binary | Binary pseudo charset | 1 | | geostd8 | geostd8_general_ci | GEOSTD8 Georgian | 1 | | cp932 | cp932_japanese_ci | SJIS for Windows Japanese | 2 | | eucjpms | eucjpms_japanese_ci | UJIS for Windows Japanese | 3 | +--------------------+----------------------+-----------------------------+--------+ 36 rows in set (0.02 sec) mysql> mysql> SHOW CHARACTER SET; +----------+-----------------------------+---------------------+--------+ | 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 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | euckr | EUC-KR Korean | euckr_korean_ci | 2 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | cp866 | DOS Russian | cp866_general_ci | 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | macce | Mac Central European | macce_general_ci | 1 | | macroman | Mac West European | macroman_general_ci | 1 | | cp852 | DOS Central European | cp852_general_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | binary | Binary pseudo charset | binary | 1 | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | +----------+-----------------------------+---------------------+--------+ 36 rows in set (0.00 sec)</source>

The INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY Table

The COLLATION_CHARACTER_SET_APPLICABILITY table indicates what character set is applicable for what collation.

The columns are equivalent to the first two display fields that we get from SHOW COLLATION.

   <source lang="sql">

mysql> desc INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY; +--------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+-------------+------+-----+---------+-------+ | COLLATION_NAME | varchar(64) | NO | | | | | CHARACTER_SET_NAME | varchar(64) | NO | | | | +--------------------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)</source>

The INFORMATION_SCHEMA.COLLATIONS Table

The COLLATIONS table provides information about collations for each character set.

   <source lang="sql">

mysql> mysql> desc INFORMATION_SCHEMA.COLLATIONS; +--------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+-------------+------+-----+---------+-------+ | COLLATION_NAME | varchar(64) | NO | | | | | CHARACTER_SET_NAME | varchar(64) | NO | | | | | ID | bigint(11) | NO | | 0 | | | IS_DEFAULT | varchar(3) | NO | | | | | IS_COMPILED | varchar(3) | NO | | | | | SORTLEN | bigint(3) | NO | | 0 | | +--------------------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)</source>

The INFORMATION_SCHEMA.COLUMN_PRIVILEGES Table

The COLUMN_PRIVILEGES table provides information about column privileges.

PRIVILEGE_TYPE can contain one (and only one) of these values: SELECT, INSERT, UPDATE, REFERENCES.

This information comes from the mysql.columns_priv grant table.

25. 8. INFORMATION_SCHEMA 25. 8. 1. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMADatabase.htm">The INFORMATION_SCHEMA Database</a> 25. 8. 2. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMASCHEMATATable.htm">The INFORMATION_SCHEMA.SCHEMATA Table</a> 25. 8. 3. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMATABLESTable.htm">The INFORMATION_SCHEMA.TABLES Table</a> 25. 8. 4. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMACOLUMNSTable.htm">The INFORMATION_SCHEMA.COLUMNS Table</a> 25. 8. 5. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMASTATISTICSTable.htm">The INFORMATION_SCHEMA.STATISTICS Table</a> 25. 8. 6. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAUSERPRIVILEGESTable.htm">The INFORMATION_SCHEMA.USER_PRIVILEGES Table</a> 25. 8. 7. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMASCHEMAPRIVILEGESTable.htm">The INFORMATION_SCHEMA.SCHEMA_PRIVILEGES Table</a> 25. 8. 8. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMATABLEPRIVILEGESTable.htm">The INFORMATION_SCHEMA.TABLE_PRIVILEGES Table</a> 25. 8. 9. The INFORMATION_SCHEMA.COLUMN_PRIVILEGES Table 25. 8. 10. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMACHARACTERSETSTable.htm">The INFORMATION_SCHEMA.CHARACTER_SETS Table</a> 25. 8. 11. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMACOLLATIONSTable.htm">The INFORMATION_SCHEMA.COLLATIONS Table</a> 25. 8. 12. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMACOLLATIONCHARACTERSETAPPLICABILITYTable.htm">The INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY Table</a> 25. 8. 13. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMATABLECONSTRAINTSTable.htm">The INFORMATION_SCHEMA.TABLE_CONSTRAINTS Table</a> 25. 8. 14. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAKEYCOLUMNUSAGETable.htm">The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table</a> 25. 8. 15. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAROUTINESTable.htm">The INFORMATION_SCHEMA ROUTINES Table</a> 25. 8. 16. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAVIEWSTable.htm">The INFORMATION_SCHEMA.VIEWS Table</a> 25. 8. 17. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMATRIGGERSTable.htm">The INFORMATION_SCHEMA.TRIGGERS Table</a> 25. 8. 18. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAPROFILINGTable.htm">The INFORMATION_SCHEMA.PROFILING Table</a>

The INFORMATION_SCHEMA.COLUMNS Table

The COLUMNS table provides information about columns in tables.

   <source lang="sql">

mysql> desc INFORMATION_SCHEMA.COLUMNS; +--------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+--------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | COLUMN_NAME | varchar(64) | NO | | | | | ORDINAL_POSITION | bigint(21) | NO | | 0 | | | COLUMN_DEFAULT | longtext | YES | | NULL | | | IS_NULLABLE | varchar(3) | NO | | | | | DATA_TYPE | varchar(64) | NO | | | | | CHARACTER_MAXIMUM_LENGTH | bigint(21) | YES | | NULL | | | CHARACTER_OCTET_LENGTH | bigint(21) | YES | | NULL | | | NUMERIC_PRECISION | bigint(21) | YES | | NULL | | | NUMERIC_SCALE | bigint(21) | YES | | NULL | | | CHARACTER_SET_NAME | varchar(64) | YES | | NULL | | | COLLATION_NAME | varchar(64) | YES | | NULL | | | COLUMN_TYPE | longtext | NO | | | | | COLUMN_KEY | varchar(3) | NO | | | | | EXTRA | varchar(20) | NO | | | | | PRIVILEGES | varchar(80) | NO | | | | | COLUMN_COMMENT | varchar(255) | NO | | | | +--------------------------+--------------+------+-----+---------+-------+ 19 rows in set (0.02 sec)</source>

The INFORMATION_SCHEMA Database

INFORMATION_SCHEMA provides access to database metadata.

Metadata is data about the data.

For example, the name of a database or table, the data type of a column, or access privileges.

INFORMATION_SCHEMA is the information database.

Inside INFORMATION_SCHEMA there are several read-only tables.

They are actually views.

It is possible to select INFORMATION_SCHEMA as the default database with a USE statement.

SELECT ... FROM INFORMATION_SCHEMA statement is a more consistent way to provide access to the information provided by the various SHOW statements that.

   <source lang="sql">

mysql> CREATE TABLE Employee(

   ->     id            int,
   ->     first_name    VARCHAR(15),
   ->     last_name     VARCHAR(15),
   ->     start_date    DATE,
   ->     end_date      DATE,
   ->     salary        FLOAT(8,2),
   ->     city          VARCHAR(10),
   ->     description   VARCHAR(15)
   -> );

Query OK, 0 rows affected (0.01 sec) mysql> mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->              values (1,"Jason",    "Martin",  "19960725",  "20060725", 1234.56, "Toronto",  "Programmer");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(2,"Alison",   "Mathews",  "19760321", "19860221", 6661.78, "Vancouver","Tester");

Query OK, 1 row affected (0.01 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(3,"James",    "Smith",    "19781212", "19900315", 6544.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(4,"Celia",    "Rice",     "19821024", "19990421", 2344.78, "Vancouver","Manager");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(5,"Robert",   "Black",    "19840115", "19980808", 2334.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(6,"Linda",    "Green",    "19870730", "19960104", 4322.78,"New York",  "Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(7,"David",    "Larry",    "19901231", "19980212", 7897.78,"New York",  "Manager");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(8,"James",    "Cat",     "19960917",  "20020415", 1232.78,"Vancouver", "Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from Employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> mysql> SELECT table_name, table_type, engine

   -> FROM information_schema.tables;

+---------------------------------------+-------------+--------+ | table_name | table_type | engine | +---------------------------------------+-------------+--------+ | CHARACTER_SETS | SYSTEM VIEW | MEMORY | | COLLATIONS | SYSTEM VIEW | MEMORY | | COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW | MEMORY | | COLUMNS | SYSTEM VIEW | MyISAM | | COLUMN_PRIVILEGES | SYSTEM VIEW | MEMORY | | KEY_COLUMN_USAGE | SYSTEM VIEW | MEMORY | | PROFILING | SYSTEM VIEW | MEMORY | | ROUTINES | SYSTEM VIEW | MyISAM | | SCHEMATA | SYSTEM VIEW | MEMORY | | SCHEMA_PRIVILEGES | SYSTEM VIEW | MEMORY | | STATISTICS | SYSTEM VIEW | MEMORY | | TABLES | SYSTEM VIEW | MEMORY | | TABLE_CONSTRAINTS | SYSTEM VIEW | MEMORY | | TABLE_PRIVILEGES | SYSTEM VIEW | MEMORY | | TRIGGERS | SYSTEM VIEW | MyISAM | | USER_PRIVILEGES | SYSTEM VIEW | MEMORY | | VIEWS | SYSTEM VIEW | MyISAM | | columns_priv | BASE TABLE | MyISAM | | db | BASE TABLE | MyISAM | | func | BASE TABLE | MyISAM | | help_category | BASE TABLE | MyISAM | | help_keyword | BASE TABLE | MyISAM | | help_relation | BASE TABLE | MyISAM | | help_topic | BASE TABLE | MyISAM | | host | BASE TABLE | MyISAM | | proc | BASE TABLE | MyISAM | | procs_priv | BASE TABLE | MyISAM | | tables_priv | BASE TABLE | MyISAM | | time_zone | BASE TABLE | MyISAM | | time_zone_leap_second | BASE TABLE | MyISAM | | time_zone_name | BASE TABLE | MyISAM | | time_zone_transition | BASE TABLE | MyISAM | | time_zone_transition_type | BASE TABLE | MyISAM | | user | BASE TABLE | MyISAM | | employee | BASE TABLE | MyISAM | | v | VIEW | NULL | +---------------------------------------+-------------+--------+ 36 rows in set (0.01 sec) mysql> mysql> mysql> mysql> mysql> mysql> drop table Employee; Query OK, 0 rows affected (0.00 sec) mysql> mysql></source>

The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table

The KEY_COLUMN_USAGE table describes which key columns have constraints.

   <source lang="sql">

mysql> mysql> mysql> desc INFORMATION_SCHEMA.KEY_COLUMN_USAGE; +-------------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------------------+--------------+------+-----+---------+-------+ | CONSTRAINT_CATALOG | varchar(512) | YES | | NULL | | | CONSTRAINT_SCHEMA | varchar(64) | NO | | | | | CONSTRAINT_NAME | varchar(64) | NO | | | | | TABLE_CATALOG | varchar(512) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | COLUMN_NAME | varchar(64) | NO | | | | | ORDINAL_POSITION | bigint(10) | NO | | 0 | | | POSITION_IN_UNIQUE_CONSTRAINT | bigint(10) | YES | | NULL | | | REFERENCED_TABLE_SCHEMA | varchar(64) | YES | | NULL | | | REFERENCED_TABLE_NAME | varchar(64) | YES | | NULL | | | REFERENCED_COLUMN_NAME | varchar(64) | YES | | NULL | | +-------------------------------+--------------+------+-----+---------+-------+ 12 rows in set (0.00 sec)</source>

The INFORMATION_SCHEMA.PROFILING Table

   <source lang="sql">

mysql> desc INFORMATION_SCHEMA.PROFILING; +---------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------------+-------------+------+-----+---------+-------+ | QUERY_ID | bigint(20) | NO | | 0 | | | SEQ | bigint(20) | NO | | 0 | | | STATE | varchar(30) | NO | | | | | DURATION | double | NO | | 0 | | | CPU_USER | double | YES | | NULL | | | CPU_SYSTEM | double | YES | | NULL | | | CONTEXT_VOLUNTARY | bigint(20) | YES | | NULL | | | CONTEXT_INVOLUNTARY | bigint(20) | YES | | NULL | | | BLOCK_OPS_IN | bigint(20) | YES | | NULL | | | BLOCK_OPS_OUT | bigint(20) | YES | | NULL | | | MESSAGES_SENT | bigint(20) | YES | | NULL | | | MESSAGES_RECEIVED | bigint(20) | YES | | NULL | | | PAGE_FAULTS_MAJOR | bigint(20) | YES | | NULL | | | PAGE_FAULTS_MINOR | bigint(20) | YES | | NULL | | | SWAPS | bigint(20) | YES | | NULL | | | SOURCE_FUNCTION | varchar(30) | YES | | NULL | | | SOURCE_FILE | varchar(20) | YES | | NULL | | | SOURCE_LINE | bigint(20) | YES | | NULL | | +---------------------+-------------+------+-----+---------+-------+ 18 rows in set (0.03 sec)</source>

The INFORMATION_SCHEMA ROUTINES Table

The ROUTINES table provides information about procedures and functions.

The column named "mysql.proc name" indicates the mysql.proc table column that corresponds to the INFORMATION_SCHEMA.ROUTINES table column, if any.

   <source lang="sql">

mysql> desc INFORMATION_SCHEMA.ROUTINES; +--------------------+--------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------------------+-------+ | SPECIFIC_NAME | varchar(64) | NO | | | | | ROUTINE_CATALOG | varchar(512) | YES | | NULL | | | ROUTINE_SCHEMA | varchar(64) | NO | | | | | ROUTINE_NAME | varchar(64) | NO | | | | | ROUTINE_TYPE | varchar(9) | NO | | | | | DTD_IDENTIFIER | varchar(64) | YES | | NULL | | | ROUTINE_BODY | varchar(8) | NO | | | | | ROUTINE_DEFINITION | longtext | YES | | NULL | | | EXTERNAL_NAME | varchar(64) | YES | | NULL | | | EXTERNAL_LANGUAGE | varchar(64) | YES | | NULL | | | PARAMETER_STYLE | varchar(8) | NO | | | | | IS_DETERMINISTIC | varchar(3) | NO | | | | | SQL_DATA_ACCESS | varchar(64) | NO | | | | | SQL_PATH | varchar(64) | YES | | NULL | | | SECURITY_TYPE | varchar(7) | NO | | | | | CREATED | datetime | NO | | 0000-00-00 00:00:00 | | | LAST_ALTERED | datetime | NO | | 0000-00-00 00:00:00 | | | SQL_MODE | longtext | NO | | | | | ROUTINE_COMMENT | varchar(64) | NO | | | | | DEFINER | varchar(77) | NO | | | | +--------------------+--------------+------+-----+---------------------+-------+ 20 rows in set (0.02 sec)</source>

The INFORMATION_SCHEMA.SCHEMA_PRIVILEGES Table

The SCHEMA_PRIVILEGES table provides information about schema (database) privileges.

This information comes from the mysql.db grant table.

   <source lang="sql">

mysql> mysql> desc INFORMATION_SCHEMA.SCHEMA_PRIVILEGES; +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+-------+ | GRANTEE | varchar(81) | NO | | | | | TABLE_CATALOG | varchar(512) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | | | | PRIVILEGE_TYPE | varchar(64) | NO | | | | | IS_GRANTABLE | varchar(3) | NO | | | | +----------------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)</source>

The INFORMATION_SCHEMA.SCHEMATA Table

A schema is a database, so the SCHEMATA table provides information about databases.

   <source lang="sql">

mysql> desc INFORMATION_SCHEMA.SCHEMATA; +----------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------------+--------------+------+-----+---------+-------+ | CATALOG_NAME | varchar(512) | YES | | NULL | | | SCHEMA_NAME | varchar(64) | NO | | | | | DEFAULT_CHARACTER_SET_NAME | varchar(64) | NO | | | | | DEFAULT_COLLATION_NAME | varchar(64) | NO | | | | | SQL_PATH | varchar(512) | YES | | NULL | | +----------------------------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)</source>

The INFORMATION_SCHEMA.STATISTICS Table

The STATISTICS table provides information about table indexes.

   <source lang="sql">

mysql> desc INFORMATION_SCHEMA.STATISTICS; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | NON_UNIQUE | bigint(1) | NO | | 0 | | | INDEX_SCHEMA | varchar(64) | NO | | | | | INDEX_NAME | varchar(64) | NO | | | | | SEQ_IN_INDEX | bigint(2) | NO | | 0 | | | COLUMN_NAME | varchar(64) | NO | | | | | COLLATION | varchar(1) | YES | | NULL | | | CARDINALITY | bigint(21) | YES | | NULL | | | SUB_PART | bigint(3) | YES | | NULL | | | PACKED | varchar(10) | YES | | NULL | | | NULLABLE | varchar(3) | NO | | | | | INDEX_TYPE | varchar(16) | NO | | | | | COMMENT | varchar(16) | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 15 rows in set (0.02 sec)</source>

The INFORMATION_SCHEMA.TABLE_CONSTRAINTS Table

The TABLE_CONSTRAINTS table describes which tables have constraints.

The CONSTRAINT_TYPE value can be UNIQUE, PRIMARY KEY, or FOREIGN KEY.

The CONSTRAINT_TYPE column can contain one of these values: UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK.

25. 8. INFORMATION_SCHEMA 25. 8. 1. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMADatabase.htm">The INFORMATION_SCHEMA Database</a> 25. 8. 2. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMASCHEMATATable.htm">The INFORMATION_SCHEMA.SCHEMATA Table</a> 25. 8. 3. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMATABLESTable.htm">The INFORMATION_SCHEMA.TABLES Table</a> 25. 8. 4. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMACOLUMNSTable.htm">The INFORMATION_SCHEMA.COLUMNS Table</a> 25. 8. 5. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMASTATISTICSTable.htm">The INFORMATION_SCHEMA.STATISTICS Table</a> 25. 8. 6. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAUSERPRIVILEGESTable.htm">The INFORMATION_SCHEMA.USER_PRIVILEGES Table</a> 25. 8. 7. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMASCHEMAPRIVILEGESTable.htm">The INFORMATION_SCHEMA.SCHEMA_PRIVILEGES Table</a> 25. 8. 8. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMATABLEPRIVILEGESTable.htm">The INFORMATION_SCHEMA.TABLE_PRIVILEGES Table</a> 25. 8. 9. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMACOLUMNPRIVILEGESTable.htm">The INFORMATION_SCHEMA.COLUMN_PRIVILEGES Table</a> 25. 8. 10. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMACHARACTERSETSTable.htm">The INFORMATION_SCHEMA.CHARACTER_SETS Table</a> 25. 8. 11. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMACOLLATIONSTable.htm">The INFORMATION_SCHEMA.COLLATIONS Table</a> 25. 8. 12. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMACOLLATIONCHARACTERSETAPPLICABILITYTable.htm">The INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY Table</a> 25. 8. 13. The INFORMATION_SCHEMA.TABLE_CONSTRAINTS Table 25. 8. 14. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAKEYCOLUMNUSAGETable.htm">The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table</a> 25. 8. 15. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAROUTINESTable.htm">The INFORMATION_SCHEMA ROUTINES Table</a> 25. 8. 16. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAVIEWSTable.htm">The INFORMATION_SCHEMA.VIEWS Table</a> 25. 8. 17. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMATRIGGERSTable.htm">The INFORMATION_SCHEMA.TRIGGERS Table</a> 25. 8. 18. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAPROFILINGTable.htm">The INFORMATION_SCHEMA.PROFILING Table</a>

The INFORMATION_SCHEMA.TABLE_PRIVILEGES Table

The TABLE_PRIVILEGES table provides information about table privileges.

PRIVILEGE_TYPE can contain one (and only one) of these values: SELECT, INSERT, UPDATE, REFERENCES, ALTER, INDEX, DROP, CREATE VIEW.

This information comes from the mysql.tables_priv grant table.

   <source lang="sql">

mysql> mysql> desc INFORMATION_SCHEMA.TABLE_PRIVILEGES; +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+-------+ | GRANTEE | varchar(81) | NO | | | | | TABLE_CATALOG | varchar(512) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | PRIVILEGE_TYPE | varchar(64) | NO | | | | | IS_GRANTABLE | varchar(3) | NO | | | | +----------------+--------------+------+-----+---------+-------+ 6 rows in set (0.02 sec)</source>

The INFORMATION_SCHEMA.TABLES Table

The TABLES table provides information about tables in databases.

   <source lang="sql">

mysql> desc INFORMATION_SCHEMA.TABLES; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | TABLE_TYPE | varchar(64) | NO | | | | | ENGINE | varchar(64) | YES | | NULL | | | VERSION | bigint(21) | YES | | NULL | | | ROW_FORMAT | varchar(10) | YES | | NULL | | | TABLE_ROWS | bigint(21) | YES | | NULL | | | AVG_ROW_LENGTH | bigint(21) | YES | | NULL | | | DATA_LENGTH | bigint(21) | YES | | NULL | | | MAX_DATA_LENGTH | bigint(21) | YES | | NULL | | | INDEX_LENGTH | bigint(21) | YES | | NULL | | | DATA_FREE | bigint(21) | YES | | NULL | | | AUTO_INCREMENT | bigint(21) | YES | | NULL | | | CREATE_TIME | datetime | YES | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | TABLE_COLLATION | varchar(64) | YES | | NULL | | | CHECKSUM | bigint(21) | YES | | NULL | | | CREATE_OPTIONS | varchar(255) | YES | | NULL | | | TABLE_COMMENT | varchar(80) | NO | | | | +-----------------+--------------+------+-----+---------+-------+ 21 rows in set (0.01 sec)</source>

The INFORMATION_SCHEMA.TRIGGERS Table

The TRIGGERS table provides information about triggers.

   <source lang="sql">

mysql> desc INFORMATION_SCHEMA.TRIGGERS; +----------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------------+--------------+------+-----+---------+-------+ | TRIGGER_CATALOG | varchar(512) | YES | | NULL | | | TRIGGER_SCHEMA | varchar(64) | NO | | | | | TRIGGER_NAME | varchar(64) | NO | | | | | EVENT_MANIPULATION | varchar(6) | NO | | | | | EVENT_OBJECT_CATALOG | varchar(512) | YES | | NULL | | | EVENT_OBJECT_SCHEMA | varchar(64) | NO | | | | | EVENT_OBJECT_TABLE | varchar(64) | NO | | | | | ACTION_ORDER | bigint(4) | NO | | 0 | | | ACTION_CONDITION | longtext | YES | | NULL | | | ACTION_STATEMENT | longtext | NO | | | | | ACTION_ORIENTATION | varchar(9) | NO | | | | | ACTION_TIMING | varchar(6) | NO | | | | | ACTION_REFERENCE_OLD_TABLE | varchar(64) | YES | | NULL | | | ACTION_REFERENCE_NEW_TABLE | varchar(64) | YES | | NULL | | | ACTION_REFERENCE_OLD_ROW | varchar(3) | NO | | | | | ACTION_REFERENCE_NEW_ROW | varchar(3) | NO | | | | | CREATED | datetime | YES | | NULL | | | SQL_MODE | longtext | NO | | | | | DEFINER | longtext | NO | | | | +----------------------------+--------------+------+-----+---------+-------+ 19 rows in set (0.02 sec)</source>

The INFORMATION_SCHEMA.USER_PRIVILEGES Table

   <source lang="sql">

mysql> desc INFORMATION_SCHEMA.USER_PRIVILEGES; +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+-------+ | GRANTEE | varchar(81) | NO | | | | | TABLE_CATALOG | varchar(512) | YES | | NULL | | | PRIVILEGE_TYPE | varchar(64) | NO | | | | | IS_GRANTABLE | varchar(3) | NO | | | | +----------------+--------------+------+-----+---------+-------+ 4 rows in set (0.02 sec)</source>

The INFORMATION_SCHEMA.VIEWS Table

The VIEWS table provides information about views in databases. You must have the SHOW VIEW privilege to access this table.

   <source lang="sql">

mysql> desc INFORMATION_SCHEMA.VIEWS; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | VIEW_DEFINITION | longtext | NO | | | | | CHECK_OPTION | varchar(8) | NO | | | | | IS_UPDATABLE | varchar(3) | NO | | | | | DEFINER | varchar(77) | NO | | | | | SECURITY_TYPE | varchar(7) | NO | | | | +-----------------+--------------+------+-----+---------+-------+ 8 rows in set (0.02 sec)</source>