The INFORMATION_SCHEMA contains only tables and columns defined by the ANSI SQL standard. It should be used over DATA_DICTIONARY if you wish to write tools portable to other RDBMs.
Drizzle currently provides the following INFORMATION_SCHEMA tables:
- CHECK_CONSTRAINTS
- COLUMNS
- COLUMN_DOMAIN_USAGE
- COLUMN_PRIVILEGES
- CONSTRAINT_COLUMN_USAGE
- CONSTRAINT_TABLE_USAGE
- DOMAINS
- DOMAIN_CONSTRAINTS
- KEY_COLUMN_USAGE
- PARAMETERS
- REFERENTIAL_CONSTRAINTS
- ROUTINES
- ROUTINE_COLUMNS
- SCHEMATA
- TABLES
- TABLE_CONSTRAINTS
- TABLE_PRIVILEGES
- VIEWS
- VIEW_COLUMN_USAGE
- VIEW_TABLE_USAGE
The INFORMATION_SCHEMA is implemented as a standard set of plugins that use the FunctionEngine. The FunctionEngine is a Drizzle Storage Engine plugin that allows table function plugins to iterate over internal data structures and present them to the user as normal SQL data. For all normal queries that either scan or do lookups on INFORMATION_SCHEMA, no tempory table is created; the data structures internal to Drizzle are iterated through and returned row by row to the user. The only exception is when the rnd_pos access method is used, then a copy of the table is made in memory so that we will not have to continually perform full table scans.
All of the INFORMATION_SCHEMA tables are read-only.
CREATE TABLE `CHECK_CONSTRAINTS` (
`CONSTRAINT_CATALOG` VARCHAR(256) NOT NULL,
`CONSTRAINT_SCHEMA` VARCHAR(256) NOT NULL,
`CONSTRAINT_NAME` VARCHAR(256) NOT NULL,
`CHECK_CLAUSE` VARCHAR(256) NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'
Since Drizzle does not currentl support CHECK constraints, this table is (currently) always empty and is provided for standards compatibility
CREATE TABLE `COLUMNS` (
`TABLE_CATALOG` VARCHAR(256) NOT NULL,
`TABLE_SCHEMA` VARCHAR(256) NOT NULL,
`TABLE_NAME` VARCHAR(256) NOT NULL,
`COLUMN_NAME` VARCHAR(256) NOT NULL,
`ORDINAL_POSITION` BIGINT NOT NULL,
`COLUMN_DEFAULT` VARCHAR(256) DEFAULT NULL,
`IS_NULLABLE` BOOLEAN NOT NULL,
`DATA_TYPE` VARCHAR(256) NOT NULL,
`CHARACTER_MAXIMUM_LENGTH` BIGINT DEFAULT NULL,
`CHARACTER_OCTET_LENGTH` BIGINT DEFAULT NULL,
`NUMERIC_PRECISION` BIGINT DEFAULT NULL,
`NUMERIC_PRECISION_RADIX` BIGINT DEFAULT NULL,
`NUMERIC_SCALE` BIGINT DEFAULT NULL,
`DATETIME_PRECISION` VARCHAR(256) DEFAULT NULL,
`CHARACTER_SET_CATALOG` VARCHAR(256) DEFAULT NULL,
`CHARACTER_SET_SCHEMA` VARCHAR(256) DEFAULT NULL,
`CHARACTER_SET_NAME` VARCHAR(256) DEFAULT NULL,
`COLLATION_CATALOG` VARCHAR(256) DEFAULT NULL,
`COLLATION_SCHEMA` VARCHAR(256) DEFAULT NULL,
`COLLATION_NAME` VARCHAR(256) DEFAULT NULL,
`DOMAIN_CATALOG` VARCHAR(256) DEFAULT NULL,
`DOMAIN_SCHEMA` VARCHAR(256) DEFAULT NULL,
`DOMAIN_NAME` VARCHAR(256) DEFAULT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'
CREATE TABLE `COLUMN_DOMAIN_USAGE` (
`DOMAIN_CATALOG` VARCHAR(256) NOT NULL,
`DOMAIN_SCHEMA` VARCHAR(256) NOT NULL,
`DOMAIN_NAME` VARCHAR(256) NOT NULL,
`TABLE_CATALOG` VARCHAR(256) NOT NULL,
`TABLE_SCHEMA` VARCHAR(256) NOT NULL,
`TABLE_NAME` VARCHAR(256) NOT NULL,
`COLUMN_NAME` VARCHAR(256) NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'
CREATE TABLE `COLUMN_PRIVILEGES` (
`GRANTOR` VARCHAR(256) NOT NULL,
`GRANTEE` VARCHAR(256) NOT NULL,
`TABLE_CATALOG` VARCHAR(256) NOT NULL,
`TABLE_SCHEMA` VARCHAR(256) NOT NULL,
`TABLE_NAME` VARCHAR(256) NOT NULL,
`COLUMN_NAME` VARCHAR(256) NOT NULL,
`PRIVILEGE_TYPE` VARCHAR(256) NOT NULL,
`IS_GRANTABLE` VARCHAR(256) NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'
CREATE TABLE `CONSTRAINT_COLUMN_USAGE` (
`TABLE_CATALOG` VARCHAR(256) NOT NULL,
`TABLE_SCHEMA` VARCHAR(256) NOT NULL,
`TABLE_NAME` VARCHAR(256) NOT NULL,
`COLUMN_NAME` VARCHAR(256) NOT NULL,
`CONSTRAINT_CATALOG` VARCHAR(256) NOT NULL,
`CONSTRAINT_SCHEMA` VARCHAR(256) NOT NULL,
`CONSTRAINT_NAME` VARCHAR(256) NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'
CREATE TABLE `CONSTRAINT_TABLE_USAGE` (
`TABLE_CATALOG` VARCHAR(256) NOT NULL,
`TABLE_SCHEMA` VARCHAR(256) NOT NULL,
`TABLE_NAME` VARCHAR(256) NOT NULL,
`CONSTRAINT_CATALOG` VARCHAR(256) NOT NULL,
`CONSTRAINT_SCHEMA` VARCHAR(256) NOT NULL,
`CONSTRAINT_NAME` VARCHAR(256) NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'
CREATE TABLE `DOMAINS` (
`DOMAIN_CATALOG` VARCHAR(256) NOT NULL,
`DOMAIN_SCHEMA` VARCHAR(256) NOT NULL,
`DOMAIN_NAME` VARCHAR(256) NOT NULL,
`DATA_TYPE` VARCHAR(256) NOT NULL,
`CHARACTER_MAXIMUM_LENGTH` VARCHAR(256) NOT NULL,
`CHARACTER_OCTET_LENGTH` VARCHAR(256) NOT NULL,
`COLLATION_CATALOG` VARCHAR(256) NOT NULL,
`COLLATION_SCHEMA` VARCHAR(256) NOT NULL,
`COLLATION_NAME` VARCHAR(256) NOT NULL,
`CHARACTER_SET_CATALOG` VARCHAR(256) NOT NULL,
`CHARACTER_SET_SCHEMA` VARCHAR(256) NOT NULL,
`CHARACTER_SET_NAME` VARCHAR(256) NOT NULL,
`NUMERIC_PRECISION` VARCHAR(256) NOT NULL,
`NUMERIC_PRECISION_RADIX` VARCHAR(256) NOT NULL,
`NUMERIC_SCALE` VARCHAR(256) NOT NULL,
`DATETIME_PRECISION` VARCHAR(256) NOT NULL,
`DOMAIN_DEFAULT` VARCHAR(256) NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'
CREATE TABLE `DOMAIN_CONSTRAINTS` (
`CONSTRAINT_CATALOG` VARCHAR(256) NOT NULL,
`CONSTRAINT_SCHEMA` VARCHAR(256) NOT NULL,
`CONSTRAINT_NAME` VARCHAR(256) NOT NULL,
`DOMAIN_CATALOG` VARCHAR(256) NOT NULL,
`DOMAIN_SCHEMA` VARCHAR(256) NOT NULL,
`DOMAIN_NAME` VARCHAR(256) NOT NULL,
`IS_DEFERRABLE` BOOLEAN NOT NULL,
`INITIALLY_DEFERRED` BOOLEAN NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'
CREATE TABLE `KEY_COLUMN_USAGE` (
`CONSTRAINT_CATALOG` VARCHAR(256) NOT NULL,
`CONSTRAINT_SCHEMA` VARCHAR(256) NOT NULL,
`CONSTRAINT_NAME` VARCHAR(256) NOT NULL,
`TABLE_CATALOG` VARCHAR(256) NOT NULL,
`TABLE_SCHEMA` VARCHAR(256) NOT NULL,
`TABLE_NAME` VARCHAR(256) NOT NULL,
`COLUMN_NAME` VARCHAR(256) NOT NULL,
`ORDINAL_POSITION` BIGINT NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'
CREATE TABLE `PARAMETERS` (
`SPECIFIC_CATALOG` VARCHAR(256) NOT NULL,
`SPECIFIC_SCHEMA` VARCHAR(256) NOT NULL,
`SPECIFIC_NAME` VARCHAR(256) NOT NULL,
`ORDINAL_POSITION` VARCHAR(256) NOT NULL,
`PARAMETER_MODE` VARCHAR(256) NOT NULL,
`IS_RESULT` VARCHAR(256) NOT NULL,
`AS_LOCATOR` VARCHAR(256) NOT NULL,
`PARAMETER_NAME` VARCHAR(256) NOT NULL,
`DATA_TYPE` VARCHAR(256) NOT NULL,
`CHARACTER_MAXIMUM_LENGTH` VARCHAR(256) NOT NULL,
`CHARACTER_OCTET_LENGTH` VARCHAR(256) NOT NULL,
`CHARACTER_OCTET_LENGTH` VARCHAR(256) NOT NULL,
`COLLATION_CATALOG` VARCHAR(256) NOT NULL,
`COLLATION_SCHEMA` VARCHAR(256) NOT NULL,
`COLLATION_NAME` VARCHAR(256) NOT NULL,
`CHARACTER_SET_CATALOG` VARCHAR(256) NOT NULL,
`CHARACTER_SET_SCHEMA` VARCHAR(256) NOT NULL,
`CHARACTER_SET_NAME` VARCHAR(256) NOT NULL,
`NUMERIC_PRECISION` VARCHAR(256) NOT NULL,
`NUMERIC_PRECISION_RADIX` VARCHAR(256) NOT NULL,
`NUMERIC_SCALE` VARCHAR(256) NOT NULL,
`DATETIME_PRECISION` VARCHAR(256) NOT NULL,
`INTERVAL_TYPE` VARCHAR(256) NOT NULL,
`INTERVAL_PRECISION` VARCHAR(256) NOT NULL,
`USER_DEFINED_TYPE_CATALOG` VARCHAR(256) NOT NULL,
`USER_DEFINED_TYPE_SCHEMA` VARCHAR(256) NOT NULL,
`USER_DEFINED_TYPE_NAME` VARCHAR(256) NOT NULL,
`SCOPE_CATALOG` VARCHAR(256) NOT NULL,
`SCOPE_SCHEMA` VARCHAR(256) NOT NULL,
`SCOPE_NAME` VARCHAR(256) NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'
CREATE TABLE `REFERENTIAL_CONSTRAINTS` (
`CONSTRAINT_CATALOG` VARCHAR(256) NOT NULL,
`CONSTRAINT_SCHEMA` VARCHAR(256) NOT NULL,
`CONSTRAINT_NAME` VARCHAR(256) NOT NULL,
`UNIQUE_CONSTRAINT_CATALOG` VARCHAR(256) NOT NULL,
`UNIQUE_CONSTRAINT_SCHEMA` VARCHAR(256) NOT NULL,
`UNIQUE_CONSTRAINT_NAME` VARCHAR(256) NOT NULL,
`MATCH_OPTION` VARCHAR(256) NOT NULL,
`UPDATE_RULE` VARCHAR(256) NOT NULL,
`DELETE_RULE` VARCHAR(256) NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'
CREATE TABLE `ROUTINES` (
`SPECIFIC_CATALOG` VARCHAR(256) NOT NULL,
`SPECIFIC_SCHEMA` VARCHAR(256) NOT NULL,
`SPECIFIC_NAME` VARCHAR(256) NOT NULL,
`ROUTINE_CATALOG` VARCHAR(256) NOT NULL,
`ROUTINE_SCHEMA` VARCHAR(256) NOT NULL,
`ROUTINE_NAME` VARCHAR(256) NOT NULL,
`MODULE_CATALOG` VARCHAR(256) NOT NULL,
`MODULE_SCHEMA` VARCHAR(256) NOT NULL,
`MODULE_NAME` VARCHAR(256) NOT NULL,
`UDT_CATALOG` VARCHAR(256) NOT NULL,
`UDT_SCHEMA` VARCHAR(256) NOT NULL,
`UDT_NAME` VARCHAR(256) NOT NULL,
`DATA_TYPE` VARCHAR(256) NOT NULL,
`CHARACTER_MAXIMUM_LENGTH` VARCHAR(256) NOT NULL,
`CHARACTER_OCTET_LENGTH` VARCHAR(256) NOT NULL,
`COLLATION_CATALOG` VARCHAR(256) NOT NULL,
`COLLATION_SCHEMA` VARCHAR(256) NOT NULL,
`COLLATION_NAME` VARCHAR(256) NOT NULL,
`CHARACTER_SET_CATALOG` VARCHAR(256) NOT NULL,
`CHARACTER_SET_SCHEMA` VARCHAR(256) NOT NULL,
`CHARACTER_SET_NAME` VARCHAR(256) NOT NULL,
`TABLE_CATALOG` VARCHAR(256) NOT NULL,
`TABLE_SCHEMA` VARCHAR(256) NOT NULL,
`TABLE_NAME` VARCHAR(256) NOT NULL,
`NUMERIC_PRECISION` VARCHAR(256) NOT NULL,
`NUMERIC_PRECISION_RADIX` VARCHAR(256) NOT NULL,
`NUMERIC_SCALE` VARCHAR(256) NOT NULL,
`DATETIME_PRECISION` VARCHAR(256) NOT NULL,
`INTERVAL_TYPE` VARCHAR(256) NOT NULL,
`INTERVAL_PRECISION` VARCHAR(256) NOT NULL,
`TYPE_UDT_CATALOG` VARCHAR(256) NOT NULL,
`TYPE_UDT_SCHEMA` VARCHAR(256) NOT NULL,
`TYPE_UDT_NAME` VARCHAR(256) NOT NULL,
`SCOPE_CATALOG` VARCHAR(256) NOT NULL,
`SCOPE_SCHEMA` VARCHAR(256) NOT NULL,
`SCOPE_NAME` VARCHAR(256) NOT NULL,
`MAXIMUM_CARDINALITY` VARCHAR(256) NOT NULL,
`DTD_IDENTIFIER` VARCHAR(256) NOT NULL,
`ROUTINE_BODY` VARCHAR(256) NOT NULL,
`ROUTINE_DEFINITION` VARCHAR(256) NOT NULL,
`EXTERNAL_NAME` VARCHAR(256) NOT NULL,
`EXTERNAL_LANGUAGE` VARCHAR(256) NOT NULL,
`PARAMETER_STYLE` VARCHAR(256) NOT NULL,
`IS_DETERMINISTIC` VARCHAR(256) NOT NULL,
`SQL_DATA_ACCESS` VARCHAR(256) NOT NULL,
`IS_NULL_CALL` VARCHAR(256) NOT NULL,
`SQL_PATH` VARCHAR(256) NOT NULL,
`SCHEMA_LEVEL_ROUTINE` VARCHAR(256) NOT NULL,
`MAX_DYNAMIC_RESULT_SETS` VARCHAR(256) NOT NULL,
`IS_USER_DEFINED_CAST` VARCHAR(256) NOT NULL,
`IS_IMPLICITLY_INVOCABLE` VARCHAR(256) NOT NULL,
`CREATED` VARCHAR(256) NOT NULL,
`LAST_ALTERED` VARCHAR(256) NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'
CREATE TABLE `ROUTINE_COLUMNS` (
`TABLE_CATALOG` VARCHAR(256) NOT NULL,
`TABLE_SCHEMA` VARCHAR(256) NOT NULL,
`TABLE_NAME` VARCHAR(256) NOT NULL,
`COLUMN_NAME` VARCHAR(256) NOT NULL,
`ORDINAL_POSITION` VARCHAR(256) NOT NULL,
`COLUMN_DEFAULT` VARCHAR(256) NOT NULL,
`IS_NULLABLE` VARCHAR(256) NOT NULL,
`DATA_TYPE` VARCHAR(256) NOT NULL,
`CHARACTER_MAXIMUM_LENGTH` VARCHAR(256) NOT NULL,
`CHARACTER_OCTET_LENGTH` VARCHAR(256) NOT NULL,
`NUMERIC_PRECISION` VARCHAR(256) NOT NULL,
`NUMERIC_PRECISION_RADIX` VARCHAR(256) NOT NULL,
`NUMERIC_SCALE` VARCHAR(256) NOT NULL,
`DATETIME_PRECISION` VARCHAR(256) NOT NULL,
`CHARACTER_SET_CATALOG` VARCHAR(256) NOT NULL,
`CHARACTER_SET_SCHEMA` VARCHAR(256) NOT NULL,
`CHARACTER_SET_NAME` VARCHAR(256) NOT NULL,
`COLLATION_CATALOG` VARCHAR(256) NOT NULL,
`COLLATION_SCHEMA` VARCHAR(256) NOT NULL,
`COLLATION_NAME` VARCHAR(256) NOT NULL,
`DOMAIN_CATALOG` VARCHAR(256) NOT NULL,
`DOMAIN_SCHEMA` VARCHAR(256) NOT NULL,
`DOMAIN_NAME` VARCHAR(256) NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'
CREATE TABLE `SCHEMATA` (
`CATALOG_NAME` VARCHAR(256) NOT NULL,
`SCHEMA_NAME` VARCHAR(256) NOT NULL,
`SCHEMA_OWNER` VARCHAR(256) DEFAULT NULL,
`DEFAULT_CHARACTER_SET_CATALOG` VARCHAR(256) DEFAULT NULL,
`DEFAULT_CHARACTER_SET_SCHEMA` VARCHAR(256) DEFAULT NULL,
`DEFAULT_CHARACTER_SET_NAME` VARCHAR(256) DEFAULT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'
CREATE TABLE `TABLES` (
`TABLE_CATALOG` VARCHAR(256) NOT NULL,
`TABLE_SCHEMA` VARCHAR(256) NOT NULL,
`TABLE_NAME` VARCHAR(256) NOT NULL,
`TABLE_TYPE` VARCHAR(256) NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'
This table contains a row for every table and view in this catalog that the current user has permission to see.
The tuple of TABLE_CATALOG, TABLE_SCHEMA and TABLE_NAME uniquely identify a table.
CREATE TABLE `TABLE_CONSTRAINTS` (
`CONSTRAINT_CATALOG` VARCHAR(256) NOT NULL,
`CONSTRAINT_SCHEMA` VARCHAR(256) NOT NULL,
`CONSTRAINT_NAME` VARCHAR(256) NOT NULL,
`TABLE_CATALOG` VARCHAR(256) NOT NULL,
`TABLE_SCHEMA` VARCHAR(256) NOT NULL,
`TABLE_NAME` VARCHAR(256) NOT NULL,
`CONSTRAINT_TYPE` VARCHAR(256) NOT NULL,
`IS_DEFERRABLE` BOOLEAN NOT NULL,
`INITIALLY_DEFERRED` BOOLEAN NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'
The tuple of TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME uniquely identifies the table that this constraint applies to.
CONSTRAINT_TYPE is one of UNIQUE, PRIMARY KEY, FOREIGN KEY or CHECK.
CREATE TABLE `TABLE_PRIVILEGES` (
`GRANTOR` VARCHAR(256) NOT NULL,
`GRANTEE` VARCHAR(256) NOT NULL,
`TABLE_CATALOG` VARCHAR(256) NOT NULL,
`TABLE_SCHEMA` VARCHAR(256) NOT NULL,
`TABLE_NAME` VARCHAR(256) NOT NULL,
`COLUMN_NAME` VARCHAR(256) NOT NULL,
`PRIVILEGE_TYPE` VARCHAR(256) NOT NULL,
`IS_GRANTABLE` VARCHAR(256) NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'
CREATE TABLE `VIEWS` (
`VIEW_CATALOG` VARCHAR(256) NOT NULL,
`VIEW_SCHEMA` VARCHAR(256) NOT NULL,
`VIEW_NAME` VARCHAR(256) NOT NULL,
`TABLE_CATALOG` VARCHAR(256) NOT NULL,
`TABLE_SCHEMA` VARCHAR(256) NOT NULL,
`TABLE_NAME` VARCHAR(256) NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'
CREATE TABLE `VIEW_COLUMN_USAGE` (
`VIEW_CATALOG` VARCHAR(256) NOT NULL,
`VIEW_SCHEMA` VARCHAR(256) NOT NULL,
`VIEW_NAME` VARCHAR(256) NOT NULL,
`TABLE_CATALOG` VARCHAR(256) NOT NULL,
`TABLE_SCHEMA` VARCHAR(256) NOT NULL,
`TABLE_NAME` VARCHAR(256) NOT NULL,
`COLUMN_NAME` VARCHAR(256) NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'
CREATE TABLE `VIEW_TABLE_USAGE` (
`TABLE_CATALOG` VARCHAR(256) NOT NULL,
`TABLE_SCHEMA` VARCHAR(256) NOT NULL,
`TABLE_NAME` VARCHAR(256) NOT NULL,
`VIEW_DEFINITION` VARCHAR(256) NOT NULL,
`CHECK_OPTION` VARCHAR(256) NOT NULL,
`IS_UPDATABLE` VARCHAR(256) NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE DEFINER 'SYSTEM'