Data Dictionary Views

This chapter documents the predefined system views on the data dictionary tables. PUBLIC holds SELECT access on these views, so that they may be examined by any user.

INFORMATION_SCHEMA views are used to retrieve information about the objects in the data dictionary.

An INFORMATION_SCHEMA view can be read with the statement (note the qualified form of view-name):

SELECT column-list

FROM INFORMATION_SCHEMA.view-name

WHERE condition

 

Many of the views include only objects, privileges and so on relevant to the current ident (the description for each view indicates exactly what information is shown in the view).

Note:Some of the views have columns designed to display information that is not currently supported by Mimer SQL (e.g. catalog names for database objects), in this situation the empty string ("") will be shown in these columns.

The tables in the data dictionary may be read directly only by the system administrator ident SYSADM in the default installation. The base tables in the data dictionary are documented in the Mimer SQL System Management Handbook. The system administrator may, if desired, grant SELECT access on the dictionary tables to other users.

No user may access the data dictionary views or tables directly for any purpose other than SELECT. All data dictionary maintenance is performed by internal routines and is invisible to the user.

INFORMATION_SCHEMA dictionary views

The table below summarizes the data dictionary views that are part of the schema INFORMATION_SCHEMA:

View name

Description

INFORMATION_SCHEMA.ASSERTIONS

Owned assertions.

INFORMATION_SCHEMA.ATTRIBUTES

Owned user-defined type attributes.

INFORMATION_SCHEMA.CHARACTER_SETS

Accessible character sets.

INFORMATION_SCHEMA.CHECK_CONSTRAINTS

Owned check constraints.

INFORMATION_SCHEMA.COLLATIONS

Accessible collations.

INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE

Columns defined using owned domains.

INFORMATION_SCHEMA.COLUMN_PRIVILEGES

Privileges granted on accessible table columns.

INFORMATION_SCHEMA.COLUMN_UDT_USAGE

Columns defined using owned user-defined types.

INFORMATION_SCHEMA.COLUMNS

Accessible table columns.

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

Columns referenced by owned referential, unique, check or assertion constraints.

INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

Tables on which owned referential, unique, check or assertion constraints are defined.

INFORMATION_SCHEMA.DIRECT_SUPERTABLES

Information about inheritance relations between tables.

INFORMATION_SCHEMA.DIRECT_SUPERTYPES

Information about inheritance relations between user-defined types.

INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS

Constraints of accessible domains.

INFORMATION_SCHEMA.DOMAINS

Accessible domains.

INFORMATION_SCHEMA.EXT_ACCESS_PATHS

All explicit and implicit indexes for tables.

INFORMATION_SCHEMA.EXT_COLLATION_DEFINITIONS

Collation definitions.

INFORMATION_SCHEMA.EXT_COLUMN_OFFSET_INFORMATION

Physical structure of columns in a table.

INFORMATION_SCHEMA.EXT_COLUMN_REMARKS

Comments for accessible table columns.

INFORMATION_SCHEMA.EXT_DATABANKS

Accessible databanks.

INFORMATION_SCHEMA.EXT_IDENTS

Accessible authorization idents.

INFORMATION_SCHEMA.EXT_INDEX_COLUMN_USAGE

Accessible table columns on which indexes depend.

INFORMATION_SCHEMA.EXT_INDEXES

Accessible indexes.

INFORMATION_SCHEMA.EXT_OBJECT_IDENT_USAGE

Accessible objects created by authorization ident.

INFORMATION_SCHEMA.EXT_OBJECT_OBJECT_USED

Accessible objects used by other objects.

INFORMATION_SCHEMA.EXT_OBJECT_OBJECT_USING

Accessible objects using other objects.

INFORMATION_SCHEMA.EXT_OBJECT_PRIVILEGES

Object privileges granted to an authorization ident.

INFORMATION_SCHEMA.EXT_ONEROW

Dummy view with one single row.

INFORMATION_SCHEMA.EXT_ROUTINE_MODULE_DEFINITION

Source definition for routines defined in modules.

INFORMATION_SCHEMA.EXT_ROUTINE_MODULE_USAGE

Accessible routines in a module.

INFORMATION_SCHEMA.EXT_SCHEMAS

Schemas containing objects to which current user have some access.

INFORMATION_SCHEMA.EXT_SEQUENCES

Accessible sequences.

INFORMATION_SCHEMA.EXT_SHADOWS

Accessible shadows.

INFORMATION_SCHEMA.EXT_SOURCE_DEFINITION

Text definition for owned objects.

INFORMATION_SCHEMA.EXT_STATEMENT_DEFINITION

Shows a textual definition of the precompiled statements available to the current ident.

INFORMATION_SCHEMA.EXT_STATEMENTS

Shows all precompiled statements available to the current ident.

INFORMATION_SCHEMA.EXT_STATISTICS

Statistics for table.

INFORMATION_SCHEMA.EXT_SYNONYMS

Accessible synonyms.

INFORMATION_SCHEMA.EXT_SYSTEM_PRIVILEGES

System privileges granted to an authorization ident.

INFORMATION_SCHEMA.EXT_TABLE_DATABANK_USAGE

Owned databanks on which tables depend.

INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Table columns constrained as keys by accessible tables.

INFORMATION_SCHEMA.METHOD_SPECIFICATION_PARAMETERS

Accessible method’s parameters.

INFORMATION_SCHEMA.METHOD_SPECIFICATIONS

Accessible method specifications.

INFORMATION_SCHEMA.MODULES

Owned modules.

INFORMATION_SCHEMA.PARAMETERS

Parameters of accessible routines.

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

Accessible tables’ referential constraints.

INFORMATION_SCHEMA.ROUTINE_COLUMN_USAGE

Owned table columns on which routines depend.

INFORMATION_SCHEMA.ROUTINE_PRIVILEGES

Privileges held on accessible routines.

INFORMATION_SCHEMA.ROUTINE_TABLE_USAGE

Owned tables on which routines depend.

INFORMATION_SCHEMA.ROUTINES

Accessible routines.

INFORMATION_SCHEMA.SCHEMATA

Owned schemas.

INFORMATION_SCHEMA.SEQUENCES

Accessible sequences.

INFORMATION_SCHEMA.SQL_FEATURES

Features and subfeatures of SQL-2016.

INFORMATION_SCHEMA.SQL_LANGUAGES

Conformance levels for supported SQL language options and dialects.

INFORMATION_SCHEMA.SQL_SIZING

Sizing items.

INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Accessible tables’ constraints.

INFORMATION_SCHEMA.TABLE_PRIVILEGES

Privileges held on accessible tables.

INFORMATION_SCHEMA.TABLES

Accessible tables.

INFORMATION_SCHEMA.TRANSLATIONS

Accessible character set translations.

INFORMATION_SCHEMA.TRIGGER_COLUMN_USAGE

Owned columns referenced from a trigger action.

INFORMATION_SCHEMA.TRIGGER_TABLE_USAGE

Tables on which owned triggers depend.

INFORMATION_SCHEMA.TRIGGERED_UPDATE_COLUMNS

Owned columns referenced from UPDATE trigger column lists.

INFORMATION_SCHEMA.TRIGGERS

Owned triggers.

INFORMATION_SCHEMA.UDT_PRIVILEGES

Privileges for accessible user-defined types.

INFORMATION_SCHEMA.USAGE_PRIVILEGES

USAGE privilege held on accessible objects.

INFORMATION_SCHEMA.USER_DEFINED_TYPES

Owned user-defined types.

INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

Columns on which owned views depend.

INFORMATION_SCHEMA.VIEW_TABLE_USAGE

Tables on which owned views depend.

INFORMATION_SCHEMA.VIEWS

Accessible views.

INFORMATION_SCHEMA.ASSERTIONS

The ASSERTIONS system view shows all assertions owned by the current ident.

Column name

Data type

Description

CONSTRAINT_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the assertion.

CONSTRAINT_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the assertion.

CONSTRAINT_NAME

NCHAR VARYING(128)

The name of the assertion.

IS_DEFERRABLE

VARCHAR(3)

One of:
YES = the assertion is deferrable
NO = the assertion is not deferrable

INITIALLY_DEFERRED

VARCHAR(3)

One of:
YES = the assertion is immediate
NO = the assertion is deferred.

INFORMATION_SCHEMA.ATTRIBUTES

Contains one row for each attribute of a user-defined type accessible to the current user

Column name

Data type

Description

UDT_CATALOG

NCHAR VARYING(128)

Name of catalog containing user-defined type.

UDT_SCHEMA

NCHAR VARYING(128)

Name of schema containing user-defined type

UDT_NAME

NCHAR VARYING(128)

Name of user-defined type

ATTRIBUTE_NAME

NCHAR VARYING(128)

Name of attribute

ORDINAL_POSITION

INTEGER

Ordinal position for attribute within user-defined type

ATTRIBUTE_DEFAULT

NCHAR VARYING(400)

Default value for attribute

IS_NULLABLE

VARCHAR(3)

Nullability attribute
YES = The attribute can be null
NO = The attribute can not be null

DATA_TYPE

VARCHAR(30)

The type of the attribute. One of:
BIGINT
BINARY
BINARY VARYING
BINARY LARGE OBJECT
BOOLEAN
CHARACTER
CHARACTER VARYING
CHARACTER LARGE OBJECT
DATE
DECIMAL
DOUBLE PRECISION
FLOAT
Float(p)
INTEGER
Integer(p)
INTERVAL
NATIONAL CHARACTER
NATIONAL CHARACTER VARYING
NATIONAL CHARACTER LARGE OBJECT
NUMERIC
REAL
SMALLINT
TIME
TIMESTAMP
USER-DEFINED

CHARACETR_MAXIMUM_LENGTH

INTEGER

For a character data type, this shows the maximum length in characters. For all other data types it is the null value.

CHARACTER_OCTET_
LENGTH

INTEGER

For a character data type, this shows the maximum length in octets. For all other data types it is the null value. It the same value as CHARACTER_MAXIMUM length for single octet data types.

NUMERIC_PRECISION

INTEGER

For NUMERIC data types, this shows the total number of significant digits allowed in the column. For all other data types it is the null value.

NUMERIC_PRECISION_RADIX

INTEGER

This shows whether the NUMERIC_PRECISION is given in a binary or decimal radix. The numeric radix is always decimal in Mimer SQL, therefore the value 10 is always shown for numeric data types. For all other data types it is the null value.

NUMERIC_SCALE

INTEGER

For NUMERIC and DECIMAL, this defines the total number of significant digits to the right of the decimal point. For BIGINT, INTEGER and SMALLINT, this is 0. For all other data types, it is the null value.

DATETIME_PRECISION

INTEGER

For DATE, TIME, TIMESTAMP and INTERVAL data types, this column contains the number of digits of precision for the fractional seconds component. For other data types it is the null value.

INTERVAL_TYPE

VARCHAR(30)

For INTERVAL data types, this is a character string specifying the interval qualifier. Can be one of:
YEAR
YEAR TO MONTH
DAY
HOUR
MINUTE
SECOND
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND
.

For other data types it is the null value.

INTERVAL_PRECISION

INTEGER

For INTERVAL data types, this is the number of significant digits for the interval leading precision (see Interval Literals). For other data types it is the null value.

CHARACTER_SET_
CATALOG

NCHAR VARYING(128)

The name of the catalog containing the character set used by the attribute

CHARACTER_SET_
SCHEMA

NCHAR VARYING(128)

The name of the schema containing the character set used by the attribute

CHARACTER_SET_NAME

NCHAR VARYING(128)

The name of the character set used by the attribute

COLLATION_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the collation used by the attribute.

COLLATION_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the collation used by the attribute.

COLLATION_NAME

NCHAR VARYING(128)

The name of the collation used by the attribute.

USER_DEFINED_TYPE_CATALOG

NCHAR VARYING(128)

The name of the user-defined type catalog used by the attribute.

USER_DEFINED_TYPE_SCHEMA

NCHAR VARYING(128)

The name of the user-defined type schema used by the attribute.

USER_DEFINED_TYPE_NAME

NCHAR VARYING(128)

The name of the user-defined type name

INFORMATION_SCHEMA.CHARACTER_SETS

The CHARACTER_SETS system view describes each character set to which the current ident has USAGE privilege.

Column name

Data type

Description

CHARACTER_SET_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the character set.

CHARACTER_SET_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the character set.

CHARACTER_SET_NAME

NCHAR VARYING(128)

The name of the character set.

FORM_OF_USE

NCHAR VARYING(128)

A user-defined name that indicates the form-of-use of the character set.

NUMBER_OF_CHARACTERS

INTEGER

The number of characters in the character set.

DEFAULT_COLLATE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the default collation for the character set.

