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

AltStyle によって変換されたページ (->オリジナル) /