System Tables

Information Schema

The system tables in the schema INFORMATION_SCHEMA contain the meta data of all tables in the database as well as the current settings.

CATALOGS

CATALOG_NAME

COLLATIONS

NAME, KEY

COLUMNS

TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION, INTERVAL_TYPE, INTERVAL_PRECISION, CHARACTER_SET_NAME, COLLATION_NAME, TYPE_NAME, NULLABLE, IS_COMPUTED, SELECTIVITY, CHECK_CONSTRAINT, SEQUENCE_NAME, REMARKS, SOURCE_DATA_TYPE, COLUMN_TYPE, COLUMN_ON_UPDATE, IS_VISIBLE

COLUMN_PRIVILEGES

GRANTOR, GRANTEE, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, IS_GRANTABLE

CONSTANTS

CONSTANT_CATALOG, CONSTANT_SCHEMA, CONSTANT_NAME, DATA_TYPE, REMARKS, SQL, ID

CONSTRAINTS

CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, UNIQUE_INDEX_NAME, CHECK_EXPRESSION, COLUMN_LIST, REMARKS, SQL, ID

CROSS_REFERENCES

PKTABLE_CATALOG, PKTABLE_SCHEMA, PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_CATALOG, FKTABLE_SCHEMA, FKTABLE_NAME, FKCOLUMN_NAME, ORDINAL_POSITION, UPDATE_RULE, DELETE_RULE, FK_NAME, PK_NAME, DEFERRABILITY

DOMAINS

DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, PRECISION, SCALE, TYPE_NAME, SELECTIVITY, CHECK_CONSTRAINT, REMARKS, SQL, ID

FUNCTION_ALIASES

ALIAS_CATALOG, ALIAS_SCHEMA, ALIAS_NAME, JAVA_CLASS, JAVA_METHOD, DATA_TYPE, TYPE_NAME, COLUMN_COUNT, RETURNS_RESULT, REMARKS, ID, SOURCE

FUNCTION_COLUMNS

ALIAS_CATALOG, ALIAS_SCHEMA, ALIAS_NAME, JAVA_CLASS, JAVA_METHOD, COLUMN_COUNT, POS, COLUMN_NAME, DATA_TYPE, TYPE_NAME, PRECISION, SCALE, RADIX, NULLABLE, COLUMN_TYPE, REMARKS, COLUMN_DEFAULT

HELP

ID, SECTION, TOPIC, SYNTAX, TEXT

INDEXES

TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, NON_UNIQUE, INDEX_NAME, ORDINAL_POSITION, COLUMN_NAME, CARDINALITY, PRIMARY_KEY, INDEX_TYPE_NAME, IS_GENERATED, INDEX_TYPE, ASC_OR_DESC, PAGES, FILTER_CONDITION, REMARKS, SQL, ID, SORT_TYPE, CONSTRAINT_NAME, INDEX_CLASS, AFFINITY

IN_DOUBT

TRANSACTION, STATE

KEY_COLUMN_USAGE

CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, POSITION_IN_UNIQUE_CONSTRAINT

LOCKS

TABLE_SCHEMA, TABLE_NAME, SESSION_ID, LOCK_TYPE

QUERY_STATISTICS

SQL_STATEMENT, EXECUTION_COUNT, MIN_EXECUTION_TIME, MAX_EXECUTION_TIME, CUMULATIVE_EXECUTION_TIME, AVERAGE_EXECUTION_TIME, STD_DEV_EXECUTION_TIME, MIN_ROW_COUNT, MAX_ROW_COUNT, CUMULATIVE_ROW_COUNT, AVERAGE_ROW_COUNT, STD_DEV_ROW_COUNT

REFERENTIAL_CONSTRAINTS

CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, UNIQUE_CONSTRAINT_CATALOG, UNIQUE_CONSTRAINT_SCHEMA, UNIQUE_CONSTRAINT_NAME, MATCH_OPTION, UPDATE_RULE, DELETE_RULE

RIGHTS

GRANTEE, GRANTEETYPE, GRANTEDROLE, RIGHTS, TABLE_SCHEMA, TABLE_NAME, ID

ROLES

NAME, REMARKS, ID

SCHEMATA

CATALOG_NAME, SCHEMA_NAME, SCHEMA_OWNER, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME, IS_DEFAULT, REMARKS, ID

SEQUENCES

SEQUENCE_CATALOG, SEQUENCE_SCHEMA, SEQUENCE_NAME, CURRENT_VALUE, INCREMENT, IS_GENERATED, REMARKS, CACHE, MIN_VALUE, MAX_VALUE, IS_CYCLE, ID

SESSIONS

ID, USER_NAME, SESSION_START, STATEMENT, STATEMENT_START, CONTAINS_UNCOMMITTED, STATE, BLOCKER_ID

SESSION_STATE

KEY, SQL

SETTINGS

NAME, VALUE

SYNONYMS

SYNONYM_CATALOG, SYNONYM_SCHEMA, SYNONYM_NAME, SYNONYM_FOR, SYNONYM_FOR_SCHEMA, TYPE_NAME, STATUS, REMARKS, ID

TABLES

TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, STORAGE_TYPE, SQL, REMARKS, LAST_MODIFICATION, ID, TYPE_NAME, TABLE_CLASS, ROW_COUNT_ESTIMATE

TABLE_CONSTRAINTS

CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, IS_DEFERRABLE, INITIALLY_DEFERRED

TABLE_PRIVILEGES

GRANTOR, GRANTEE, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, IS_GRANTABLE

TABLE_TYPES

TYPE

TRIGGERS

TRIGGER_CATALOG, TRIGGER_SCHEMA, TRIGGER_NAME, TRIGGER_TYPE, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, BEFORE, JAVA_CLASS, QUEUE_SIZE, NO_WAIT, REMARKS, SQL, ID

TYPE_INFO

TYPE_NAME, DATA_TYPE, PRECISION, PREFIX, SUFFIX, PARAMS, AUTO_INCREMENT, MINIMUM_SCALE, MAXIMUM_SCALE, RADIX, POS, CASE_SENSITIVE, NULLABLE, SEARCHABLE

USERS

NAME, ADMIN, REMARKS, ID

VIEWS

TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION, CHECK_OPTION, IS_UPDATABLE, STATUS, REMARKS, ID

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