DEFAULT_COLLATE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the default collation for the character set.

DEFAULT_COLLATE_NAME

NCHAR VARYING(128)

The name of the default collation for the character set.

INFORMATION_SCHEMA.CHECK_CONSTRAINTS

The CHECK_CONSTRAINTS system view lists the check constraints that are owned by the current ident.

Column name

Data type

Description

CONSTRAINT_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the check constraint.

CONSTRAINT_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the check constraint.

CONSTRAINT_NAME

NCHAR VARYING(128)

The name of the check constraint.

CHECK_CLAUSE

NCHAR VARYING(200)

The character representation of the search condition used in the check clause.

If the character representation does not fit, the value is null. In that case the definition can be found in INFORMATION_SCHEMA.EXT_SOURCE_DEFINITION.

INFORMATION_SCHEMA.COLLATIONS

The COLLATIONS system view describes each collation to which the current ident has access.

Column name

Data type

Description

COLLATION_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the collation.

COLLATION_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the collation.

COLLATION_NAME

NCHAR VARYING(128)

Name of the collation.

CHARACTER_SET_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the character set on which the collation is defined.

CHARACTER_SET_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the character set on which the collation is defined.

CHARACTER_SET_NAME

NCHAR VARYING(128)

The name of the character set on which the collation is defined.

PAD_ATTRIBUTE

VARCHAR(20)

One of the following values:
NO PAD = the collation has the no pad attribute
PAD SPACE = the collation has the pad space attribute.

INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE

The COLUMN_DOMAIN_USAGE system view lists the table columns which depend on domains owned by the current ident.

 

 

Column name

Data type

Description

DOMAIN_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the domain.

DOMAIN_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the domain.

DOMAIN_NAME

NCHAR VARYING(128)

The name of the domain.

TABLE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table.

TABLE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table.

TABLE_NAME

NCHAR VARYING(128)

The name of the table.

COLUMN_NAME

NCHAR VARYING(128)

The name of the column.

INFORMATION_SCHEMA.COLUMN_PRIVILEGES

The COLUMN_PRIVILEGES system view lists privileges on table columns that were granted by the current ident and privileges on table columns that were granted to the current ident or to PUBLIC.

Column name

Data type

Description

GRANTOR

NCHAR VARYING(128)

The name of the ident who granted the privilege.

GRANTEE

NCHAR VARYING(128)

The name of the ident to whom the privilege was granted. Granting a privilege to PUBLIC will result in only one row (per privilege granted) in this view and the name PUBLIC will be shown.

TABLE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table.

TABLE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table.

TABLE_NAME

NCHAR VARYING(128)

The name of the table containing the column on which the column privilege has been granted.

COLUMN_NAME

NCHAR VARYING(128)

The name of the column on which the privilege has been granted.

PRIVILEGE_TYPE

VARCHAR(20)

A value describing the type of the column privilege that was granted. One of:

INSERT
REFERENCES
SELECT
UPDATE
.
Note that when multiple table column privileges are granted to the same user at the same time (e.g. when the keyword ALL is used), multiple rows appear in this view (one for each privilege granted).

IS_GRANTABLE

VARCHAR(3)

One of:
YES = the privilege is held WITH GRANT OPTION
NO
 = the privilege is not held WITH GRANT OPTION.

INFORMATION_SCHEMA.COLUMN_UDT_USAGE

Contains one row for each column using a user-defined type created by the current user.

Column name

Data type

Description

UDT_CATALOG

NCHAR VARYING(128)

Name of catalog containing user-defined type.

UDT_SCHEMA

NCHAR VARYING(128)

Name of schema containing user-defined type

UDT_NAME

NCHAR VARYING(128)

Name of user-defined type

TABLE_CATALOG

NCHAR VARYING(128)

Catalog name for table using UDT

TABLE_SCHEMA

NCHAR VARYING(128)

Schema name for table using UDT

TABLE_NAME

NCHAR VARYING(128)

Name of table using UDT

COLUMN_NAME

NCHAR VARYING(128)

Name of column using UDT

INFORMATION_SCHEMA.COLUMNS

The COLUMNS system view lists the table columns to which the current ident has access.

Column name

Data type

Description

TABLE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table or view.

TABLE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table or view.

TABLE_NAME

NCHAR VARYING(128)

The name of the table or view.

COLUMN_NAME

NCHAR VARYING(128)

The name of the column of the table or view.

ORDINAL_POSITION

INTEGER

The ordinal position of the column in the table. The first column in the table is number 1.

COLUMN_DEFAULT

NCHAR VARYING(200)

This shows the default value for the column.
If the default value is a character string, the value shown is the string enclosed in single quotes.
If the default value is a numeric literal, the value is shown in its original character representation without enclosing quotes.
If the default value is a DATE, TIME or TIMESTAMP, the value shown is the appropriate keyword (e.g. DATE) followed by the literal representation of the value enclosed in single quotes (see DATE, TIME and TIMESTAMP Literals for a description of DATE, TIME and TIMESTAMP literals).
If the default value is a pseudo-literal, the value shown is the appropriate keyword (e.g. CURRENT_DATE) without enclosing quotes.
If the default value is the null value, the value shown is the keyword NULL without enclosing quotes.
If the default value cannot be represented without truncation, then TRUNCATED is shown without enclosing quotes.
If no default value was specified then its value is the null value.
The value of COLUMN_DEFAULT is syntactically suitable for use in specifying default-value in a CREATE TABLE or ALTER TABLE statement (except when TRUNCATED is shown).

IS_NULLABLE

VARCHAR(3)

One of:
NO = the column is not nullable, according to the rules in the international standard
YES = the null value is allowed in the column.

DATA_TYPE

VARCHAR(30)

Identifies the data type of the column.
Can be one of the following:
BIGINT
BINARY
BINARY VARYING
BINARY LARGE OBJECT
BOOLEAN
CHARACTER
CHARACTER VARYING
CHARACTER LARGE OBJECT
NATIONAL CHARACTER
NATIONAL CHARACTER VARYING
NATIONAL CHAR LARGE OBJECT
DATE
DECIMAL
DOUBLE PRECISION
FLOAT
Float(p)
INTEGER
Integer(p)
INTERVAL
NUMERIC
REAL
SMALLINT
TIME
TIMESTAMP
USER-DEFINED.

LOB_MAXIMUM_LENGTH

BIGINT

For the LOB data type, this shows the maximum length in bytes. For all other data types it is the null value.

CHARACTER_MAXIMUM_
LENGTH

INTEGER

For CHARACTER, LOB and BINARY data types, this shows the maximum length in characters or bytes. For all other data types it is the null value.

CHARACTER_OCTET_
LENGTH

INTEGER

For CHARACTER, LOB and BINARY data types, this shows the maximum length in octets. For all other data types it is the null value.

For single octet character sets, this is the same as CHARACTER_MAX_LENGTH.

NUMERIC_PRECISION

INTEGER

For NUMERIC data types, this shows the total number of significant digits allowed in the column. For all other data types it is the null value.

NUMERIC_PRECISION_
RADIX

INTEGER

This shows whether the NUMERIC_PRECISION is given in a binary or decimal radix. The numeric radix is always decimal in Mimer SQL, therefore the value 10 is always shown for numeric data types. For all other data types it is the null value.

NUMERIC_SCALE

INTEGER

For NUMERIC and DECIMAL, this defines the total number of significant digits to the right of the decimal point.

For BIGINT, INTEGER and SMALLINT, this is 0.

For all other data types, it is the null value.

DATETIME_PRECISION

INTEGER

For DATE, TIME, TIMESTAMP and INTERVAL data types, this column contains the number of digits of precision for the fractional seconds component.

For other data types it is the null value.

INTERVAL_TYPE

VARCHAR(30)

For INTERVAL data types, this is a character string specifying the interval qualifier. Can be one of:
YEAR
YEAR TO MONTH
DAY
HOUR
MINUTE
SECOND
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND
.

For other data types it is the null value.

INTERVAL_PRECISION

INTEGER

For INTERVAL data types, this is the number of significant digits for the interval leading precision (see Interval Qualifiers).

For other data types it is the null value.

CHARACTER_SET_
CATALOG

NCHAR VARYING(128)

The name of the catalog containing the character set used by the column.

CHARACTER_SET_
SCHEMA

NCHAR VARYING(128)

The name of the schema containing the character set used by the column.

CHARACTER_SET_NAME

NCHAR VARYING(128)

The name of the character set used by the column.

COLLATION_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the collation used by the column.

COLLATION_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the collation used by the column.

COLLATION_NAME

NCHAR VARYING(128)

The name of the collation used by the column.

DOMAIN_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the domain used by the column.

DOMAIN_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the domain used by the column.

DOMAIN_NAME

NCHAR VARYING(128)

The name of the domain used by the column.

USER_DEFINED_TYPE_
CATALOG

NCHAR VARYING(128)

The name of the user-defined type catalog.

USER_DEFINED_TYPE_
SCHEMA

NCHAR VARYING(128)

The name of the user-defined type schema.

USER_DEFINED_TYPE_
NAME

NCHAR VARYING(128)

The name of the user-defined type name.

COLUMN_CARD

BIGINT

Number of unique values in column as set by last UPDATE STATISTICS command for table.

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

The CONSTRAINT_COLUMN_USAGE system view lists the table columns on which constraints (referential constraints, unique constraints, check constraints and assertions) that are owned by the current ident are defined.

Column name

Data type

Description

TABLE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table.

TABLE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table.

TABLE_NAME

NCHAR VARYING(128)

The name of the table.

COLUMN_NAME

NCHAR VARYING(128)

The name of the table column.

CONSTRAINT_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the constraint.

CONSTRAINT_NAME

NCHAR VARYING(128)

The name of the constraint.

INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

The CONSTRAINT_TABLE_USAGE system view lists the tables on which constraints (referential constraints, unique constraints, check constraints and assertions) that are owned by the current ident are defined.

Column name

Data type

Description

TABLE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table.

TABLE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table.

TABLE_NAME

NCHAR VARYING(128)

The name of the table.

CONSTRAINT_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the constraint.

CONSTRAINT_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the constraint.

CONSTRAINT_NAME

NCHAR VARYING(128)

The name of the constraint.

INFORMATION_SCHEMA.DIRECT_SUPERTABLES

The DIRECT_SUPERTABLES system view lists each table created under another table.

Column name

Data type

Description

TABLE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table.

TABLE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table.

TABLE_NAME

NCHAR VARYING(128)

The name of the table.

SUPERTABLE_NAME

NCHAR VARYING(128)

The name of the supertable.

INFORMATION_SCHEMA.DIRECT_SUPERTYPES

The DIRECT_SUPERTYPES system view lists each user-defined type created under another user-defined type.

Column name

Data type

Description

UDT_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the user-defined type.

UDT_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the user-defined type.

UDT_NAME

NCHAR VARYING(128)

The name of the user-defined type.

SUPERTYPE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the supertype.

SUPERTYPE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the supertype.

SUPERTYPE_NAME

NCHAR VARYING(128)

The name of the supertype.

INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS

The DOMAIN_CONSTRAINTS system view lists the domain constraints of domains to which the current ident has access.

Column name

Data type

Description

CONSTRAINT_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the constraint.

CONSTRAINT_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the constraint.

CONSTRAINT_NAME

NCHAR VARYING(128)

Name of the constraint.

DOMAIN_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the domain on which the constraint is defined.

DOMAIN_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the domain on which the constraint is defined.

DOMAIN_NAME

NCHAR VARYING(128)

The name of the domain on which the constraint is defined.

IS_DEFERRABLE

VARCHAR(3)

One of:
YES = the constraint is deferrable
NO = the constraint is not deferrable.

INITIALLY_DEFERRED

VARCHAR(3)

One of:
YES = the constraint is immediate
NO = the constraint is deferred.

INFORMATION_SCHEMA.DOMAINS

The DOMAINS system view describes each domain to which the current ident has USAGE privilege.

Column name

Data type

Description

DOMAIN_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the domain.

DOMAIN_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the domain.

DOMAIN_NAME

NCHAR VARYING(128)

Name of the domain.

DATA_TYPE

VARCHAR(30)

Identifies the data type of the domain.
Can be one of the following:
BIGINT
BINARY
BINARY VARYING
BINARY LARGE OBJECT
BOOLEAN
CHARACTER
CHARACTER VARYING
CHARACTER LARGE OBJECT
NATIONAL CHARACTER
NATIONAL CHARACTER
   VARYING
NATIONAL CHAR LARGE
   OBJECT
DATE
DECIMAL
DOUBLE PRECISION
FLOAT
Float(p)
INTEGER
Integer(p)
INTERVAL
NUMERIC
REAL
SMALLINT
TIME
TIMESTAMP
.

