▲
Search:
| Highlight keyword(s)
Home Download Cheat Sheet Documentation Quickstart Installation Tutorial Features Performance Advanced Reference Commands Functions Data Types SQL Grammar System Tables Javadoc PDF (1.5 MB) Support FAQ Error Analyzer Google Group (English) Google Group (Japanese) Google Group (Chinese) Appendix History & Roadmap License Build Links MVStore Architecture |  | # System Tables Information Schema Range Table ## 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