▲
Search:
| Highlight keyword(s)
Home Download Cheat Sheet Documentation Quickstart Installation Tutorial Features Security Performance Advanced Reference Commands Functions • Aggregate • Window Data Types SQL Grammar System Tables Javadoc PDF (2 MB) Support FAQ Error Analyzer Google Group Appendix History License Build Links MVStore Architecture Migration to 2.0 |  | # System Tables ## Index Information Schema
CHECK_CONSTRAINTS COLLATIONS COLUMNS COLUMN_PRIVILEGES CONSTANTS CONSTRAINT_COLUMN_USAGE DOMAINS DOMAIN_CONSTRAINTS ELEMENT_TYPES ENUM_VALUES FIELDS INDEXES | INDEX_COLUMNS INFORMATION_SCHEMA_CATALOG_NAME IN_DOUBT KEY_COLUMN_USAGE LOCKS PARAMETERS QUERY_STATISTICS REFERENTIAL_CONSTRAINTS RIGHTS ROLES ROUTINES SCHEMATA | SEQUENCES SESSIONS SESSION_STATE SETTINGS SYNONYMS TABLES TABLE_CONSTRAINTS TABLE_PRIVILEGES TRIGGERS USERS VIEWS
Range Table ## Information Schema The system tables and views in the schema `INFORMATION_SCHEMA` contain the meta data of all tables, views, domains, and other objects in the database as well as the current settings. This documentation describes the default new version of `INFORMATION_SCHEMA` for H2 2.0. Old TCP clients (1.4.200 and below) see the legacy version of `INFORMATION_SCHEMA`, because they can't work with the new one. The legacy version is not documented. ### CHECK_CONSTRAINTS Contains CHECK clauses of check and domain constraints.
CONSTRAINT_CATALOG | CHARACTER VARYING
The catalog (database name).
CONSTRAINT_SCHEMA | CHARACTER VARYING
The schema of the constraint.
CONSTRAINT_NAME | CHARACTER VARYING
The name of the constraint.
CHECK_CLAUSE | CHARACTER VARYING
The SQL of CHECK clause.
### COLLATIONS Contains available collations.
COLLATION_CATALOG | CHARACTER VARYING
The catalog (database name) for character string data types.
COLLATION_SCHEMA | CHARACTER VARYING
The name of public schema for character string data types.
COLLATION_NAME | CHARACTER VARYING
The name of collation for character string data types.
PAD_ATTRIBUTE | CHARACTER VARYING
'NO PAD'.
LANGUAGE_TAG | CHARACTER VARYING
The language tag.
### COLUMNS Contains information about columns of tables.
TABLE_CATALOG | CHARACTER VARYING
The catalog (database name).
TABLE_SCHEMA | CHARACTER VARYING
The schema of the table.
TABLE_NAME | CHARACTER VARYING
The name of the table.
COLUMN_NAME | CHARACTER VARYING
The name of the column.
ORDINAL_POSITION | INTEGER
The ordinal position (1-based).
COLUMN_DEFAULT | CHARACTER VARYING
The SQL of DEFAULT expression, if any.
IS_NULLABLE | CHARACTER VARYING
Whether column may contain NULL value ('YES' or 'NO').
DATA_TYPE | CHARACTER VARYING
The SQL data type name.
CHARACTER_MAXIMUM_LENGTH | BIGINT
The maximum length in characters for character string data types. For binary string data types contains the same value as CHARACTER_OCTET_LENGTH.
CHARACTER_OCTET_LENGTH | BIGINT
The maximum length in bytes for binary string data types. For character string data types contains the same value as CHARACTER_MAXIMUM_LENGTH.
NUMERIC_PRECISION | INTEGER
The precision for numeric data types.
NUMERIC_PRECISION_RADIX | INTEGER
The radix of precision (2 or 10) for numeric data types.
NUMERIC_SCALE | INTEGER
The scale for numeric data types.
DATETIME_PRECISION | INTEGER
The fractional seconds precision for datetime data types.
INTERVAL_TYPE | CHARACTER VARYING
The data type of interval qualifier for interval data types.
INTERVAL_PRECISION | INTEGER
The leading field precision for interval data types.
CHARACTER_SET_CATALOG | CHARACTER VARYING
The catalog (database name) for character string data types.
CHARACTER_SET_SCHEMA | CHARACTER VARYING
The name of public schema for character string data types.
CHARACTER_SET_NAME | CHARACTER VARYING
The 'Unicode' for character string data types.
COLLATION_CATALOG | CHARACTER VARYING
The catalog (database name) for character string data types.
COLLATION_SCHEMA | CHARACTER VARYING
The name of public schema for character string data types.
COLLATION_NAME | CHARACTER VARYING
The name of collation for character string data types.
DOMAIN_CATALOG | CHARACTER VARYING
The catalog for columns with domain.
DOMAIN_SCHEMA | CHARACTER VARYING
The schema of domain for columns with domain.
DOMAIN_NAME | CHARACTER VARYING
The name of domain for columns with domain.
MAXIMUM_CARDINALITY | INTEGER
The maximum cardinality for array data types.
DTD_IDENTIFIER | CHARACTER VARYING
The data type identifier to read additional information from INFORMATION_SCHEMA.ELEMENT_TYPES for array data types, INFORMATION_SCHEMA.ENUM_VALUES for ENUM data type, and INFORMATION_SCHEMA.FIELDS for row value data types.
IS_IDENTITY | CHARACTER VARYING
Whether column is an identity column ('YES' or 'NO').
IDENTITY_GENERATION | CHARACTER VARYING
Identity generation ('ALWAYS' or 'BY DEFAULT') for identity columns.
IDENTITY_START | BIGINT
The initial start value for identity columns.
IDENTITY_INCREMENT | BIGINT
The increment value for identity columns.
IDENTITY_MAXIMUM | BIGINT
The maximum value for identity columns.
IDENTITY_MINIMUM | BIGINT
The minimum value for identity columns.
IDENTITY_CYCLE | CHARACTER VARYING
Whether identity values are cycled ('YES' or 'NO') for identity columns.
IS_GENERATED | CHARACTER VARYING
Whether column is an generated column ('ALWAYS' or 'NEVER')
GENERATION_EXPRESSION | CHARACTER VARYING
The SQL of GENERATED ALWAYS AS expression for generated columns.
DECLARED_DATA_TYPE | CHARACTER VARYING
The declared SQL data type name for numeric data types.
DECLARED_NUMERIC_PRECISION | INTEGER
The declared precision, if any, for numeric data types.
DECLARED_NUMERIC_SCALE | INTEGER
The declared scale, if any, for numeric data types.
GEOMETRY_TYPE | CHARACTER VARYING
The geometry type constraint, if any, for geometry data types.
GEOMETRY_SRID | INTEGER
The geometry SRID (Spatial Reference Identifier) constraint, if any, for geometry data types.
IDENTITY_BASE | BIGINT
The current base value for identity columns.
IDENTITY_CACHE | BIGINT
The cache size for identity columns.
COLUMN_ON_UPDATE | CHARACTER VARYING
The SQL of ON UPDATE expression, if any.
IS_VISIBLE | BOOLEAN
Whether column is visible (included into SELECT *).
DEFAULT_ON_NULL | BOOLEAN
Whether value of DEFAULT expression is used when NULL value is inserted.
SELECTIVITY | INTEGER
The selectivity of a column (0-100), used to choose the best index.
REMARKS | CHARACTER VARYING
Optional remarks.
### COLUMN_PRIVILEGES Contains information about privileges of columns. H2 doesn't have per-column privileges, so this view actually contains privileges of their tables.
GRANTOR | CHARACTER VARYING
NULL.
GRANTEE | CHARACTER VARYING
The name of grantee.
TABLE_CATALOG | CHARACTER VARYING
The catalog (database name).
TABLE_SCHEMA | CHARACTER VARYING
The schema of the table.
TABLE_NAME | CHARACTER VARYING
The name of the table.
COLUMN_NAME | CHARACTER VARYING
The name of the column.
PRIVILEGE_TYPE | CHARACTER VARYING
'SELECT', 'INSERT', 'UPDATE', or 'DELETE'.
IS_GRANTABLE | CHARACTER VARYING
Whether grantee may grant rights to this object to others ('YES' or 'NO').
### CONSTANTS Contains information about constants.
CONSTANT_CATALOG | CHARACTER VARYING
The catalog (database name).
CONSTANT_SCHEMA | CHARACTER VARYING
The schema of the constant.
CONSTANT_NAME | CHARACTER VARYING
The name of the constant.
VALUE_DEFINITION | CHARACTER VARYING
The SQL of value.
DATA_TYPE | CHARACTER VARYING
The SQL data type name.
CHARACTER_MAXIMUM_LENGTH | BIGINT
The maximum length in characters for character string data types. For binary string data types contains the same value as CHARACTER_OCTET_LENGTH.
CHARACTER_OCTET_LENGTH | BIGINT
The maximum length in bytes for binary string data types. For character string data types contains the same value as CHARACTER_MAXIMUM_LENGTH.
CHARACTER_SET_CATALOG | CHARACTER VARYING
The catalog (database name) for character string data types.
CHARACTER_SET_SCHEMA | CHARACTER VARYING
The name of public schema for character string data types.
CHARACTER_SET_NAME | CHARACTER VARYING
The 'Unicode' for character string data types.
COLLATION_CATALOG | CHARACTER VARYING
The catalog (database name) for character string data types.
COLLATION_SCHEMA | CHARACTER VARYING
The name of public schema for character string data types.
COLLATION_NAME | CHARACTER VARYING
The name of collation for character string data types.
NUMERIC_PRECISION | INTEGER
The precision for numeric data types.
NUMERIC_PRECISION_RADIX | INTEGER
The radix of precision (2 or 10) for numeric data types.
NUMERIC_SCALE | INTEGER
The scale for numeric data types.
DATETIME_PRECISION | INTEGER
The fractional seconds precision for datetime data types.
INTERVAL_TYPE | CHARACTER VARYING
The data type of interval qualifier for interval data types.
INTERVAL_PRECISION | INTEGER
The leading field precision for interval data types.
MAXIMUM_CARDINALITY | INTEGER
The maximum cardinality for array data types.
DTD_IDENTIFIER | CHARACTER VARYING
The data type identifier to read additional information from INFORMATION_SCHEMA.ELEMENT_TYPES for array data types, INFORMATION_SCHEMA.ENUM_VALUES for ENUM data type, and INFORMATION_SCHEMA.FIELDS for row value data types.
DECLARED_DATA_TYPE | CHARACTER VARYING
The declared SQL data type name for numeric data types.
DECLARED_NUMERIC_PRECISION | INTEGER
The declared precision, if any, for numeric data types.
DECLARED_NUMERIC_SCALE | INTEGER
The declared scale, if any, for numeric data types.
GEOMETRY_TYPE | CHARACTER VARYING
The geometry type constraint, if any, for geometry data types.
GEOMETRY_SRID | INTEGER
The geometry SRID (Spatial Reference Identifier) constraint, if any, for geometry data types.
REMARKS | CHARACTER VARYING
Optional remarks.
### CONSTRAINT_COLUMN_USAGE Contains information about columns used in constraints.
TABLE_CATALOG | CHARACTER VARYING
The catalog (database name).
TABLE_SCHEMA | CHARACTER VARYING
The schema of the table.
TABLE_NAME | CHARACTER VARYING
The name of the table.
COLUMN_NAME | CHARACTER VARYING
The name of the column.
CONSTRAINT_CATALOG | CHARACTER VARYING
The catalog (database name).
CONSTRAINT_SCHEMA | CHARACTER VARYING
The schema of the constraint.
CONSTRAINT_NAME | CHARACTER VARYING
The name of the constraint.
### DOMAINS Contains information about domains.
DOMAIN_CATALOG | CHARACTER VARYING
The catalog (database name).
DOMAIN_SCHEMA | CHARACTER VARYING
The schema of domain.
DOMAIN_NAME | CHARACTER VARYING
The name of domain.
DATA_TYPE | CHARACTER VARYING
The SQL data type name.
CHARACTER_MAXIMUM_LENGTH | BIGINT
The maximum length in characters for character string data types. For binary string data types contains the same value as CHARACTER_OCTET_LENGTH.
CHARACTER_OCTET_LENGTH | BIGINT
The maximum length in bytes for binary string data types. For character string data types contains the same value as CHARACTER_MAXIMUM_LENGTH.
CHARACTER_SET_CATALOG | CHARACTER VARYING
The catalog (database name) for character string data types.
CHARACTER_SET_SCHEMA | CHARACTER VARYING
The name of public schema for character string data types.
CHARACTER_SET_NAME | CHARACTER VARYING
The 'Unicode' for character string data types.
COLLATION_CATALOG | CHARACTER VARYING
The catalog (database name) for character string data types.
COLLATION_SCHEMA | CHARACTER VARYING
The name of public schema for character string data types.
COLLATION_NAME | CHARACTER VARYING
The name of collation for character string data types.
NUMERIC_PRECISION | INTEGER
The precision for numeric data types.
NUMERIC_PRECISION_RADIX | INTEGER
The radix of precision (2 or 10) for numeric data types.
NUMERIC_SCALE | INTEGER
The scale for numeric data types.
DATETIME_PRECISION | INTEGER
The fractional seconds precision for datetime data types.
INTERVAL_TYPE | CHARACTER VARYING
The data type of interval qualifier for interval data types.
INTERVAL_PRECISION | INTEGER
The leading field precision for interval data types.
DOMAIN_DEFAULT | CHARACTER VARYING
The SQL of DEFAULT expression, if any.
MAXIMUM_CARDINALITY | INTEGER
The maximum cardinality for array data types.
DTD_IDENTIFIER | CHARACTER VARYING
The data type identifier to read additional information from INFORMATION_SCHEMA.ELEMENT_TYPES for array data types, INFORMATION_SCHEMA.ENUM_VALUES for ENUM data type, and INFORMATION_SCHEMA.FIELDS for row value data types.
DECLARED_DATA_TYPE | CHARACTER VARYING
The declared SQL data type name for numeric data types.
DECLARED_NUMERIC_PRECISION | INTEGER
The declared precision, if any, for numeric data types.
DECLARED_NUMERIC_SCALE | INTEGER
The declared scale, if any, for numeric data types.
GEOMETRY_TYPE | CHARACTER VARYING
The geometry type constraint, if any, for geometry data types.
GEOMETRY_SRID | INTEGER
The geometry SRID (Spatial Reference Identifier) constraint, if any, for geometry data types.
DOMAIN_ON_UPDATE | CHARACTER VARYING
The SQL of ON UPDATE expression, if any.
PARENT_DOMAIN_CATALOG | CHARACTER VARYING
The catalog (database name) for domains with parent domain.
PARENT_DOMAIN_SCHEMA | CHARACTER VARYING
The schema of parent domain for domains with parent domain.
PARENT_DOMAIN_NAME | CHARACTER VARYING
The name of parent domain for domains with parent domain.
REMARKS | CHARACTER VARYING
Optional remarks.
### DOMAIN_CONSTRAINTS Contains basic information about domain constraints. See also INFORMATION_SCHEMA.CHECK_CONSTRAINTS.
CONSTRAINT_CATALOG | CHARACTER VARYING
The catalog (database name).
CONSTRAINT_SCHEMA | CHARACTER VARYING
The schema of the constraint.
CONSTRAINT_NAME | CHARACTER VARYING
The name of the constraint.
DOMAIN_CATALOG | CHARACTER VARYING
The catalog (database name).
DOMAIN_SCHEMA | CHARACTER VARYING
The schema of domain.
DOMAIN_NAME | CHARACTER VARYING
The name of domain.
IS_DEFERRABLE | CHARACTER VARYING
'NO'.
INITIALLY_DEFERRED | CHARACTER VARYING
'NO'.
REMARKS | CHARACTER VARYING
Optional remarks.
### ELEMENT_TYPES Contains information about types of array elements.
OBJECT_CATALOG | CHARACTER VARYING
The catalog (database name).
OBJECT_SCHEMA | CHARACTER VARYING
The schema of the object.
OBJECT_NAME | CHARACTER VARYING
The name of the object.
OBJECT_TYPE | CHARACTER VARYING
The TYPE of the object ('CONSTANT', 'DOMAIN', 'TABLE', or 'ROUTINE').
COLLECTION_TYPE_IDENTIFIER | CHARACTER VARYING
The DTD_IDENTIFIER value of the object.
DATA_TYPE | CHARACTER VARYING
The SQL data type name.
CHARACTER_MAXIMUM_LENGTH | BIGINT
The maximum length in characters for character string data types. For binary string data types contains the same value as CHARACTER_OCTET_LENGTH.
CHARACTER_OCTET_LENGTH | BIGINT
The maximum length in bytes for binary string data types. For character string data types contains the same value as CHARACTER_MAXIMUM_LENGTH.
CHARACTER_SET_CATALOG | CHARACTER VARYING
The catalog (database name) for character string data types.
CHARACTER_SET_SCHEMA | CHARACTER VARYING
The name of public schema for character string data types.
CHARACTER_SET_NAME | CHARACTER VARYING
The 'Unicode' for character string data types.
COLLATION_CATALOG | CHARACTER VARYING
The catalog (database name) for character string data types.
COLLATION_SCHEMA | CHARACTER VARYING
The name of public schema for character string data types.
COLLATION_NAME | CHARACTER VARYING
The name of collation for character string data types.
NUMERIC_PRECISION | INTEGER
The precision for numeric data types.
NUMERIC_PRECISION_RADIX | INTEGER
The radix of precision (2 or 10) for numeric data types.
NUMERIC_SCALE | INTEGER
The scale for numeric data types.
DATETIME_PRECISION | INTEGER
The fractional seconds precision for datetime data types.
INTERVAL_TYPE | CHARACTER VARYING
The data type of interval qualifier for interval data types.
INTERVAL_PRECISION | INTEGER
The leading field precision for interval data types.
MAXIMUM_CARDINALITY | INTEGER
The maximum cardinality for array data types.
DTD_IDENTIFIER | CHARACTER VARYING
The data type identifier to read additional information from INFORMATION_SCHEMA.ELEMENT_TYPES for array data types, INFORMATION_SCHEMA.ENUM_VALUES for ENUM data type, and INFORMATION_SCHEMA.FIELDS for row value data types.
DECLARED_DATA_TYPE | CHARACTER VARYING
The declared SQL data type name for numeric data types.
DECLARED_NUMERIC_PRECISION | INTEGER
The declared precision, if any, for numeric data types.
DECLARED_NUMERIC_SCALE | INTEGER
The declared scale, if any, for numeric data types.
GEOMETRY_TYPE | CHARACTER VARYING
The geometry type constraint, if any, for geometry data types.
GEOMETRY_SRID | INTEGER
The geometry SRID (Spatial Reference Identifier) constraint, if any, for geometry data types.
### ENUM_VALUES Contains information about enum values.
OBJECT_CATALOG | CHARACTER VARYING
The catalog (database name).
OBJECT_SCHEMA | CHARACTER VARYING
The schema of the object.
OBJECT_NAME | CHARACTER VARYING
The name of the object.
OBJECT_TYPE | CHARACTER VARYING
The TYPE of the object ('CONSTANT', 'DOMAIN', 'TABLE', or 'ROUTINE').
ENUM_IDENTIFIER | CHARACTER VARYING
The DTD_IDENTIFIER value of the object.
VALUE_NAME | CHARACTER VARYING
The name of enum value.
VALUE_ORDINAL | CHARACTER VARYING
The ordinal of enum value.
### FIELDS Contains information about fields of row values.
OBJECT_CATALOG | CHARACTER VARYING
The catalog (database name).
OBJECT_SCHEMA | CHARACTER VARYING
The schema of the object.
OBJECT_NAME | CHARACTER VARYING
The name of the object.
OBJECT_TYPE | CHARACTER VARYING
The TYPE of the object ('CONSTANT', 'DOMAIN', 'TABLE', or 'ROUTINE').
ROW_IDENTIFIER | CHARACTER VARYING
The DTD_IDENTIFIER value of the object.
FIELD_NAME | CHARACTER VARYING
The name of the field of the row value.
ORDINAL_POSITION | INTEGER
The ordinal position (1-based).
DATA_TYPE | CHARACTER VARYING
The SQL data type name.
CHARACTER_MAXIMUM_LENGTH | BIGINT
The maximum length in characters for character string data types. For binary string data types contains the same value as CHARACTER_OCTET_LENGTH.
CHARACTER_OCTET_LENGTH | BIGINT
The maximum length in bytes for binary string data types. For character string data types contains the same value as CHARACTER_MAXIMUM_LENGTH.
CHARACTER_SET_CATALOG | CHARACTER VARYING
The catalog (database name) for character string data types.
CHARACTER_SET_SCHEMA | CHARACTER VARYING
The name of public schema for character string data types.
CHARACTER_SET_NAME | CHARACTER VARYING
The 'Unicode' for character string data types.
COLLATION_CATALOG | CHARACTER VARYING
The catalog (database name) for character string data types.
COLLATION_SCHEMA | CHARACTER VARYING
The name of public schema for character string data types.
COLLATION_NAME | CHARACTER VARYING
The name of collation for character string data types.
NUMERIC_PRECISION | INTEGER
The precision for numeric data types.
NUMERIC_PRECISION_RADIX | INTEGER
The radix of precision (2 or 10) for numeric data types.
NUMERIC_SCALE | INTEGER
The scale for numeric data types.
DATETIME_PRECISION | INTEGER
The fractional seconds precision for datetime data types.
INTERVAL_TYPE | CHARACTER VARYING
The data type of interval qualifier for interval data types.
INTERVAL_PRECISION | INTEGER
The leading field precision for interval data types.
MAXIMUM_CARDINALITY | INTEGER
The maximum cardinality for array data types.
DTD_IDENTIFIER | CHARACTER VARYING
The data type identifier to read additional information from INFORMATION_SCHEMA.ELEMENT_TYPES for array data types, INFORMATION_SCHEMA.ENUM_VALUES for ENUM data type, and INFORMATION_SCHEMA.FIELDS for row value data types.
DECLARED_DATA_TYPE | CHARACTER VARYING
The declared SQL data type name for numeric data types.
DECLARED_NUMERIC_PRECISION | INTEGER
The declared precision, if any, for numeric data types.
DECLARED_NUMERIC_SCALE | INTEGER
The declared scale, if any, for numeric data types.
GEOMETRY_TYPE | CHARACTER VARYING
The geometry type constraint, if any, for geometry data types.
GEOMETRY_SRID | INTEGER
The geometry SRID (Spatial Reference Identifier) constraint, if any, for geometry data types.
### INDEXES Contains information about indexes.
INDEX_CATALOG | CHARACTER VARYING
The catalog (database name).
INDEX_SCHEMA | CHARACTER VARYING
The schema of the index.
INDEX_NAME | CHARACTER VARYING
The name of the index.
TABLE_CATALOG | CHARACTER VARYING
The catalog (database name).
TABLE_SCHEMA | CHARACTER VARYING
The schema of the table.
TABLE_NAME | CHARACTER VARYING
The name of the table.
INDEX_TYPE_NAME | CHARACTER VARYING
The type of the index ('PRIMARY KEY', 'UNIQUE INDEX', 'SPATIAL INDEX', etc.)
NULLS_DISTINCT | CHARACTER VARYING
'YES' for unique indexes with distinct null values, 'NO' for unique indexes with not distinct null values, 'ALL' for multi-column unique indexes where only rows with null values in all unique columns are distinct, NULL for other types of indexes.
IS_GENERATED | BOOLEAN
Whether index is generated by a constraint and belongs to it.
REMARKS | CHARACTER VARYING
Optional remarks.
INDEX_CLASS | CHARACTER VARYING
The Java class name of index implementation.
### INDEX_COLUMNS Contains information about columns used in indexes.
INDEX_CATALOG | CHARACTER VARYING
The catalog (database name).
INDEX_SCHEMA | CHARACTER VARYING
The schema of the index.
INDEX_NAME | CHARACTER VARYING
The name of the index.
TABLE_CATALOG | CHARACTER VARYING
The catalog (database name).
TABLE_SCHEMA | CHARACTER VARYING
The schema of the table.
TABLE_NAME | CHARACTER VARYING
The name of the table.
COLUMN_NAME | CHARACTER VARYING
The name of the column.
ORDINAL_POSITION | INTEGER
The ordinal position (1-based).
ORDERING_SPECIFICATION | CHARACTER VARYING
'ASC' or 'DESC'.
NULL_ORDERING | CHARACTER VARYING
'FIRST', 'LAST', or NULL.
IS_UNIQUE | BOOLEAN
Whether this column is a part of unique column list of a unique index (TRUE or FALSE).
### INFORMATION_SCHEMA_CATALOG_NAME Contains a single row with the name of catalog (database name).
CATALOG_NAME | CHARACTER VARYING
The catalog (database name).
### IN_DOUBT Contains information about prepared transactions.
TRANSACTION_NAME | CHARACTER VARYING
The name of prepared transaction.
TRANSACTION_STATE | CHARACTER VARYING
The state of prepared transaction ('IN_DOUBT', 'COMMIT', or 'ROLLBACK').
### KEY_COLUMN_USAGE Contains information about columns used by primary key, unique, or referential constraint.
CONSTRAINT_CATALOG | CHARACTER VARYING
The catalog (database name).
CONSTRAINT_SCHEMA | CHARACTER VARYING
The schema of the constraint.
CONSTRAINT_NAME | CHARACTER VARYING
The name of the constraint.
TABLE_CATALOG | CHARACTER VARYING
The catalog (database name).
TABLE_SCHEMA | CHARACTER VARYING
The schema of the table.
TABLE_NAME | CHARACTER VARYING
The name of the table.
COLUMN_NAME | CHARACTER VARYING
The name of the column.
ORDINAL_POSITION | INTEGER
The ordinal position (1-based).
POSITION_IN_UNIQUE_CONSTRAINT | INTEGER
The ordinal position in the referenced unique constraint (1-based).
### LOCKS Contains information about tables locked by sessions.
TABLE_SCHEMA | CHARACTER VARYING
The schema of the table.
TABLE_NAME | CHARACTER VARYING
The name of the table.
SESSION_ID | INTEGER
The identifier of the session.
LOCK_TYPE | CHARACTER VARYING
'READ' or 'WRITE'.
### PARAMETERS Contains information about parameters of routines.
SPECIFIC_CATALOG | CHARACTER VARYING
The catalog (database name).
SPECIFIC_SCHEMA | CHARACTER VARYING
The schema of the overloaded version of routine.
SPECIFIC_NAME | CHARACTER VARYING
The name of the overloaded version of routine.
ORDINAL_POSITION | INTEGER
The ordinal position (1-based).
PARAMETER_MODE | CHARACTER VARYING
'IN'.
IS_RESULT | CHARACTER VARYING
'NO'.
AS_LOCATOR | CHARACTER VARYING
'YES' for LOBs, 'NO' for others.
PARAMETER_NAME | CHARACTER VARYING
The name of the parameter.
DATA_TYPE | CHARACTER VARYING
The SQL data type name.
CHARACTER_MAXIMUM_LENGTH | BIGINT
The maximum length in characters for character string data types. For binary string data types contains the same value as CHARACTER_OCTET_LENGTH.
CHARACTER_OCTET_LENGTH | BIGINT
The maximum length in bytes for binary string data types. For character string data types contains the same value as CHARACTER_MAXIMUM_LENGTH.
CHARACTER_SET_CATALOG | CHARACTER VARYING
The catalog (database name) for character string data types.
CHARACTER_SET_SCHEMA | CHARACTER VARYING
The name of public schema for character string data types.
CHARACTER_SET_NAME | CHARACTER VARYING
The 'Unicode' for character string data types.
COLLATION_CATALOG | CHARACTER VARYING
The catalog (database name) for character string data types.
COLLATION_SCHEMA | CHARACTER VARYING
The name of public schema for character string data types.
COLLATION_NAME | CHARACTER VARYING
The name of collation for character string data types.
NUMERIC_PRECISION | INTEGER
The precision for numeric data types.
NUMERIC_PRECISION_RADIX | INTEGER
The radix of precision (2 or 10) for numeric data types.
NUMERIC_SCALE | INTEGER
The scale for numeric data types.
DATETIME_PRECISION | INTEGER
The fractional seconds precision for datetime data types.
INTERVAL_TYPE | CHARACTER VARYING
The data type of interval qualifier for interval data types.
INTERVAL_PRECISION | INTEGER
The leading field precision for interval data types.
MAXIMUM_CARDINALITY | INTEGER
The maximum cardinality for array data types.
DTD_IDENTIFIER | CHARACTER VARYING
The data type identifier to read additional information from INFORMATION_SCHEMA.ELEMENT_TYPES for array data types, INFORMATION_SCHEMA.ENUM_VALUES for ENUM data type, and INFORMATION_SCHEMA.FIELDS for row value data types.
DECLARED_DATA_TYPE | CHARACTER VARYING
The declared SQL data type name for numeric data types.
DECLARED_NUMERIC_PRECISION | INTEGER
The declared precision, if any, for numeric data types.
DECLARED_NUMERIC_SCALE | INTEGER
The declared scale, if any, for numeric data types.
PARAMETER_DEFAULT | CHARACTER VARYING
NULL.
GEOMETRY_TYPE | CHARACTER VARYING
The geometry type constraint, if any, for geometry data types.
GEOMETRY_SRID | INTEGER
The geometry SRID (Spatial Reference Identifier) constraint, if any, for geometry data types.
### QUERY_STATISTICS Contains statistics of queries when query statistics gathering is enabled.
SQL_STATEMENT | CHARACTER VARYING
The SQL statement.
EXECUTION_COUNT | INTEGER
The execution count.
MIN_EXECUTION_TIME | DOUBLE PRECISION
The minimum execution time in milliseconds.
MAX_EXECUTION_TIME | DOUBLE PRECISION
The maximum execution time in milliseconds.
CUMULATIVE_EXECUTION_TIME | DOUBLE PRECISION
The total execution time in milliseconds.
AVERAGE_EXECUTION_TIME | DOUBLE PRECISION
The average execution time in milliseconds.
STD_DEV_EXECUTION_TIME | DOUBLE PRECISION
The standard deviation of execution time in milliseconds.
MIN_ROW_COUNT | BIGINT
The minimum number of rows.
MAX_ROW_COUNT | BIGINT
The maximum number of rows.
CUMULATIVE_ROW_COUNT | BIGINT
The total number of rows.
AVERAGE_ROW_COUNT | DOUBLE PRECISION
The average number of rows.
STD_DEV_ROW_COUNT | DOUBLE PRECISION
The standard deviation of number of rows.
### REFERENTIAL_CONSTRAINTS Contains additional information about referential constraints.
CONSTRAINT_CATALOG | CHARACTER VARYING
The catalog (database name).
CONSTRAINT_SCHEMA | CHARACTER VARYING
The schema of the constraint.
CONSTRAINT_NAME | CHARACTER VARYING
The name of the constraint.
UNIQUE_CONSTRAINT_CATALOG | CHARACTER VARYING
The catalog (database name).
UNIQUE_CONSTRAINT_SCHEMA | CHARACTER VARYING
The schema of referenced unique constraint.
UNIQUE_CONSTRAINT_NAME | CHARACTER VARYING
The name of referenced unique constraint.
MATCH_OPTION | CHARACTER VARYING
'NONE'.
UPDATE_RULE | CHARACTER VARYING
The rule for UPDATE in referenced table ('RESTRICT', 'CASCADE', 'SET DEFAULT', or 'SET NULL').
DELETE_RULE | CHARACTER VARYING
The rule for DELETE in referenced table ('RESTRICT', 'CASCADE', 'SET DEFAULT', or 'SET NULL').
### RIGHTS Contains information about granted rights and roles.
GRANTEE | CHARACTER VARYING
The name of grantee.
GRANTEETYPE | CHARACTER VARYING
'USER' if grantee is a user, 'ROLE' if grantee is a role.
GRANTEDROLE | CHARACTER VARYING
The name of the granted role for role grants.
RIGHTS | CHARACTER VARYING
The set of rights ('SELECT', 'DELETE', 'INSERT', 'UPDATE', or 'ALTER ANY SCHEMA' separated with ', ') for table grants.
TABLE_SCHEMA | CHARACTER VARYING
The schema of the table.
TABLE_NAME | CHARACTER VARYING
The name of the table.
### ROLES Contains information about roles.
ROLE_NAME | CHARACTER VARYING
The name of the role.
REMARKS | CHARACTER VARYING
Optional remarks.
### ROUTINES Contains information about user-defined routines, including aggregate functions.
SPECIFIC_CATALOG | CHARACTER VARYING
The catalog (database name).
SPECIFIC_SCHEMA | CHARACTER VARYING
The schema of the overloaded version of routine.
SPECIFIC_NAME | CHARACTER VARYING
The name of the overloaded version of routine.
ROUTINE_CATALOG | CHARACTER VARYING
The catalog (database name).
ROUTINE_SCHEMA | CHARACTER VARYING
The schema of the routine.
ROUTINE_NAME | CHARACTER VARYING
The name of the routine.
ROUTINE_TYPE | CHARACTER VARYING
'PROCEDURE', 'FUNCTION', or 'AGGREGATE'.
DATA_TYPE | CHARACTER VARYING
The SQL data type name.
CHARACTER_MAXIMUM_LENGTH | BIGINT
The maximum length in characters for character string data types. For binary string data types contains the same value as CHARACTER_OCTET_LENGTH.
CHARACTER_OCTET_LENGTH | BIGINT
The maximum length in bytes for binary string data types. For character string data types contains the same value as CHARACTER_MAXIMUM_LENGTH.
CHARACTER_SET_CATALOG | CHARACTER VARYING
The catalog (database name) for character string data types.
CHARACTER_SET_SCHEMA | CHARACTER VARYING
The name of public schema for character string data types.
CHARACTER_SET_NAME | CHARACTER VARYING
The 'Unicode' for character string data types.
COLLATION_CATALOG | CHARACTER VARYING
The catalog (database name) for character string data types.
COLLATION_SCHEMA | CHARACTER VARYING
The name of public schema for character string data types.
COLLATION_NAME | CHARACTER VARYING
The name of collation for character string data types.
NUMERIC_PRECISION | INTEGER
The precision for numeric data types.
NUMERIC_PRECISION_RADIX | INTEGER
The radix of precision (2 or 10) for numeric data types.
NUMERIC_SCALE | INTEGER
The scale for numeric data types.
DATETIME_PRECISION | INTEGER
The fractional seconds precision for datetime data types.
INTERVAL_TYPE | CHARACTER VARYING
The data type of interval qualifier for interval data types.
INTERVAL_PRECISION | INTEGER
The leading field precision for interval data types.
MAXIMUM_CARDINALITY | INTEGER
The maximum cardinality for array data types.
DTD_IDENTIFIER | CHARACTER VARYING
The data type identifier to read additional information from INFORMATION_SCHEMA.ELEMENT_TYPES for array data types, INFORMATION_SCHEMA.ENUM_VALUES for ENUM data type, and INFORMATION_SCHEMA.FIELDS for row value data types.
ROUTINE_BODY | CHARACTER VARYING
'EXTERNAL'.
ROUTINE_DEFINITION | CHARACTER VARYING
Source code or NULL if not applicable or user doesn't have ADMIN privileges.
EXTERNAL_NAME | CHARACTER VARYING
The name of the class or method.
EXTERNAL_LANGUAGE | CHARACTER VARYING
'JAVA'.
PARAMETER_STYLE | CHARACTER VARYING
'GENERAL'.
IS_DETERMINISTIC | CHARACTER VARYING
Whether routine is deterministic ('YES' or 'NO').
DECLARED_DATA_TYPE | CHARACTER VARYING
The declared SQL data type name for numeric data types.
DECLARED_NUMERIC_PRECISION | INTEGER
The declared precision, if any, for numeric data types.
DECLARED_NUMERIC_SCALE | INTEGER
The declared scale, if any, for numeric data types.
GEOMETRY_TYPE | CHARACTER VARYING
The geometry type constraint, if any, for geometry data types.
GEOMETRY_SRID | INTEGER
The geometry SRID (Spatial Reference Identifier) constraint, if any, for geometry data types.
REMARKS | CHARACTER VARYING
Optional remarks.
### SCHEMATA Contains information about schemas.
CATALOG_NAME | CHARACTER VARYING
The catalog (database name).
SCHEMA_NAME | CHARACTER VARYING
The schema name.
SCHEMA_OWNER | CHARACTER VARYING
The name of schema owner.
DEFAULT_CHARACTER_SET_CATALOG | CHARACTER VARYING
The catalog (database name).
DEFAULT_CHARACTER_SET_SCHEMA | CHARACTER VARYING
The name of public schema.
DEFAULT_CHARACTER_SET_NAME | CHARACTER VARYING
'Unicode'.
SQL_PATH | CHARACTER VARYING
NULL.
DEFAULT_COLLATION_NAME | CHARACTER VARYING
The name of database collation.
REMARKS | CHARACTER VARYING
Optional remarks.
### SEQUENCES Contains information about sequences.
SEQUENCE_CATALOG | CHARACTER VARYING
The catalog (database name).
SEQUENCE_SCHEMA | CHARACTER VARYING
The schema of the sequence.
SEQUENCE_NAME | CHARACTER VARYING
The name of the sequence.
DATA_TYPE | CHARACTER VARYING
The SQL data type name.
NUMERIC_PRECISION | INTEGER
The precision for numeric data types.
NUMERIC_PRECISION_RADIX | INTEGER
The radix of precision (2 or 10) for numeric data types.
NUMERIC_SCALE | INTEGER
The scale for numeric data types.
START_VALUE | BIGINT
The initial start value.
MINIMUM_VALUE | BIGINT
The minimum value.
MAXIMUM_VALUE | BIGINT
The maximum value.
INCREMENT | BIGINT
The increment value.
CYCLE_OPTION | CHARACTER VARYING
Whether values are cycled ('YES' or 'NO').
DECLARED_DATA_TYPE | CHARACTER VARYING
The declared SQL data type name for numeric data types.
DECLARED_NUMERIC_PRECISION | INTEGER
The declared precision, if any, for numeric data types.
DECLARED_NUMERIC_SCALE | INTEGER
The declared scale, if any, for numeric data types.
BASE_VALUE | BIGINT
The current base value.
CACHE | BIGINT
The cache size.
REMARKS | CHARACTER VARYING
Optional remarks.
### SESSIONS Contains information about sessions. Only users with ADMIN privileges can see all sessions, other users can see only own session.
SESSION_ID | INTEGER
The identifier of the session.
USER_NAME | CHARACTER VARYING
The name of the user.
SERVER | CHARACTER VARYING
The name of the server used by remote connection.
CLIENT_ADDR | CHARACTER VARYING
The client address and port used by remote connection.
CLIENT_INFO | CHARACTER VARYING
Additional client information provided by remote connection.
SESSION_START | TIMESTAMP(9) WITH TIME ZONE
When this session was started.
ISOLATION_LEVEL | CHARACTER VARYING
The isolation level of the session ('READ UNCOMMITTED', 'READ COMMITTED', 'REPEATABLE READ', 'SNAPSHOT', or 'SERIALIZABLE').
EXECUTING_STATEMENT | CHARACTER VARYING
The currently executing statement, if any.
EXECUTING_STATEMENT_START | TIMESTAMP(9) WITH TIME ZONE
When the current command was started, if any.
CONTAINS_UNCOMMITTED | BOOLEAN
Whether the session contains any uncommitted changes.
SESSION_STATE | CHARACTER VARYING
The state of the session ('RUNNING', 'SLEEP', etc.)
BLOCKER_ID | INTEGER
The identifier or blocking session, if any.
SLEEP_SINCE | TIMESTAMP(9) WITH TIME ZONE
When the last command was finished if session is sleeping.
### SESSION_STATE Contains the state of the current session.
STATE_KEY | CHARACTER VARYING
The key.
STATE_COMMAND | CHARACTER VARYING
The SQL command that can be used to restore the state.
### SETTINGS Contains values of various settings.
SETTING_NAME | CHARACTER VARYING
The name of the setting.
SETTING_VALUE | CHARACTER VARYING
The value of the setting.
### SYNONYMS Contains information about table synonyms.
SYNONYM_CATALOG | CHARACTER VARYING
The catalog (database name).
SYNONYM_SCHEMA | CHARACTER VARYING
The schema of the synonym.
SYNONYM_NAME | CHARACTER VARYING
The name of the synonym.
SYNONYM_FOR | CHARACTER VARYING
The name of the referenced table.
SYNONYM_FOR_SCHEMA | CHARACTER VARYING
The name of the referenced schema.
TYPE_NAME | CHARACTER VARYING
'SYNONYM'.
STATUS | CHARACTER VARYING
'VALID'.
REMARKS | CHARACTER VARYING
Optional remarks.
### TABLES Contains information about tables. See also INFORMATION_SCHEMA.COLUMNS.
TABLE_CATALOG | CHARACTER VARYING
The catalog (database name).
TABLE_SCHEMA | CHARACTER VARYING
The schema of the table.
TABLE_NAME | CHARACTER VARYING
The name of the table.
TABLE_TYPE | CHARACTER VARYING
'BASE TABLE', 'VIEW', 'GLOBAL TEMPORARY', or 'LOCAL TEMPORARY'.
IS_INSERTABLE_INTO | CHARACTER VARYING
Whether the table is insertable ('YES' or 'NO').
COMMIT_ACTION | CHARACTER VARYING
'DELETE', 'DROP', or 'PRESERVE' for temporary tables.
STORAGE_TYPE | CHARACTER VARYING
'CACHED' for regular persisted tables, 'MEMORY' for in-memory tables or persisted tables with in-memory indexes, 'GLOBAL TEMPORARY' or 'LOCAL TEMPORARY' for temporary tables, 'EXTERNAL' for tables with external table engines, or 'TABLE LINK' for linked tables.
REMARKS | CHARACTER VARYING
Optional remarks.
LAST_MODIFICATION | BIGINT
The sequence number of the last modification, if applicable.
TABLE_CLASS | CHARACTER VARYING
The Java class name of implementation.
ROW_COUNT_ESTIMATE | BIGINT
The approximate number of rows if known or some default value if unknown. For regular tables contains the total number of rows including the uncommitted rows.
### TABLE_CONSTRAINTS Contains basic information about table constraints (check, primary key, unique, and referential).
CONSTRAINT_CATALOG | CHARACTER VARYING
The catalog (database name).
CONSTRAINT_SCHEMA | CHARACTER VARYING
The schema of the constraint.
CONSTRAINT_NAME | CHARACTER VARYING
The name of the constraint.
CONSTRAINT_TYPE | CHARACTER VARYING
'CHECK', 'PRIMARY KEY', 'UNIQUE', or 'REFERENTIAL'.
TABLE_CATALOG | CHARACTER VARYING
The catalog (database name).
TABLE_SCHEMA | CHARACTER VARYING
The schema of the table.
TABLE_NAME | CHARACTER VARYING
The name of the table.
IS_DEFERRABLE | CHARACTER VARYING
'NO'.
INITIALLY_DEFERRED | CHARACTER VARYING
'NO'.
ENFORCED | CHARACTER VARYING
'YES' for non-referential constants. 'YES' for referential constants when checks for referential integrity are enabled for the both referenced and referencing tables and 'NO' when they are disabled.
NULLS_DISTINCT | CHARACTER VARYING
'YES' for unique constraints with distinct null values, 'NO' for unique constraints with not distinct null values, 'ALL' for multi-column unique constraints where only rows with null values in all unique columns are distinct, NULL for other types of constraints.
INDEX_CATALOG | CHARACTER VARYING
The catalog (database name).
INDEX_SCHEMA | CHARACTER VARYING
The schema of the index.
INDEX_NAME | CHARACTER VARYING
The name of the index.
REMARKS | CHARACTER VARYING
Optional remarks.
### TABLE_PRIVILEGES Contains information about privileges of tables. See INFORMATION_SCHEMA.CHECK_CONSTRAINTS, INFORMATION_SCHEMA.KEY_COLUMN_USAGE, and INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS for additional information.
GRANTOR | CHARACTER VARYING
NULL.
GRANTEE | CHARACTER VARYING
The name of grantee.
TABLE_CATALOG | CHARACTER VARYING
The catalog (database name).
TABLE_SCHEMA | CHARACTER VARYING
The schema of the table.
TABLE_NAME | CHARACTER VARYING
The name of the table.
PRIVILEGE_TYPE | CHARACTER VARYING
'SELECT', 'INSERT', 'UPDATE', or 'DELETE'.
IS_GRANTABLE | CHARACTER VARYING
Whether grantee may grant rights to this object to others ('YES' or 'NO').
WITH_HIERARCHY | CHARACTER VARYING
'NO'.
### TRIGGERS Contains information about triggers.
TRIGGER_CATALOG | CHARACTER VARYING
The catalog (database name).
TRIGGER_SCHEMA | CHARACTER VARYING
The schema of the trigger.
TRIGGER_NAME | CHARACTER VARYING
The name of the trigger.
EVENT_MANIPULATION | CHARACTER VARYING
'INSERT', 'UPDATE', 'DELETE', or 'SELECT'.
EVENT_OBJECT_CATALOG | CHARACTER VARYING
The catalog (database name).
EVENT_OBJECT_SCHEMA | CHARACTER VARYING
The schema of the table.
EVENT_OBJECT_TABLE | CHARACTER VARYING
The name of the table.
ACTION_ORIENTATION | CHARACTER VARYING
'ROW' or 'STATEMENT'.
ACTION_TIMING | CHARACTER VARYING
'BEFORE', 'AFTER', or 'INSTEAD OF'.
IS_ROLLBACK | BOOLEAN
Whether this trigger is executed on rollback.
JAVA_CLASS | CHARACTER VARYING
The Java class name.
QUEUE_SIZE | INTEGER
The size of the queue (is not actually used).
NO_WAIT | BOOLEAN
Whether trigger is defined with NO WAIT clause (is not actually used).
REMARKS | CHARACTER VARYING
Optional remarks.
### USERS Contains information about users. Only users with ADMIN privileges can see all users, other users can see only themselves.
USER_NAME | CHARACTER VARYING
The name of the user.
IS_ADMIN | BOOLEAN
Whether user has ADMIN privileges.
REMARKS | CHARACTER VARYING
Optional remarks.
### VIEWS Contains additional information about views. See INFORMATION_SCHEMA.TABLES for basic information.
TABLE_CATALOG | CHARACTER VARYING
The catalog (database name).
TABLE_SCHEMA | CHARACTER VARYING
The schema of the table.
TABLE_NAME | CHARACTER VARYING
The name of the table.
VIEW_DEFINITION | CHARACTER VARYING
The query SQL, if applicable.
CHECK_OPTION | CHARACTER VARYING
'NONE'.
IS_UPDATABLE | CHARACTER VARYING
'NO'.
INSERTABLE_INTO | CHARACTER VARYING
'NO'.
IS_TRIGGER_UPDATABLE | CHARACTER VARYING
Whether the view has INSTEAD OF trigger for UPDATE ('YES' or 'NO').
IS_TRIGGER_DELETABLE | CHARACTER VARYING
Whether the view has INSTEAD OF trigger for DELETE ('YES' or 'NO').
IS_TRIGGER_INSERTABLE_INTO | CHARACTER VARYING
Whether the view has INSTEAD OF trigger for INSERT ('YES' or 'NO').
STATUS | CHARACTER VARYING
'VALID' or 'INVALID'.
REMARKS | CHARACTER VARYING
Optional remarks.
## Range Table The range table is a dynamic system table that contains all values from a start to an end value. Non-zero step value may be also specified, default is 1. Start value, end value, and optional step value are converted to BIGINT data type. The table contains one column called X. If start value is greater than end value and step is positive the result is empty. If start value is less than end value and step is negative the result is empty too. If start value is equal to end value the result contains only start value. Start value, start value plus step, start value plus step multiplied by two and so on are included in result. If step is positive the last value is less than or equal to the specified end value. If step in negative the last value is greater than or equal to the specified end value. The table is used as follows: Examples:
    SELECT X FROM SYSTEM_RANGE(1, 10);
    -- 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
    SELECT X FROM SYSTEM_RANGE(1, 10, 2);
    -- 1, 3, 5, 7, 9
    SELECT X FROM SYSTEM_RANGE(1, 10, -1);
    -- No rows
    SELECT X FROM SYSTEM_RANGE(10, 2, -2);
    -- 10, 8, 6, 4, 2