LOB_MAXIMUM_LENGTH

BIGINT

For the LOB data type, this shows the maximum length in bytes. For all other data types it is the null value.

CHARACTER_MAXIMUM_
LENGTH

INTEGER

For CHARACTER, LOB and BINARY data types, this shows the maximum length in characters or bytes. For all other data types it is the null value.

CHARACTER_OCTET_LENGTH

INTEGER

For CHARACTER, LOB and BINARY data types, this shows the maximum length in octets. For all other data types it is the null value.
For single octet character sets, this is the same as CHARACTER_MAX_LENGTH.

CHARACTER_SET_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the character set used by the domain. Null if not CHARACTER data type.

CHARACTER_SET_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the character set used by the domain. Null if not CHARACTER data type.

CHARACTER_SET_NAME

NCHAR VARYING(128)

The name of the character set used by the domain. Null if not CHARACTER data type.

COLLATION_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the default collation for the character set. Null if not CHARACTER data type.

COLLATION_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the default collation for the character set. Null if not CHARACTER data type.

COLLATION_NAME

NCHAR VARYING(128)

The name of the default collation for the character set. Null if not CHARACTER data type.

NUMERIC_PRECISION

INTEGER

For NUMERIC data types, this shows the total number of significant digits allowed in the column. For all other data types it is the null value.

NUMERIC_PRECISION
_RADIX

INTEGER

This shows whether the NUMERIC_PRECISION is given in a binary or decimal radix. The numeric radix is always decimal in Mimer SQL, therefore the value 10 is always shown for numeric data types. For all other data types it is the null value.

NUMERIC_SCALE

INTEGER

For NUMERIC and DECIMAL, this defines the total number of significant digits to the right of the decimal point. For BIGINT, INTEGER and SMALLINT, this is 0. For all other data types, it is the null value.

DATETIME_PRECISION

INTEGER

For DATE, TIME, TIMESTAMP and INTERVAL data types, this column contains the number of digits of precision for the fractional seconds component. For other data types it is the null value.

INTERVAL_TYPE

VARCHAR(30)

For INTERVAL data types, this is a character string specifying the interval qualifier for the named interval data type (see Interval Qualifiers). For other data types it is the null value.

INTERVAL_PRECISION

INTEGER

For INTERVAL data types, this is the number of significant digits for the interval leading precision (see Interval Qualifiers). For other data types it is the null value.

DOMAIN_DEFAULT

NCHAR VARYING(200)

This shows the default value for the domain.

For more information, see INFORMATION_SCHEMA.COLUMNS

INFORMATION_SCHEMA.EXT_ACCESS_PATHS

The EXT_ACCESS_PATHS view shows all explicit and implicit indexes on tables that are accessible by the current ident. All columns in the indexes are displayed including the primary key columns that are automatically appended to an index.

Column name

Data type

Description

INDEX_CATALOG

NCHAR VARYING(128)

Catalog name for the index.

INDEX_SCHEMA

NCHAR VARYING(128)

Schema name for the index.

INDEX_NAME

NCHAR VARYING(128)

Index name for the secondary index. For implicit indexes this is the name of the constraint that is the reason for the index. This can be constraints such as primary key, unique, foreign key etc.

TABLE_NAME

