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, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, CHARACTER_SET_NAME, COLLATION_NAME, TYPE_NAME, NULLABLE, IS_COMPUTED, SELECTIVITY, CHECK_CONSTRAINT, SEQUENCE_NAME, REMARKS, SOURCE_DATA_TYPE, COLUMN_TYPE, COLUMN_ON_UPDATE
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
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. The table contains one column called X. Both the start and end values are included in the result. The table is used as follows:
Example:
SELECT X FROM SYSTEM_RANGE(1, 10);