NCHAR VARYING(128

Name of the table on which the index is defined. The table always has the same catalog and and schema as the index itself.

INDEX_TYPE

VARCHAR(20)

One of:
FOREIGN KEY
INDEX
INTERNAL KEY
PRIMARY KEY
UNIQUE
UNIQUE INDEX.

COLUMN_NAME

NCHAR VARYING(128)

Name of column present in index.

ORDINAL_POSITION

INTEGER

Ordinal position for the column within the index.

COLUMN_SOURCE

VARCHAR(20)

Used to distinguish which columns are the index columns and which columns comes from the primary key of the base table.

COLLATION_CATALOG

NCHAR VARYING(128)

Catalog name for the collation used by the index column.

The value is null if the column is not of CHARACTER type.

COLLATION_SCHEMA

NCHAR VARYING(128)

Schema name for the collation used by the index column.

The value is null if the column is not of CHARACTER type.

COLLATION_NAME

NCHAR VARYING(128)

Name of the collation used by the index column.

The value is null if the column is not of CHARACTER type.

INDEX_ALGORITHM

VARCHAR(20)

For secondary index this indicates the type of index. SIMPLE is used for ordinary indexes. Other values include WORD_SEARCH and PINYIN_START.

Implicit indexes has null in this column.

INFORMATION_SCHEMA.EXT_COLLATION_DEFINITIONS

The EXT_COLLATION_DEFINITIONS system view shows collation definitions.

Column name

Data type

Description

COLLATION_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the collation.

COLLATION_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the collation.

COLLATION_NAME

NCHAR VARYING(128)

Name of the collation.

COLLATION_VERSION

VARCHAR(20)

Version of the collation.

COLLATION_DEFINITION

VARCHAR(400)

Delta string defining the collation.

COLLATION_SEQNO

INTEGER

Sequence number.

BASE_COLLATION_CATALOG

NCHAR VARYING(128)

The catalog for the collation on which the current collation is based.

BASE_COLLATION_SCHEMA

NCHAR VARYING(128)

The schema for the collation on which the current collation is based.

BASE_COLLATION_NAME

NCHAR VARYING(128)

The name of the collation on which the current collation is based.

INFORMATION_SCHEMA.EXT_COLUMN_OFFSET_INFORMATION

The EXT_COLUMN_OFFSET_INFORMATION system view shows the physical layout of columns in tables accessible by current user.

Column name

Data type

Description

TABLE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table.

TABLE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table.

TABLE_NAME

NCHAR VARYING(128)

The name of the table.

TABLE_SYSID

INTEGER

System identifier for the table.

COLUMN_NAME

NCHAR VARYING(128)

Name of the column.

ORDINAL_POSITION

INTEGER

The ordinal position of the column in the table. The first column in the table is number 1.

IS_NULLABLE

CHARACTER VARYING(3)

One of:
NO = the column is not nullable, according to the rules in the international standard
YES = the null value is allowed in the column.

DATA_TYPE

CHARACTER VARYING(30)

Identifies the data type of the domain.
Can be one of the following:
BIGINT
BINARY
BINARY VARYING
BINARY LARGE OBJECT
BOOLEAN
CHARACTER
CHARACTER VARYING
CHARACTER LARGE OBJECT
NATIONAL CHARACTER
NATIONAL CHARACTER VARYING
NATIONAL CHAR LARGE OBJECT
DATE
DECIMAL
DOUBLE PRECISION
FLOAT
Float(p)
INTEGER
Integer(p)
INTERVAL
NUMERIC
REAL
SMALLINT
TIME
TIMESTAMP
USER-DEFINED.

LOB_MAXIMUM_LENGTH

BIGINT

For the LOB data type, this shows the maximum length in bytes. For all other data types it is the null value.

CHARACTER_MAXIMUM_LENGTH

INTEGER

For CHARACTER, BINARY, CHARACTER LARGE OBJECT and BINARY LARGE OBJECT data types, this shows the maximum length in characters or bytes as appropriate.

For all other data types it is the null value.

CHARACTER_OCTET_LENGTH

INTEGER

For a CHARACTER, BINARY, CHARACTER LARGE OBJECT and BINARY LARGE OBJECT data types, this shows the maximum length in octets.

For all other data types it is the null value. (For single octet character sets, this is the same as CHARACTER_MAXIMUM_
LENGTH
.)

NUMERIC_PRECISION

INTEGER

For NUMERIC data types, this shows the total number of decimal digits allowed in the column.

For all other data types it is the null value.

NUMERIC_SCALE

INTEGER

This defines the total number of significant digits to the right of the decimal point.

For INTEGER and SMALLINT, this is 0.

For CHARACTER, VARCHAR, DATETIME, FLOAT, INTERVAL, REAL and DOUBLE PRECISION data types, it is the null value.

DATETIME_PRECISION

INTEGER

For DATE, TIME, TIMESTAMP and interval data types, this column contains the number of digits of precision for the fractional seconds component.

For other data types it is the null value.

INTERVAL_TYPE

CHARACTER VARYING(30)

For INTERVAL data types, this is a character string specifying the interval qualifier for the named interval data type, see the Mimer SQL Reference Manual.

For other data types it is the null value.

INTERVAL_PRECISION

INTEGER

For INTERVAL data types, this is the number of significant digits for the interval leading precision, see the Mimer SQL Reference Manual.

For other data types it is the null value.

COLUMN_OFFSET

INTEGER

Internal offset for column in the table.

COLUMN_LENGTH

INTEGER

Internal length of column.

VARCHAR_OFFSET

INTEGER

Internal offset for field containing actual length for a varying length column.

INFORMATION_SCHEMA.EXT_COLUMN_REMARKS

The EXT_COLUMN_REMARKS system view shows remarks for columns that are accessible by the current ident.

Column name

Data type

Description

TABLE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table.

TABLE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table.

TABLE_NAME

NCHAR VARYING(128)

The name of the table.

COLUMN_NAME

NCHAR VARYING(128)

Name of the column.

REMARKS

NCHAR VARYING(254)

Remark for column.

INFORMATION_SCHEMA.EXT_DATABANKS

The EXT_DATABANKS system view shows databanks on which the current ident has table privilege.

An ident with the system privileges BACKUP or SHADOW may see all databanks in the system.

Column name

Data type

Description

DATABANK_CREATOR

NCHAR VARYING(128)

The name of the authorization ident that created the databank.

DATABANK_NAME

NCHAR VARYING(128)

The name of the databank.

DATABANK_TYPE

VARCHAR(20)

One of:
LOG
READ ONLY
TEMPORARY
TRANSACTION
WORK
.

IS_ONLINE

VARCHAR(3)

One of:
YES = the databank is online
NO = the databank is offline.

FILE_NUMBER

INTEGER

Ordinal number for databank file.

FILE_NAME

NCHAR VARYING(256)

File name for databank.

MAXSIZE

BIGINT

Maximum size for databank file, in kilo bytes

GOALSIZE

BIGINT

Ideal size for databank file, in kilo bytes

MINSIZE

BIGINT

Minimum size for databank file, in kilo bytes

IS_REMOVABLE

VARCHAR(3)

One of:
NO = databank is not removable
YES = databank is removable

BACKUP_DATE

TIMESTAMP

Last backup date for databank.

INFORMATION_SCHEMA.EXT_IDENTS

The EXT_IDENTS system view shows authorization idents either created by the current ident or those on which the current ident has execute or member privilege.

Column name

Data type

Description

IDENT_CREATOR

NCHAR VARYING(128)

The name of the authorization ident that created the ident.

IDENT_NAME

NCHAR VARYING(128)

The name of the ident.

IDENT_LOGIN

NCHAR VARYING(128)

OS_USER login name.

The value is null value if no OS_USER is defined.

HAS_PASSWORD

VARCHAR(3)

This column is only kept for backward compatibility with older versions. The value is always:
NO

IDENT_TYPE

VARCHAR(20)

One of:
GROUP
PROGRAM
USER
.

IDENT_SCHEMA

VARCHAR(3)

One of:
YES = the ident has a schema
NO = the ident is created without schema

INFORMATION_SCHEMA.EXT_INDEX_COLUMN_USAGE

The EXT_INDEX_COLUMN_USAGE system view shows on which table columns an secondary index is defined. Only indexes defined on table accessible by the current ident is shown.

Column name

Data type

Description

INDEX_CATALOG

NCHAR VARYING(128)

Catalog name for the secondary index.

INDEX_SCHEMA

NCHAR VARYING(128)

Schema name for the secondary index.

INDEX_NAME

NCHAR VARYING(128)

Name of the index.

IS_UNIQUE

VARCHAR(3)

One of:
YES = the index may not contain duplicates
NO = the index may contain duplicates.

TABLE_NAME

NCHAR VARYING(128)

Name of the table on which the index is defined.

COLUMN_NAME

NCHAR VARYING(128)

Name of column present in index.

IS_ASCENDING

VARCHAR(3)

One of:
YES = the sort order of the index is ascending
NO = the sort order of the index is descending.

ORDINAL_POSITION

INTEGER

Ordinal position for the column within the index.

COLLATION_CATALOG

NCHAR VARYING(128)

Catalog name for the collation. The value is null if the column is not of CHARACTER type.

COLLATION_SCHEMA

NCHAR VARYING(128)

Schema name for the collation. The value is null if the column is not of CHARACTER type.

COLLATION_NAME

NCHAR VARYING(128)

Name of the collation. The value is null if the column is not of CHARACTER type.

INFORMATION_SCHEMA.EXT_INDEXES

The EXT_INDEXES system view shows secondary indexes defined on tables that are accessible by the current ident.

Column name

Data type

Description

INDEX_CATALOG

NCHAR VARYING(128)

Catalog name for the secondary index.

INDEX_SCHEMA

NCHAR VARYING(128)

Schema name for the secondary index.

INDEX_NAME

NCHAR VARYING(128)

Name of the secondary index.

TABLE_NAME

NCHAR VARYING(128)

Name of the table on which the index is defined.

IS_UNIQUE

VARCHAR(3)

One of:
YES = the index may not contain duplicates
NO = the index may contain duplicates.

INFORMATION_SCHEMA.EXT_OBJECT_IDENT_USAGE

The EXT_OBJECT_IDENT system view shows objects accessible by the current ident.

Column name

Data type

Description

CREATOR_NAME

NCHAR VARYING(128)

Name of authorization ident that created the object.

OBJECT_CATALOG

NCHAR VARYING(128)

Catalog name for the object.

OBJECT_SCHEMA

NCHAR VARYING(128)

Schema name for the object.

OBJECT_NAME

NCHAR VARYING(128)

Name of the object.

SPECIFIC_NAME

NCHAR VARYING(128)

Specific name for owned object if object_type is one of
FUNCTION
METHOD
METHOD SPECIFICATION
PROCEDURE

Otherwise null.

OBJECT_TYPE

VARCHAR(20)

One of:
ASSERTION
BASE TABLE
CHARACTER SET
COLLATION
CONSTRAINT
DATABANK
DOMAIN
FUNCTION
IDENT
INDEX
METHOD
METHOD SPECIFICATION
MODULE
PROCEDURE
SCHEMA
SEQUENCE
SHADOW
STATEMENT
SYNONYM
TRIGGER
USER-DEFINED TYPE
VIEW
.

CREATION_DATE

TIMESTAMP

Time when object was created.

ALTERATION_DATE

TIMESTAMP

Time when object was last altered.

IS_IMPLICIT

VARCHAR(3)

One of
YES - The object has been created implicitly by Mimer when creating other objects
NO - The object has been created explicitly by the ident

REMARKS

NCHAR VARYING(254)

Remark for the object.

INFORMATION_SCHEMA.EXT_OBJECT_OBJECT_USED

The EXT_OBJECT_OBJECT_USED system view shows which object that are used by an object. The used and using objects must be accessible to the current ident.

Column name

Data type

Description

USED_OBJECT_TYPE

VARCHAR(20)

Object type for used object.
One of:
ASSERTION
BASE TABLE
CHARACTER SET
COLLATION
DOMAIN
FUNCTION
INDEX
METHOD
METHOD SPECIFICATION
MODULE
PROCEDURE
SEQUENCE
SHADOW
TRIGGER
USER-DEFINED TYPE
VIEW
.

USED_OBJECT_CATALOG

NCHAR VARYING(128)

Catalog name for used object.

USED_OBJECT_SCHEMA

NCHAR VARYING(128)

Schema name for used object.

USED_OBJECT_NAME

NCHAR VARYING(128)

Name of used object

USED_SPECIFIC_NAME

NCHAR VARYING(128)

Specific name for used object

USING_OBJECT_TYPE

VARCHAR(20)

Object type for using object.
One of:
ASSERTION
BASE TABLE
CHARACTER SET
COLLATION
CONSTRAINT
DOMAIN
FUNCTION
INDEX
METHOD
METHOD SPECIFICATION
MODULE
PROCEDURE
SHADOW
STATEMENT
TRIGGER
USER-DEFINED TYPE
VIEW
.

USING_OBJECT_CATALOG

NCHAR VARYING(128)

Catalog name for using object

USING_OBJECT_SCHEMA

NCHAR VARYING(128)

Schema name for using object

USING_OBJECT_NAME

NCHAR VARYING(128)

Name of using object

USING_SPECIFIC_NAME

NCHAR VARYING(128)

Specific name for using object

INFORMATION_SCHEMA.EXT_OBJECT_OBJECT_USING

The EXT_OBJECT_OBJECT_USING system view shows which object that are using another object. The used and using objects must be accessible to the current ident.

Column name

Data type

Description

USING_OBJECT_TYPE

VARCHAR(20)

Object type for using object.
One of:
ASSERTION
BASE TABLE
CHARACTER SET
COLLATION
CONSTRAINT
DOMAIN
FUNCTION
INDEX
METHOD
METHOD SPECIFICATION
MODULE
PROCEDURE
SHADOW
STATEMENT
TRIGGER
USER-DEFINED TYPE
VIEW
.

USING_OBJECT_CATALOG

NCHAR VARYING(128)

Catalog name for using object.

USING_OBJECT_SCHEMA

NCHAR VARYING(128)

Schema name for using object.

USING_OBJECT_NAME

NCHAR VARYING(128)

Name of using object

USING_SPECIFIC_NAME

NCHAR VARYING(128)

Specific name for using object

USED_OBJECT_TYPE

VARCHAR(20)

Object type for used object.
One of:
ASSERTION
BASE TABLE
CHARACTER SET
COLLATION
DOMAIN
FUNCTION
IDENT
METHOD
METHOD SPECIFICATION
MODULE
PROCEDURE
SEQUENCE
SHADOW
TRIGGER
USER-DEFINED TYPE
VIEW
.

USED_OBJECT_CATALOG

NCHAR VARYING(128)

Catalog name for used object

USED_OBJECT_SCHEMA

NCHAR VARYING(128)

Schema name for used object

USED_OBJECT_NAME

NCHAR VARYING(128)

Name of used object

USED_SPECIFIC_NAME

NCHAR VARYING(128)

Specific name for used object

INFORMATION_SCHEMA.EXT_OBJECT_PRIVILEGES

The EXT_OBJECT_PRIVILEGES system view shows which object privileges that are granted to an authorization ident. Either GRANTEE or GRANTOR should be equal to current ident.

Column name

Data type

Description

GRANTEE

NCHAR VARYING(128)

Name of authorization ident that has received the privilege.

OBJECT_CATALOG

NCHAR VARYING(128)

Catalog name for object.

OBJECT_SCHEMA

NCHAR VARYING(128)

Schema name for object.

OBJECT_NAME

NCHAR VARYING(128)

Name of object on which privilege is granted.

OBJECT_TYPE

VARCHAR(20)

One of:
CHARACTER SET
DOMAIN
DATABANK
FUNCTION
IDENT
METHOD
METHOD SPECIFICATION
PROCEDURE
SEQUENCE
STATEMENT
USER-DEFINED TYPE.

PRIVILEGE_TYPE

VARCHAR(20)

One of:
EXECUTE
MEMBER
TABLE
USAGE
USER-DEFINED TYPE
.

GRANTOR

NCHAR VARYING(128)

Name of authorization ident that granted the privilege.

IS_GRANTABLE

VARCHAR(3)

One of:
YES = the grantee may grant the privilege
NO = the grantee may not grant the privilege.

INFORMATION_SCHEMA.EXT_ONEROW

Dummy view containing one single row.

Column name

Data type

Description

M

CHAR(1)

Contains the value M

INFORMATION_SCHEMA.EXT_ROUTINE_MODULE_DEFINITION

The EXT_ROUTINE_MODULE_DEFINITION system view lists definitions for routines that are defined in a module.

Column name

Data type

Description

SPECIFIC_SCHEMA

NCHAR VARYING(128)

The name of the schema to which the routine belongs.

SPECIFIC_NAME

NCHAR VARYING(128)

The specific name of the routine

ROUTINE_LENGTH

INTEGER

The total length of the routine definition

ROUTINE_DEFINITION

NCHAR VARYING(400)

The source text for the routine

INFORMATION_SCHEMA.EXT_ROUTINE_MODULE_USAGE

The EXT_ROUTINE_MODULE_USAGE system view lists which routines that are defined in a module.

Column name

Data type

Description

ROUTINE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the routine

ROUTINE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the routine

ROUTINE_NAME

NCHAR VARYING(128)

The name of the routine

SPECIFIC_NAME

NCHAR VARYING(128)

The specific name of the routine

MODULE_NAME

NCHAR VARYING(128)

The name of the module in which the routine is defined

INFORMATION_SCHEMA.EXT_SCHEMAS

The EXT_SCHEMAS system view shows schemas containing objects that are accessible to the current ident.

Column name

Data type

Description

SCHEMA_NAME

NCHAR VARYING(128)

Schema name

INFORMATION_SCHEMA.EXT_SEQUENCES

The EXT_SEQUENCES system view shows sequences that are accessible to the current ident.

Column name

Data type

Description

SEQUENCE_CATALOG

NCHAR VARYING(128)

Catalog name for the sequence.

SEQUENCE_SCHEMA

NCHAR VARYING(128)

Schema name for the sequence.

SEQUENCE_NAME

NCHAR VARYING(128)

Name of the sequence.

IS_UNIQUE

VARCHAR(3)

One of:
YES = the sequence will yield unique values
NO = the sequence may repeat itself.

INITIAL_VALUE

BIGINT

The initial value for the sequence.

INCREMENT

BIGINT

The increment for the sequence.

MAXIMUM_VALUE

BIGINT

The maximum value for the sequence.

FLUSH_RATE

BIGINT

Number of sequence value allocations between saving sequence data to disk.

DATABANK_NAME

NCHAR VARYING(128)

Name of databank where the sequence table is located.

INFORMATION_SCHEMA.EXT_SHADOWS

The EXT_SHADOWS system view shows shadows. A user with shadow privilege may see any shadow or shadows on databanks that are created by the user.

Column name

Data type

Description

SHADOW_CREATOR

NCHAR VARYING(128)

Creator of the shadow.

SHADOW_NAME

NCHAR VARYING(128)

Name of the shadow.

DATABANK_NAME

NCHAR VARYING(128)

Name of databank.

IS_ONLINE

VARCHAR(3)

One of:
YES = the shadow is online
NO = the shadow is offline.

FILE_NUMBER

INTEGER

Ordinal number for physical file.

FILE_NAME

NCHAR VARYING(256)

Name of physical file for shadow.

INFORMATION_SCHEMA.EXT_SOURCE_DEFINITION

The EXT_SOURCE_DEFINITION system view shows a textual definition for owned objects.

Column name

Data type

Description

OBJECT_CATALOG

NCHAR VARYING(128)

Catalog name for object.

OBJECT_SCHEMA

NCHAR VARYING(128)

Schema name for object.

OBJECT_NAME

NCHAR VARYING(128)

Name of object.

SPECIFIC_NAME

NCHAR VARYING(128)

Specific name for routine if object type is one of:
CONSTRUCTOR METHOD
CONSTRUCTOR FUNCTION
FUNCTION
INSTANCE METHOD
PROCEDURE
STATIC METHOD

Otherwise the column is null.

COLUMN_NAME

NCHAR VARYING(128)

If object type is BASE TABLE the column contains the name of the column for which the default value is defined.

If object type is USER-DEFINED TYPE the column contains the name of the attribute for which the default value is defined.

Otherwise the column is null.

OBJECT_TYPE

VARCHAR(20)

One of:
CONSTRAINT
FUNCTION
METHOD
METHOD SPECIFICATION
MODULE
PROCEDURE
TRIGGER
USER-DEFINED TYPE
VIEW
.

SOURCE_DEFINITION

NCHAR VARYING(400)

Definition text for object.

SOURCE_LENGTH

INTEGER

Total length of source.

LINE_NUMBER

INTEGER

The line number within the source.
1 = this is the first 400 characters of the source.

2 = this is the second 400 characters of the source, etc.

INFORMATION_SCHEMA.EXT_STATEMENTS

The EXT_STATEMENTS view shows all precompiled statements available to the current IDENT.

Column name

Data type

Description

STATEMENT_CATALOG

NCHAR VARYING(128)

Catalog name for precompiled statement.

STATEMENT_SCHEMA

NCHAR VARYING(128)

Schema name for precompiled statement.

STATEMENT_NAME

NCHAR VARYING(128)

Name of precompiled statement.

STATEMENT_TYPE

INTEGER

Type of precompiled statement.

STATEMENT_DEFINITION

NCHAR VARYING(200)

Definition text of precompiled statement.

If the definition does not fit, the null value is shown. In that case the definition can be found in INFORMATION_SCHEMA.EXT_STATEMENT_DEFINITION.

IS_SCROLLABLE

VARCHAR(3)

One of:
YES = a scrollable version of the statement exists.

NO = no scrollable version of the statement exists.

IS_FORWARD_ONLY

VARCHAR(3)

One of:
YES = a forward only version of the statement exists.

NO = no forward only version of the statement exists.

INFORMATION_SCHEMA.EXT_STATEMENT_DEFINITION

The EXT_STATEMENT_DEFINITION view shows a textual definition of the precompiled statements available to the current IDENT.

Column name

Data type

Description

STATEMENT_SCHEMA

NCHAR VARYING(128)

Schema name for precompiled statement.

STATEMENT_NAME

NCHAR VARYING(128)

Name of precompiled statement.

STATEMENT_SEQUENCE_NO

INTEGER

The sequence number.

STATEMENT_DEFINITION

NCHAR VARYING(400)

The definition text of the precompiled statement. 400 characters for each sequence number.

INFORMATION_SCHEMA.EXT_STATISTICS

The EXT_STATISTICS system view shows when statistics for a base table was collected.

Column name

Data type

Description

TABLE_CATALOG

NCHAR VARYING(128)

Catalog name for base table.

TABLE_SCHEMA

NCHAR VARYING(128)

Schema name for base table.

TABLE_NAME

NCHAR VARYING(128)

Name of base table.

STATISTICS_GATHERED

TIMESTAMP(2)

Time when statistics for this table was collected

CARDINALITY

BIGINT

Number of rows in table when statistics was gathered.

INFORMATION_SCHEMA.EXT_SYNONYMS

The EXT_SYNONYMS system view shows synonyms on accessible tables.

Column name

Data type

Description

SYNONYM_CATALOG

NCHAR VARYING(128)

Catalog name for synonym.

SYNONYM_SCHEMA

NCHAR VARYING(128)

Schema name for synonym.

SYNONYM_NAME

NCHAR VARYING(128)

Name of synonym.

TABLE_CATALOG

NCHAR VARYING(128)

Catalog for table on which synonym is defined.

TABLE_SCHEMA

NCHAR VARYING(128)

Schema name for table.

TABLE_NAME

NCHAR VARYING(128)

Name of table.

INFORMATION_SCHEMA.EXT_SYSTEM_PRIVILEGES

The EXT_SYSTEM_PRIVILEGES system view shows granted to or by the current ident.

Column name

Data type

Description

GRANTEE

NCHAR VARYING(128)

Name of grantee.

PRIVILEGE_TYPE

VARCHAR(20)

One of:
BACKUP
DATABANK
IDENT
SCHEMA
SHADOW
STATISTICS
.

GRANTOR

NCHAR VARYING(128)

Name of grantor.

IS_GRANTABLE

VARCHAR(3)

One of:
YES = grantee has grant option
NO = grantee has not grant option.

INFORMATION_SCHEMA.EXT_TABLE_DATABANK_USAGE

The EXT_TABLE_DATABANK_USAGE system view shows in which databank a base table is located. Base tables accessible to the current ident are shown.

Column name

Data type

Description

TABLE_CATALOG

NCHAR VARYING(128)

Catalog for table.

TABLE_SCHEMA

NCHAR VARYING(128)

Schema name for table.

TABLE_NAME

NCHAR VARYING(128)

Name of table.

DATABANK_CREATOR

NCHAR VARYING(128)

Name of authorization ident that created databank.

DATABANK_NAME

NCHAR VARYING(128)

Name of databank.

INFORMATION_SCHEMA.KEY_COLUMN_USAGE

The KEY_COLUMN_USAGE system view lists the table columns that are constrained as keys and on accessible tables.

Column name

Data type

Description

CONSTRAINT_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table constraint.

CONSTRAINT_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table or view.

CONSTRAINT_NAME

NCHAR VARYING(128)

The name of the table constraint.

TABLE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table or view.

TABLE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table or view.

TABLE_NAME

NCHAR VARYING(128)

The name of the table or view.

COLUMN_NAME

NCHAR VARYING(128)

The name of the column.

ORDINAL_POSITION

INTEGER

The ordinal position of the column within the key.

INFORMATION_SCHEMA.METHOD_SPECIFICATION_PARAMETERS

Contains one row for each parameter for a method specification belonging to a user-defined type on which the current user has USAGE privilege.

Column name

Data type

Description

SPECIFIC_CATALOG

NCHAR VARYING(128)

Name of catalog containing method specification.

SPECIFIC_SCHEMA

NCHAR VARYING(128)

Name of schema containing method specification.

SPECIFIC_NAME

NCHAR VARYING(128)

Name of method specification

ORDINAL_POSITION

INTEGER

Ordinal position for parameter

PARAMETER_MODE

VARCHAR(5)

One of
IN = The parameter mode is in.

IS_RESULT

VARCHAR(3)

One of
YES = The parameter is a result parameter
NO = The parameter is not a result parameter

PARAMETER_NAME

NCHAR VARYING(128)

Name of parameter

DATA_TYPE

VARCHAR(30)

The type of the parameter. One of:
BIGINT
BINARY
BINARY VARYING
BINARY LARGE OBJECT
BOOLEAN
CHARACTER
CHARACTER VARYING
CHARACTER LARGE OBJECT
DATE
DECIMAL
DOUBLE PRECISION
FLOAT
Float(p)
INTEGER
Integer(p)
INTERVAL
NATIONAL CHARACTER
NATIONAL CHARACTER VARYING
NATIONAL CHARACTER LARGE OBJECT
NUMERIC
REAL
SMALLINT
TIME
TIMESTAMP
USER-DEFINED

CHARACETR_MAXIMUM_LENGTH

INTEGER

For a character data type, this shows the maximum length in characters. For all other data types it is the null value.

CHARACTER_OCTET_
LENGTH

INTEGER

For a character data type, this shows the maximum length in octets. For all other data types it is the null value. It the same value as CHARACTER_MAXIMUM length for single octet data types.

CHARACTER_SET_
CATALOG

NCHAR VARYING(128)

The name of the catalog containing the character set used by the parameter

CHARACTER_SET_
SCHEMA

NCHAR VARYING(128)

The name of the schema containing the character set used by the parameter

CHARACTER_SET_NAME

NCHAR VARYING(128)

The name of the character set used by the parameter

COLLATION_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the collation used by the parameter.

COLLATION_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the collation used by the parameter.

COLLATION_NAME

NCHAR VARYING(128)

The name of the collation used by the parameter.

NUMERIC_PRECISION

INTEGER

For NUMERIC data types, this shows the total number of significant digits allowed in the column. For all other data types it is the null value.

NUMERIC_PRECISION_RADIX

INTEGER

This shows whether the NUMERIC_PRECISION is given in a binary or decimal radix. The numeric radix is always decimal in Mimer SQL, therefore the value 10 is always shown for numeric data types. For all other data types it is the null value.

NUMERIC_SCALE

INTEGER

For NUMERIC and DECIMAL, this defines the total number of significant digits to the right of the decimal point. For BIGINT, INTEGER and SMALLINT, this is 0. For all other data types, it is the null value.

DATETIME_PRECISION

INTEGER

For DATE, TIME, TIMESTAMP and INTERVAL data types, this column contains the number of digits of precision for the fractional seconds component. For other data types it is the null value.

INTERVAL_TYPE

VARCHAR(30)

For INTERVAL data types, this is a character string specifying the interval qualifier. Can be one of:
YEAR
YEAR TO MONTH
DAY
HOUR
MINUTE
SECOND
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND
.

For other data types it is the null value.

INTERVAL_PRECISION

INTEGER

For INTERVAL data types, this is the number of significant digits for the interval leading precision (see Interval Qualifiers). For other data types it is the null value.

USER_DEFINED_TYPE_CATALOG

NCHAR VARYING(128)

The name of the user-defined type catalog used by the parameter.

USER_DEFINED_TYPE_SCHEMA

NCHAR VARYING(128)

The name of the user-defined type schema used by the parameter.

USER_DEFINED_TYPE_NAME

NCHAR VARYING(128)

The name of the user-defined type name used by the parameter

PARAMETER_DOMAIN_
CATALOG

NCHAR VARYING(128)

The catalog name for a domain when domain is used as the type for a parameter in a method.

PARAMETER_DOMAIN_
SCHEMA

NCHAR VARYING(128)

The schema name for a domain when domain is used as the type for a parameter in a method.

PARAMETER_DOMAIN_
NAME

NCHAR VARYING(128)

The name for a domain when domain is used as the type for a parameter in a method.

INFORMATION_SCHEMA.METHOD_SPECIFICATIONS

Contains one row for each method specification belonging to a user-defined type on which the current user has USAGE privilege.

Column name

Data type

Description

SPECIFIC_CATALOG

NCHAR VARYING(128)

Catalog name for method specification

SPECIFIC_SCHEMA

NCHAR VARYING(128)

Schema name for method specification

SPECIFIC_NAME

NCHAR VARYING(128)

Specific name for method specification

UDT_CATALOG

NCHAR VARYING(128)

Catalog for user-defined type containing method specification

UDT_SCHEMA

NCHAR VARYING(128)

Schema for user-defined type containing method specification

UDT_NAME

NCHAR VARYING(128)

Name of user-defined type containing method specification

METHOD_NAME

NCHAR VARYING(128)

Method name

IS_STATIC

VARCHAR(3)

One of
YES = Method specification is static
NO = Method specification is not static

IS_OVERRIDING

VARCHAR(3)

One of
YES = Method specification is overriding
NO = Method specification is not overriding

IS_CONSTRUCTOR

VARCHAR(3)

One of
YES = Method specification is a constructor method
NO = Method specification is not a constructor method

DATA_TYPE

VARCHAR(30)

The return data type of the method specification. One of:
BIGINT
BINARY
BINARY VARYING
BINARY LARGE OBJECT
BOOLEAN
CHARACTER
CHARACTER VARYING
CHARACTER LARGE OBJECT
DATE
DECIMAL
DOUBLE PRECISION
FLOAT
Float(p)
INTEGER
Integer(p)
INTERVAL
NATIONAL CHARACTER
NATIONAL CHARACTER VARYING
NATIONAL CHARACTER LARGE OBJECT
NUMERIC
REAL
SMALLINT
TIME
TIMESTAMP
USER-DEFINED

LOB_MAXIMUM_
LENGTH

INTEGER

For a lob data type, this shows the maximum length. For all other data types it is the null value.

CHARACTER_MAXIMUM_
LENGTH

INTEGER

For a character data type, this shows the maximum length in characters. For all other data types it is the null value.

CHARACTER_OCTET_
LENGTH

INTEGER

For a character data type, this shows the maximum length in octets. For all other data types it is the null value. It the same value as CHARACTER_MAXIMUM length for single octet data types.

CHARACTER_SET_
CATALOG

NCHAR VARYING(128)

The name of the catalog containing the character set used by the parameter

CHARACTER_SET_
SCHEMA

NCHAR VARYING(128)

The name of the schema containing the character set used by the result type for the method specification

CHARACTER_SET_NAME

NCHAR VARYING(128)

The name of the character set used by the result type for the method specification

COLLATION_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the collation used by the result type for the method specification.

COLLATION_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the collation used by the result type for the method specification.

COLLATION_NAME

NCHAR VARYING(128)

The name of the collation used by the result type for the method specification.

NUMERIC_PRECISION

INTEGER

For NUMERIC data types, this shows the total number of significant digits allowed in the column. For all other data types it is the null value.

NUMERIC_PRECISION_RADIX

INTEGER

This shows whether the NUMERIC_PRECISION is given in a binary or decimal radix. The numeric radix is always decimal in Mimer SQL, therefore the value 10 is always shown for numeric data types. For all other data types it is the null value.

NUMERIC_SCALE

INTEGER

For NUMERIC and DECIMAL, this defines the total number of significant digits to the right of the decimal point. For BIGINT, INTEGER and SMALLINT, this is 0. For all other data types, it is the null value.

DATETIME_PRECISION

INTEGER

For DATE, TIME, TIMESTAMP and INTERVAL data types, this column contains the number of digits of precision for the fractional seconds component. For other data types it is the null value.

INTERVAL_TYPE

VARCHAR(30)

For INTERVAL data types, this is a character string specifying the interval qualifier. Can be one of:
YEAR
YEAR TO MONTH
DAY
HOUR
MINUTE
SECOND
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND
.

For other data types it is the null value.

INTERVAL_PRECISION

INTEGER

For INTERVAL data types, this is the number of significant digits for the interval leading precision (see Interval Qualifiers). For other data types it is the null value.

RETURN_UDT_CATALOG

NCHAR VARYING(128)

The name of the user-defined type catalog used by the result type for the method specification.

RETURN_UDT_SCHEMA

NCHAR VARYING(128)

The name of the user-defined type schema used by the result type for the method specification.

RETURN_UDT_NAME

NCHAR VARYING(128)

The name of the user-defined type name used by the result type for the method specification

METHOD_LANGUAGE

VARCHAR(20)

One of:
SQL

IS_DETERMINISTIC

VARCHAR(3)

One of:
YES = the function was declared as DETERMINISTIC when it was created
NO = the function was not declared as DETERMINISTIC when it was created.

SQL_DATA_ACCESS

VARCHAR(20)

One of:
CONTAINS SQL
READS SQL
MODIFIES SQL
.

IS_NULL_CALL

VARCHAR(3)

One of:
YES = The method will be invoked even if any parameter is null
NO  = The method will return NULL if any parameter is null

CREATED

TIMESTAMP(2)

The date and time at which the method specification was created

LAST_ALTERED

TIMESTAMP(2)

The date and time at which the method specification was last altered.

If the method has not been altered, this value will be null.

AS_LOCATOR

VARCHAR(3)

One of:
YES = The returned data type for the method is declared as a locator
NO  = The returned data type for the method is not declared as a locator

RETURN_DOMAIN_
CATALOG

NCHAR VARYING(128)

The catalog name for a domain when domain is used as return type for a method.

RETURN_DOMAIN_
SCHEMA

NCHAR VARYING(128)

The schema name for a domain when domain is used as return type for a method.

RETURN_DOMAIN_NAME

NCHAR VARYING(128)

The name for a domain when domain is used as return type for a method.

INFORMATION_SCHEMA.MODULES

The MODULES system views shows modules created by the current ident.

Column name

Data type

Description

MODULE_CATALOG

NCHAR VARYING(128)

Catalog name for module.

MODULE_SCHEMA

NCHAR VARYING(128)

Schema name for module.

MODULE_NAME

NCHAR VARYING(128)

Name of module.

DEFAULT_CHARACTER_SET
_CATALOG

NCHAR VARYING(128)

Catalog name for default character set.

DEFAULT_CHARACTER_SET
_SCHEMA

NCHAR VARYING(128)

Schema name for default character set.

DEFAULT_CHARACTER_SET
_NAME

NCHAR VARYING(128)

The name for default character set.

DEFAULT_SCHEMA_CATALOG

NCHAR VARYING(128)

Catalog name for default schema of the module.

DEFAULT_SCHEMA_NAME

NCHAR VARYING(128)

Name of default schema of the module.

MODULE_DEFINITION

NCHAR VARYING(400)

Module definition; if text larger than 400, the null value is stored. The complete text can always be found in the EXT_SOURCE_
DEFINITION view.

SQL_PATH

NCHAR VARYING(200)

The default path for the module

INFORMATION_SCHEMA.PARAMETERS

The PARAMETERS system view lists the parameters of routines on which the current ident has EXECUTE privilege.

Column name

Data type

Description

SPECIFIC_CATALOG

NCHAR VARYING(128)

The catalog name for the specific name of the routine.

SPECIFIC_SCHEMA

NCHAR VARYING(128)

The schema name for the specific name of the routine.

SPECIFIC_NAME

NCHAR VARYING(128)

The specific name of the routine.

ORDINAL_POSITION

INTEGER

The ordinal position of the parameter in the routine. The first parameter in the routine is number 1.

PARAMETER_MODE

VARCHAR(5)

Indicates whether the parameter is IN, OUT or INOUT.

PARAMETER_NAME

NCHAR VARYING(128)

The name of the parameter.

DATA_TYPE

VARCHAR(30)

The data type of the parameter. One of:
BIGINT
BINARY
BINARY VARYING
BINARY LARGE OBJECT
BOOLEAN
CHARACTER
CHARACTER VARYING
CHARACTER LARGE OBJECT
DATE
DECIMAL
DOUBLE PRECISION
FLOAT
Float(p)
INTEGER
Integer(p)
INTERVAL
NATIONAL CHARACTER
NATIONAL CHARACTER VARYING
NATIONAL CHARACTER LARGE OBJECT
NUMERIC
REAL
SMALLINT
TIME
TIMESTAMP
USER-DEFINED

LOB_MAXIMUM_LENGTH

BIGINT

For the LOB data type, this shows the maximum length in bytes. For all other data types it is the null value.

CHARACTER_MAXIMUM
_LENGTH

INTEGER

For CHARACTER, LOB and BINARY data types, this shows the maximum length in characters or bytes. For all other data types it is the null value.

CHARACTER_OCTET
_LENGTH

INTEGER

For CHARACTER, LOB and BINARY data types, this shows the maximum length in octets. For all other data types it is the null value. (For single octet character sets, this is the same as CHARACTER_MAX_LENGTH).

CHARACTER_SET
_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the character set.

CHARACTER_SET
_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the character set.

CHARACTER_SET
_NAME

NCHAR VARYING(128)

Name of the character set.

COLLATION_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the collation.

COLLATION_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the collation.

COLLATION_NAME

NCHAR VARYING(128)

Name of the collation.

USER_DEFINED_TYPE
_CATALOG

NCHAR VARYING(128)

The name of the user-defined type catalog.

USER_DEFINED_TYPE
_SCHEMA

NCHAR VARYING(128)

The name of the user-defined type schema.

USER_DEFINED_TYPE
_NAME

NCHAR VARYING(128)

The name of the user-defined type name.

NUMERIC_PRECISION

INTEGER

For numeric data types, this shows the total number of significant digits allowed in the column.

For all other data types it is the null value.

NUMERIC_PRECISION
_RADIX

INTEGER

This shows whether the NUMERIC_PRECISION is given in a binary or decimal radix. The numeric radix is always decimal in Mimer SQL, therefore the value 10 is always shown for numeric data types. For all other data types it is the null value.

NUMERIC_SCALE

INTEGER

For NUMERIC and DECIMAL, this defines the total number of significant digits to the right of the decimal point. For BIGINT, INTEGER and SMALLINT, this is 0. For all other data types, it is the null value.

DATETIME_PRECISION

INTEGER

For DATE, TIME, TIMESTAMP and interval data types, this column contains the number of digits of precision for the fractional seconds component. For other data types it is the null value.

INTERVAL_TYPE

VARCHAR(30)

For INTERVAL data types, this is a character string specifying the interval qualifier for the named interval data type (see Interval Qualifiers).

INTERVAL_PRECISION

INTEGER

For INTERVAL data types, this is the number of significant digits for the interval leading precision (Interval Qualifiers).

PARAMETER_DEFAULT

NCHAR VARYING(200)

Default value for parameter.

DOMAIN_CATALOG

NCHAR VARYING(128)

The catalog name for a domain when domain is used as the type for a parameter in a routine.

DOMAIN_SCHEMA

NCHAR VARYING(128)

The schema name for a domain when domain is used as the type for a parameter in a routine.

DOMAIN_NAME

NCHAR VARYING(128)

The name for a domain when domain is used as the type for a parameter in a routine.

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

The REFERENTIAL_CONSTRAINTS system view lists the referential constraints that are accessible by the current ident.

Column name

Data type

Description

CONSTRAINT_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the referential constraint.

CONSTRAINT_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the referential constraint.

CONSTRAINT_NAME

NCHAR VARYING(128)

The name of the referential constraint.

UNIQUE_CONSTRAINT
_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the unique constraint being referenced.

UNIQUE_CONSTRAINT
_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the unique constraint being referenced

UNIQUE_CONSTRAINT
_NAME

NCHAR VARYING(128)

The name of the unique constraint being referenced.

MATCH_OPTION

VARCHAR(20)

One of:
NONE
PARTIAL
FULL
.

UPDATE_RULE

VARCHAR(20)

One of:
CASCADE
SET NULL
SET DEFAULT
NO ACTION
.

DELETE_RULE

VARCHAR(20)

One of:
CASCADE
SET NULL
SET DEFAULT
NO ACTION
.

INFORMATION_SCHEMA.ROUTINE_COLUMN_USAGE

The ROUTINE_COLUMN_USAGE system view lists the table columns that are owned by the current ident which are referenced from within a routine.

Column name

Data type

Description

SPECIFIC_CATALOG

NCHAR VARYING(128)

The catalog name for the specific name of the routine.

SPECIFIC_SCHEMA

NCHAR VARYING(128)

The schema name for the specific name of the routine.

SPECIFIC_NAME

NCHAR VARYING(128)

The specific name of the routine.

ROUTINE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the routine.

ROUTINE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the routine.

ROUTINE_NAME

NCHAR VARYING(128)

The name of the routine.

TABLE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table.

TABLE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table.

TABLE_NAME

NCHAR VARYING(128)

The name of the table.

COLUMN_NAME

NCHAR VARYING(128)

The name of the column.

INFORMATION_SCHEMA.ROUTINE_PRIVILEGES

The ROUTINE_PRIVILEGES system view lists privileges that were granted by the current ident, and privileges that were granted to the current ident or to PUBLIC, on a routine.

Column name

Data type

Description

GRANTOR

NCHAR VARYING(128)

The name of the ident who granted the privilege.

GRANTEE

NCHAR VARYING(128)

The name of the ident to whom the privilege was granted. Granting a privilege to PUBLIC will result in only one row (per privilege granted) in this view and the name PUBLIC will be shown.

SPECIFIC_CATALOG

NCHAR VARYING(128)

The catalog name for the specific name of the routine.

SPECIFIC_SCHEMA

NCHAR VARYING(128)

The schema name for the specific name of the routine.

SPECIFIC_NAME

NCHAR VARYING(128)

The specific name of the routine.

ROUTINE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the routine.

ROUTINE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the routine.

ROUTINE_NAME

NCHAR VARYING(128)

The name of the routine.

PRIVILEGE_TYPE

VARCHAR(20)

The type of the privilege.

IS_GRANTABLE

VARCHAR(3)

One of:
YES = the privilege is held with the WITH GRANT OPTION
NO
 = the privilege is held without the WITH GRANT OPTION.

INFORMATION_SCHEMA.ROUTINE_TABLE_USAGE

The ROUTINE_TABLE_USAGE system view lists the tables that are owned by the current ident on which SQL-invoked routines depend.

Column name

Data type

Description

SPECIFIC_CATALOG

NCHAR VARYING(128)

The catalog name for the specific name of the routine.

SPECIFIC_SCHEMA

NCHAR VARYING(128)

The schema name for the specific name of the routine.

SPECIFIC_NAME

NCHAR VARYING(128)

The specific name of the routine.

ROUTINE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the routine.

ROUTINE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the routine.

ROUTINE_NAME

NCHAR VARYING(128)

The name of the routine.

TABLE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table.

TABLE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table.

TABLE_NAME

NCHAR VARYING(128)

The name of the table.

INFORMATION_SCHEMA.ROUTINES

The ROUTINES system view lists the routines on which the current ident has EXECUTE privilege. If the routine is a function, the result data type for the function is returned.

Column name

Data type

Description

SPECIFIC_CATALOG

NCHAR VARYING(128)

The catalog name for the specific name of the routine.

SPECIFIC_SCHEMA

NCHAR VARYING(128)

The schema name for the specific name of the routine.

SPECIFIC_NAME

NCHAR VARYING(128)

The specific name of the routine.

ROUTINE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the routine.

ROUTINE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the routine.

ROUTINE_NAME

NCHAR VARYING(128)

The name of the routine.

MODULE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the module to which the routine belongs.

MODULE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the module to which the routine belongs.

MODULE_NAME

NCHAR VARYING(128)

The name of the module to which the routine belongs.

ROUTINE_TYPE

VARCHAR(20)

One of:
CONSTRUCTOR METHOD
FUNCTION
INSTANCE METHOD
PROCEDURE
STATIC METHOD

DATA_TYPE

VARCHAR(30)

The data type returned by the function or method. One of:
BIGINT
BINARY
BINARY VARYING
BINARY LARGE OBJECT
BOOLEAN
CHARACTER
CHARACTER VARYING
CHARACTER LARGE OBJECT
DATE
DECIMAL
DOUBLE PRECISION
FLOAT
Float(p)
INTEGER
Integer(p)
INTERVAL
NATIONAL CHARACTER
NATIONAL CHARACTER VARYING
NATIONAL CHARACTER LARGE OBJECT
NUMERIC
REAL
SMALLINT
TIME
TIMESTAMP
USER-DEFINED

LOB_MAXIMUM_LENGTH

BIGINT

For LOB data types, this shows the maximum length in bytes.

CHARACTER_MAXIMUM
_LENGTH

INTEGER

For CHARACTER, LOB and BINARY data types, this shows the maximum length in characters or bytes. For all other data types it is the null value.

CHARACTER_OCTET
_LENGTH

INTEGER

For CHARACTER, LOB and BINARY data types, this shows the maximum length in octets. For all other data types it is the null value. (For single octet character sets, this is the same as CHARACTER_MAX_LENGTH).

CHARACTER_SET
_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the character set.

CHARACTER_SET_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the character set.

CHARACTER_SET_NAME

NCHAR VARYING(128)

Name of the character set.

COLLATION_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the collation.

COLLATION_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the collation.

COLLATION_NAME

NCHAR VARYING(128)

Name of the collation.

USER_DEFINED_TYPE
_CATALOG

NCHAR VARYING(128)

The name of the user-defined type catalog.

USER_DEFINED_TYPE
_SCHEMA

NCHAR VARYING(128)

The name of the user-defined type schema.

USER_DEFINED_TYPE
_NAME

NCHAR VARYING(128)

The name of the user-defined type name.

NUMERIC_PRECISION

INTEGER

For NUMERIC data types, this shows the total number of significant digits contained in the result.

For all other data types it is the null value.

NUMERIC_PRECISION
_RADIX

INTEGER

This shows whether the NUMERIC_PRECISION is given in a binary or decimal radix. The numeric radix is always decimal in Mimer SQL, therefore the value 10 is always shown for numeric data types.

For all other data types it is the null value.

NUMERIC_SCALE

INTEGER

For NUMERIC and DECIMAL, this defines the total number of significant digits to the right of the decimal point. For BIGINT, INTEGER and SMALLINT, this is 0. For all other data types, it is the null value.

DATETIME_PRECISION

INTEGER

For DATE, TIME, TIMESTAMP and interval data types, this result contains the number of digits of precision for the fractional seconds component. For other data types it is the null value.

INTERVAL_TYPE

VARCHAR(30)

For interval data types, this is a character string specifying the interval qualifier for the named interval data type (see Interval Qualifiers).

INTERVAL_PRECISION

INTEGER

For interval data types, this is the number of significant digits for the interval leading precision, see Interval Qualifiers.

EXTERNAL_LANGUAGE

VARCHAR(20)

The language for the routine if it is an external routine, otherwise the null value is shown

IS_DETERMINISTIC

VARCHAR(3)

One of:
YES = the function was declared as DETERMINISTIC when it was created
NO = the function was not declared as DETERMINISTIC when it was created.

SQL_DATA_ACCESS

VARCHAR(20)

One of:
CONTAINS SQL
READS SQL
MODIFIES SQL
.

IS_NULL_CALL

VARCHAR(3)

If the routine is a function or method then one of:
YES = The function will be invoked even if any parameter is null
NO = The function will return NULL if any parameter is null

ROUTINE_BODY

VARCHAR(20)

One of:
SQL = the routine is an SQL routine
EXTERNAL = the routine is an external routine.

ROUTINE_DEFINITION

NCHAR VARYING(200)

The text of the routine definition.

If the actual definition would not fit into the maximum length of this column, the null value will be shown and the definition text will appear in the view INFORMATION_SCHEMA.EXT_SOURCE_DEFINITION.

EXTERNAL_NAME

NCHAR VARYING(128)

The external name of the routine if it is an external routine, otherwise the null value is shown.

PARAMETER_STYLE

VARCHAR(20)

The parameter passing style of the routine if it is an external routine, otherwise the null value is shown.

SQL_PATH

NCHAR VARYING(200)

The path for the routine.

The null value is shown if no path is defined.

SCHEMA_LEVEL_ROUTINE

VARCHAR(3)

One of:
YES = the routine was created on its own
NO = the routine was created in a module.

IS_RESULT

VARCHAR(3)

One of:
YES = the routine returns a result set
NO = the routine does not return a result set.

EXTERNAL_LIBRARY

NCHAR VARYING(128)

 

DOMAIN_CATALOG

NCHAR VARYING(128)

The catalog name for a domain when domain is used as return type for a function or a result set procedure.

DOMAIN_SCHEMA

NCHAR VARYING(128)

The schema name for a domain when domain is used as return type for a function or a result set procedure.

DOMAIN_NAME

NCHAR VARYING(128)

The name for a domain when domain is used as return type for a function or a result set procedure.

INFORMATION_SCHEMA.SCHEMATA

The SCHEMATA system view lists the schemas that are owned by the current ident.

Column name

Data type

Description

CATALOG_NAME

NCHAR VARYING(128)

The name of the catalog containing the schema.

SCHEMA_NAME

NCHAR VARYING(128)

The name of the schema.

SCHEMA_OWNER

NCHAR VARYING(128)

The name of the ident who created the schema.

DEFAULT_CHARACTER_SET_CATALOG

NCHAR VARYING(128)

The name of the catalog that contains the default character set for the schema.

DEFAULT_CHARACTER_SET_SCHEMA

NCHAR VARYING(128)

The name of the schema that contains the default character set for the schema.

DEFAULT_CHARACTER_SET_NAME

NCHAR VARYING(128)

The name of the default character set for the schema.

SQL_PATH

NCHAR VARYING(200)

The default path for the schema

INFORMATION_SCHEMA.SEQUENCES

The SEQUENCES system view shows sequences that are accessible to the current ident.

Column name

Data type

Description

SEQUENCE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the sequence.

SEQUENCE_SCHEMA

NCHAR VARYING(128)

Schema name for the sequence.

SEQUENCE_NAME

NCHAR VARYING(128)

Name of the sequence.

DATA_TYPE

VARCHAR(30)

Data type for sequence.

One of:
SMALLINT
INTEGER
BIGINT

NUMERIC_PRECISION

INTEGER

Number of significant digits for sequence value.

NUMERIC_PRECISION_RADIX

INTEGER

This shows whether the NUMERIC_PRECISION is given in a binary or decimal radix. The numeric radix is always decimal in Mimer SQL, therefore the value 10 is always shown.

NUMERIC_SCALE

INTEGER

Number of significant decimals. This value will always be zero.

START_VALUE

VARCHAR(20)

Initial value for sequence.

MINIMUM_VALUE

VARCHAR(20)

Minimum value for sequence.

MAXIMUM_VALUE

VARCHAR(20)

Maximum value for sequence.

INCREMENT

VARCHAR(20)

Increment value for sequence.

CYCLE_OPTION

VARCHAR(3)

Indicates whether sequence is defined with cycle option or not.

One of:
YES
NO

INFORMATION_SCHEMA.SQL_FEATURES

The SQL_FEATURES lists the features and subfeatures of the SQL-2011 standard, and indicates which of these Mimer SQL supports.

Column name

Data type

Description

FEATURE_ID

VARCHAR(20)

Identifies the feature by a letter and three digits.

FEATURE_NAME

VARCHAR(254)

Short description of the feature.

SUB_FEATURE_ID

VARCHAR(20)

Identifies a subfeature by two digits. Single space if feature described.

SUB_FEATURE_NAME

VARCHAR(254)

Short description of the subfeature. Single space if feature described.

IS_SUPPORTED

VARCHAR(3)

YES if fully supported by Mimer SQL, otherwise NO.

IS_VERIFIED_BY

VARCHAR(3)

Should identify the test used to verify the conformance (always null).

IS_CORE_SQL

VARCHAR(3)

YES if the feature belongs to Core SQL, otherwise NO.

COMMENTS

VARCHAR(254)

Comments pertinent to the feature element.

INFORMATION_SCHEMA.SQL_LANGUAGES

The SQL_LANGUAGES system view lists the conformance levels, options and dialects supported by the SQL implementation.

Column name

Data type

Description

SQL_LANGUAGE_SOURCE

VARCHAR(254)

The organization that defined the SQL version.

SQL_LANGUAGE_YEAR

VARCHAR(254)

The year the relevant source document was approved.

SQL_LANGUAGE
_CONFORMANCE

VARCHAR(254)

The conformance level to the relevant document that the implementation claims.

SQL_LANGUAGE
_INTEGRITY

VARCHAR(254)

(Meaning no longer defined).

SQL_LANGUAGE
_IMPLEMENTATION

VARCHAR(254)

A character string, defined by the vendor, that uniquely defines the vendor’s SQL product.

SQL_LANGUAGE
_BINDING_STYLE

VARCHAR(254)

Included to envisage future adoption of direct, module or other binding styles.

SQL_LANGUAGE
_PROGRAMMING
_LANGUAGE

VARCHAR(254)

The host language for which the binding style is supported.

INFORMATION_SCHEMA.SQL_SIZING

The SQL_SIZING view lists the sizing items defined in the SQL-2011 standard and, for each of these, indicate the size supported by Mimer SQL.

Column name

Data type

Description

SIZING_ID

SMALLINT

Identifies the sizing item by an integer value.

SIZING_NAME

VARCHAR(50)

Description of the sizing item.

SUPPORTED_VALUE

INTEGER

0 if no limit by Mimer SQL
null value if item not supported by Mimer SQL

COMMENTS

VARCHAR(200)

Comments pertinent to the sizing item.

INFORMATION_SCHEMA.TABLE_CONSTRAINTS

The TABLE_CONSTRAINTS system view lists the table constraints that are accessible by the current ident.

Column name

Data type

Description

CONSTRAINT_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table constraint.

CONSTRAINT_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table or view.

CONSTRAINT_NAME

NCHAR VARYING(128)

The name of the table constraint.

TABLE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table or view.

TABLE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table or view.

TABLE_NAME

NCHAR VARYING(128)

The name of the table or view.

CONSTRAINT_TYPE

VARCHAR(20)

One of:
CHECK
FOREIGN KEY
PRIMARY KEY
UNIQUE
.

IS_DEFERRABLE

VARCHAR(3)

One of:
YES = the constraint is deferrable
NO = the constraint is not deferrable.

INITIALLY_DEFERRED

VARCHAR(3)

One of:
YES = the constraint is immediate
NO = the constraint is deferred.

INFORMATION_SCHEMA.TABLE_PRIVILEGES

The TABLE_PRIVILEGES system view lists privileges that were granted by the current ident, and privileges that were granted to the current ident or to PUBLIC, on an entire table.

Column name

Data type

Description

GRANTOR

NCHAR VARYING(128)

The name of the ident who granted the privilege.

GRANTEE

NCHAR VARYING(128)

The name of the ident to whom the privilege was granted.

Granting a privilege to PUBLIC will result in only one row (per privilege granted) in this view and the name PUBLIC will be shown.

TABLE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table.

TABLE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table.

TABLE_NAME

NCHAR VARYING(128)

The name of the table in question.

PRIVILEGE_TYPE

VARCHAR(20)

A value describing the type of the privilege.
One of:
DELETE
INSERT
REFERENCES
SELECT
UPDATE
.
Rows where the privilege type is REFERENCES, UPDATE or INSERT only describe cases where the grantee was granted the privilege on the entire table.

Where the GRANT statement granted REFERENCES or UPDATE privilege to specified columns of a table, no rows appear in TABLE_PRIVILEGES but there are rows in COLUMN_PRIVILEGES.

Note that when multiple table privileges are granted to the same user at the same time (e.g. when the keyword ALL is used), multiple rows appear in this view (one for each privilege granted).

IS_GRANTABLE

VARCHAR(3)

One of:
YES = the privilege is held with the WITH GRANT OPTION
NO
 = the privilege is held without the WITH GRANT OPTION.

INFORMATION_SCHEMA.TABLES

The TABLES system view lists tables to which the current ident has access.

Column name

Data type

Description

TABLE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table or view.

TABLE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table or view.

TABLE_NAME

NCHAR VARYING(128)

The name of the table or view.

TABLE_TYPE

VARCHAR(20)

One of:
BASE TABLE = the row describes a table
VIEW = the row describes a view.

COMMIT_ACTION

VARCHAR(20)

Indicates what happens with records in a temporary table at commit.

One of:
DELETE
PRESERVE

The column will be null if the table is not temporary.

INFORMATION_SCHEMA.TRANSLATIONS

The TRANSLATIONS system view lists the character translations on which the current ident has USAGE privilege.

The source character set is the character set to which the characters that are to be translated by the translation belong.

The target character set is the character set to which the characters that are the result of the translation belong.

Column name

Data type

Description

TRANSLATION
_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the translation.

TRANSLATION
_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the translation.

TRANSLATION_NAME

NCHAR VARYING(128)

The name of the translation.

SOURCE_CHARACTER
_SET_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the source character set.

SOURCE_CHARACTER
_SET_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the source character set.

SOURCE_CHARACTER
_SET_NAME

NCHAR VARYING(128)

The name of the source character set.

TARGET_CHARACTER
_SET_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the target character set.

TARGET_CHARACTER
_SET_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the target character set.

TARGET_CHARACTER
_SET_NAME

NCHAR VARYING(128)

The name of the target character set.

INFORMATION_SCHEMA.TRIGGERED_UPDATE_COLUMNS

The TRIGGERED_UPDATE_COLUMNS system view lists the columns owned by the current ident that are referenced from the explicit column list in the trigger event of an UPDATE trigger.

Column name

Data type

Description

TRIGGER_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the trigger.

TRIGGER_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the trigger.

TRIGGER_NAME

NCHAR VARYING(128)

The name of the trigger.

EVENT_OBJECT
_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table or view on which the trigger is created.

EVENT_OBJECT
_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table or view on which the UPDATE trigger is created.

EVENT_OBJECT
_TABLE

NCHAR VARYING(128)

The name of the table or view on which the UPDATE trigger is created.

EVENT_OBJECT
_COLUMN

NCHAR VARYING(128)

The name of the table or view column referenced in the column list of the UPDATE trigger event.

INFORMATION_SCHEMA.TRIGGER_COLUMN_USAGE

The TRIGGER_COLUMN_USAGE view lists the table columns on which triggers, that owned by the current ident, depend because they are referenced in the search condition of the trigger or in one of the statements in the body of the trigger.

Column name

Data type

Description

TRIGGER_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the trigger.

TRIGGER_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the trigger.

TRIGGER_NAME

NCHAR VARYING(128)

The name of the trigger.

TABLE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the referenced table or view.

TABLE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the referenced table or view.

TABLE_NAME

NCHAR VARYING(128)

The name of the referenced table or view.

COLUMN_NAME

NCHAR VARYING(128)

The name of the referenced column.

INFORMATION_SCHEMA.TRIGGER_TABLE_USAGE

The TRIGGER_TABLE_USAGE view lists the tables on which triggers, that owned by the current ident, depend.

Column name

Data type

Description

TRIGGER_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the trigger.

TRIGGER_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the trigger.

TRIGGER_NAME

NCHAR VARYING(128)

The name of the trigger.

TABLE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the referenced table or view.

TABLE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the referenced table or view.

TABLE_NAME

NCHAR VARYING(128)

The name of the referenced table or view.

INFORMATION_SCHEMA.TRIGGERS

The TRIGGERS system view lists the triggers owned by the current ident.

Column name

Data type

Description

TRIGGER_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the trigger.

TRIGGER_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the trigger.

TRIGGER_NAME

NCHAR VARYING(128)

The name of the trigger.

EVENT_MANIPULATION

VARCHAR(20)

The data manipulation event triggering execution of the trigger (the trigger event).
One of:
INSERT
DELETE
UPDATE
.

EVENT_OBJECT_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table or view on which the trigger is created.

EVENT_OBJECT_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table or view on which the trigger is created.

EVENT_OBJECT_TABLE

NCHAR VARYING(128)

The name of the table or view on which the trigger is created.

ACTION_ORDER

INTEGER

Ordinal number for trigger execution. This number will define the execution order of triggers on the same table and with the same value for EVENT_MANIPULATION, ACTION_CONDITION, CONDITION_TIMING and ACTION_ORIENTATION. The trigger with 1 in this column will be executed first, followed by the trigger with 2, etc.

ACTION_CONDITION

NCHAR VARYING(200)

The character representation of the search condition in the WHEN clause of the trigger. If the length of the text exceeds 200 characters, the null value will be shown.

ACTION_STATEMENT

NCHAR VARYING(200)

The character representation of the body of the trigger. If the length of the text exceeds 200 characters, the null value will be shown.

ACTION_ORIENTATION

VARCHAR(20)

One of:
ROW = the trigger is a row trigger
STATEMENT = the trigger is a statement trigger.

ACTION_TIMING

VARCHAR(20)

One of:
BEFORE = the trigger is executed before the triggering data manipulation operation
INSTEAD OF = the trigger is executed instead of the triggering data manipulation operation
AFTER = the trigger is executed after the triggering data manipulation operation.

ACTION_REFERENCE
_OLD_TABLE

NCHAR VARYING(128)

The identifier specified in the OLD TABLE clause.

ACTION_REFERENCE
_NEW_TABLE

NCHAR VARYING(128)

The identifier specified in the NEW TABLE clause.

ACTION_REFERENCE
_OLD_ROW

NCHAR VARYING(128)

The identifier specified in the OLD ROW clause.

ACTION_REFERENCE
_NEW_ROW

NCHAR VARYING(128)

The identifier specified in the NEW ROW clause.

COLUMN_LIST_IS
_IMPLICIT

CHAR(3)

One of:
YES = the trigger will be executed on update of any column in the table
NO = the trigger will only be executed on update of those columns specified in the UPDATE OF clause in the trigger definition.

CREATED

TIMESTAMP(2)

The date when the trigger was created.

INFORMATION_SCHEMA.UDT_PRIVILEGES

Contains one row for each user-defined type on which the current user has granted or been granted USAGE privilege.

Column name

Data type

Description

GRANTOR

NCHAR VARYING(128)

Name of authorization ident who has granted privilege

GRANTEE

NCHAR VARYING(128)

Name of authorization ident who has been granted privilege

UDT_CATALOG

NCHAR VARYING(128)

Catalog name for user-defined type.

UDT_SCHEMA

NCHAR VARYING(128)

Schema name for user-defined type.

UDT_NAME

NCHAR VARYING(128)

Name of user-defined type

PRIVILEGE_TYPE

VARCHAR(20)

One of
USAGE TYPE

IS_GRANTABLE

VARCHAR(3)

One of
YES = The grantee has the privilege with grant option.
NO = The grantee has the privilege without grant option.

INFORMATION_SCHEMA.USAGE_PRIVILEGES

The USAGE_PRIVILEGES system view lists the USAGE privileges that were granted by the current ident, and granted to the current ident or to PUBLIC.

Column name

Data type

Description

GRANTOR

NCHAR VARYING(128)

The name of the ident who granted the privilege.

GRANTEE

NCHAR VARYING(128)

The name of the ident to whom the privilege was granted. Granting a privilege to PUBLIC will result in only one row (per privilege granted) in this view and the name PUBLIC will be shown.

OBJECT_CATALOG

NCHAR VARYING(128)

The name of the catalog that contains the object character set or collation.

OBJECT_SCHEMA

NCHAR VARYING(128)

The name of the schema that contains the object character set or collation.

OBJECT_NAME

NCHAR VARYING(128)

The name of the character set or collation.

OBJECT_TYPE

NCHAR VARYING(20)

One of:
CHARACTER SET = the privilege is held on a character set
COLLATION = the privilege is held on a collation
DOMAIN = the privilege is held on a domain
SEQUENCE = the privilege is held on a sequence.

PRIVILEGE_TYPE

VARCHAR(20)

This will always be USAGE.

IS_GRANTABLE

VARCHAR(3)

One of:
YES = the privilege is held WITH GRANT OPTION
NO
 = the privilege is not held WITH GRANT OPTION.

INFORMATION_SCHEMA.USER_DEFINED_TYPES

The USER_DEFINED_TYPES system view shows the user-defined types defined and accessible by the current ident.

Column name

Data type

Description

USER_DEFINED_TYPE_CATALOG

NCHAR VARYING(128)

Catalog name for the user-defined type.

USER_DEFINED_TYPE_SCHEMA

NCHAR VARYING(128)

Schema name for the user-defined type.

USER_DEFINED_TYPE_NAME

NCHAR VARYING(128)

Name of the user-defined type.

USER_DEFINED_TYPE_CATEGORY

VARCHAR(20)

Contains STRUCTURED or DISTINCT.

IS_FINAL

VARCHAR(3)

YES if the user-defined type cannot have subtypes, otherwise NO.

DATA_TYPE

VARCHAR(30)

Identifies the data type of the column. Can be one of the following:
BIGINT
BINARY
BINARY VARYING
BINARY LARGE OBJECT
BOOLEAN
CHARACTER
CHARACTER VARYING
CHARACTER LARGE OBJECT
NATIONAL CHARACTER
NATIONAL CHARACTER VARYING
NATIONAL CHAR LARGE OBJECT
DATE
DECIMAL
DOUBLE PRECISION
FLOAT
Float(p)
INTEGER
Integer(p)
INTERVAL
NUMERIC
REAL
SMALLINT
TIME
TIMESTAMP
USER-DEFINED.

LOB_MAXIMUM_LENGTH

BIGINT

For the LOB data type, this shows the maximum length in bytes. For all other data types it is the null value.

CHARACTER_MAXIMUM_
LENGTH

INTEGER

For CHARACTER, LOB and BINARY data types, this shows the maximum length in characters or bytes. For all other data types it is the null value.

CHARACTER_OCTET_
LENGTH

INTEGER

For CHARACTER, LOB and BINARY data types, this shows the maximum length in octets. For all other data types it is the null value.

For single octet character sets, this is the same as CHARACTER_MAX_LENGTH.

CHARACTER_SET_
CATALOG

NCHAR VARYING(128)

The name of the catalog containing the character set used by the column.

CHARACTER_SET_
SCHEMA

NCHAR VARYING(128)

The name of the schema containing the character set used by the column.

CHARACTER_SET_NAME

NCHAR VARYING(128)

The name of the character set used by the column.

COLLATION_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the collation used by the column.

COLLATION_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the collation used by the column.

COLLATION_NAME

NCHAR VARYING(128)

The name of the collation used by the column.

NUMERIC_PRECISION

INTEGER

For NUMERIC data types, this shows the total number of significant digits allowed in the column. For all other data types it is the null value.

NUMERIC_PRECISION_
RADIX

INTEGER

This shows whether the NUMERIC_PRECISION is given in a binary or decimal radix. The numeric radix is always decimal in Mimer SQL, therefore the value 10 is always shown for numeric data types. For all other data types it is the null value.

NUMERIC_SCALE

INTEGER

For NUMERIC and DECIMAL, this defines the total number of significant digits to the right of the decimal point.

For BIGINT, INTEGER and SMALLINT, this is 0.

For all other data types, it is the null value.

DATETIME_PRECISION

INTEGER

For DATE, TIME, TIMESTAMP and INTERVAL data types, this column contains the number of digits of precision for the fractional seconds component.

For other data types it is the null value.

INTERVAL_TYPE

VARCHAR(30)

For INTERVAL data types, this is a character string specifying the interval qualifier. Can be one of:
YEAR
YEAR TO MONTH
DAY
HOUR
MINUTE
SECOND
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND
.

For other data types it is the null value.

INTERVAL_PRECISION

INTEGER

For INTERVAL data types, this is the number of significant digits for the interval leading precision (see Interval Qualifiers).

For other data types it is the null value.

INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

The VIEW_COLUMN_USAGE system view lists the table columns on which views that are owned by the current ident depend.

Column name

Data type

Description

VIEW_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the view.

VIEW_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the view.

VIEW_NAME

NCHAR VARYING(128)

The name of the view.

TABLE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table.

TABLE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table.

TABLE_NAME

NCHAR VARYING(128)

The name of the table.

COLUMN_NAME

NCHAR VARYING(128)

The name of the column.

INFORMATION_SCHEMA.VIEW_TABLE_USAGE

The VIEW_TABLE_USAGE system view lists the tables on which views that are owned by the current ident depend.

Column name

Data type

Description

VIEW_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the view.

VIEW_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the view.

VIEW_NAME

NCHAR VARYING(128)

The name of the view.

TABLE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table.

TABLE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table.

TABLE_NAME

NCHAR VARYING(128)

The name of the table.

INFORMATION_SCHEMA.VIEWS

The VIEWS system view lists the views to which the current ident as access.

Column name

Data type

Description

TABLE_CATALOG

NCHAR VARYING(128)

The name of the catalog containing the table or view.

TABLE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table or view.

TABLE_NAME

NCHAR VARYING(128)

The name of the table or view.

VIEW_DEFINITION

NCHAR VARYING(200)

The definition of the view as it would appear in a CREATE VIEW statement.

If the actual definition would not fit into the maximum length of this column, the null value will be shown. In that case the definition can be found in INFORMATION_SCHEMA.EXT_SOURCE_DEFINITION.

CHECK_OPTION

VARCHAR(20)

The value CASCADED is shown if WITH CHECK OPTION was specified in the CREATE VIEW statement that created the view, and the value NONE is shown otherwise.

IS_UPDATABLE

VARCHAR(3)

One of:
YES = the view is updatable
NO = the view is not updatable.

Standard Compliance

The table below summarizes standards compliance concerning the views in INFORMATION_SCHEMA.

Standard

Compliance

Comments

SQL-2016

Core

Fully compliant.

SQL-2016

Features outside core

Feature F231, “Privilege tables” support for the views TABLE_PRIVILEGES, COLUMN_PRIVILEGES and USAGE_PRIVILEGES.

Feature T011, “TIME_STAMP domain in information_schema”.

 

Mimer SQL extension

All views starting with the name EXT_ are Mimer SQL extensions.