Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

http://developer.mimer.com


INFORMATION_SCHEMA


The table below summarizes the system views that are part of the schema INFORMATION_SCHEMA (view names appear in their unqualified form):

View name
Description
ASSERTIONS
Owned assertions. See ASSERTIONS
CHARACTER_SETS
Accessible character sets. See CHARACTER_SETS
CHECK_CONSTRAINTS
Owned check constraints. See CHECK_CONSTRAINTS.
COLLATIONS
Accessible collations. See COLLATIONS.
COLUMN_DOMAIN_USAGE
Columns defined using to owned domains. See COLUMN_DOMAIN_USAGE.
COLUMN_PRIVILEGES
Privileges granted on accessible table columns. See COLUMN_PRIVILEGES.
COLUMNS
Accessible table columns. See COLUMNS.
CONSTRAINT_COLUMN_USAGE
Columns referenced by owned referential, unique, check or assertion constraints. CONSTRAINT_COLUMN_USAGE.
CONSTRAINT_TABLE_USAGE
Tables on which owned referential, unique, check or assertion constraints are defined. See CONSTRAINT_TABLE_USAGE.
DOMAIN_CONSTRAINTS
Constraints of accessible domains. See DOMAIN_CONSTRAINTS
DOMAINS
Accessible domains. See DOMAINS.
EXT_COLUMN_REMARKS
Comments for accessible table columns. See EXT_COLUMN_REMARKS
EXT_DATABANKS
Accessible databanks. See EXT_DATABANKS.
EXT_IDENTS
Accessible authorization idents. See EXT_IDENTS.
EXT_INDEX_COLUMN_USAGE
Accessible table columns on which indexes depend. EXT_INDEX_COLUMN_USAGE.
EXT_INDEXES
Accessible indexes. See EXT_INDEXES.
EXT_OBJECT_IDENT_USAGE
Accessible objects created by authorization ident. See EXT_OBJECT_IDENT_USAGE.
EXT_OBJECT_OBJECT_USED
Accessible objects used by other objects. See EXT_OBJECT_OBJECT_USED.
EXT_OBJECT_OBJECT_USING
Accessible objects using other objects. See EXT_OBJECT_OBJECT_USING.
EXT_OBJECT_PRIVILEGES
Object privileges granted to an authorization ident. EXT_OBJECT_PRIVILEGES.
EXT_ROUTINE_MODULE_DEFINITION
Source definition for routines defined in modules. See EXT_ROUTINE_MODULE_DEFINITION.
EXT_ROUTINE_MODULE_USAGE
Accessible routines in a module. See EXT_ROUTINE_MODULE_USAGE.
EXT_SEQUENCES
Accessible sequences. See EXT_SEQUENCES.
EXT_SHADOWS
Accessible shadows. See EXT_SHADOWS.
EXT_SOURCE_DEFINITION
Text definition for owned objects. See EXT_SOURCE_DEFINITION.
EXT_STATEMENTS
Shows all precompiled statements available to the current IDENT. See EXT_STATEMENTS.
EXT_STATEMENT_DEFINITION
Shows a textual definition of the precompiled statements available to the current IDENT. See EXT_STATEMENT_DEFINITION.
EXT_STATISTICS
Statistics for table. See EXT_STATISTICS.
EXT_SYNONYMS
Accessible synonyms. See EXT_SYNONYMS.
EXT_SYSTEM_PRIVILEGES
System privileges granted to an authorization ident. See EXT_SYSTEM_PRIVILEGES.
EXT_TABLE_DATABANK_USAGE
Owned databanks on which tables depend. See EXT_TABLE_DATABANK_USAGE.
KEY_COLUMN_USAGE
Table columns constrained as keys by owned constraints. See KEY_COLUMN_USAGE.
MODULES
Owned modules. See MODULES.
PARAMETERS
Parameters of accessible routines. See MODULES.
REFERENTIAL_CONSTRAINTS
Owned referential constraints. See REFERENTIAL_CONSTRAINTS.
ROUTINE_COLUMN_USAGE
Owned table columns on which routines depend. See ROUTINE_COLUMN_USAGE.
ROUTINE_PRIVILEGES
Privileges held on accessible routines. See ROUTINE_PRIVILEGES.
ROUTINE_TABLE_USAGE
Owned tables on which routines depend. See ROUTINE_TABLE_USAGE.
ROUTINES
Accessible routines. ROUTINES.
SCHEMATA
Owned schemas. See SCHEMATA.
SQL_LANGUAGES
Conformance levels for supported SQL language options and dialects. See SQL_LANGUAGES.
TABLE_CONSTRAINTS
Owned table constraints. See TABLE_CONSTRAINTS.
TABLE_PRIVILEGES
Privileges held on accessible tables. See TABLE_PRIVILEGES.
TABLES
Accessible tables. See TABLES.
TRANSLATIONS
Accessible character set translations. See TRANSLATIONS.
TRIGGERED_UPDATE_COLUMNS
Owned columns referenced from UPDATE trigger column lists. See TRIGGERED_UPDATE_COLUMNS.
TRIGGER_COLUMN_USAGE
Owned columns referenced from a trigger action. See TRIGGER_COLUMN_USAGE.
TRIGGER_TABLE_USAGE
Tables on which owned triggers depend. See TRIGGER_TABLE_USAGE.
TRIGGERS
Owned triggers. See TRIGGERS.
USAGE_PRIVILEGES
USAGE privilege held on accessible objects. See USAGE_PRIVILEGES.
VIEW_COLUMN_USAGE
Columns on which owned views depend. See VIEW_COLUMN_USAGE.
VIEW_TABLE_USAGE
Tables on which owned views depend. VIEW_TABLE_USAGE.
VIEWS
Accessible views. SeeVIEWS.

ASSERTIONS

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

Column name
Data type
Description
CONSTRAINT_CATALOG
VARCHAR(128)
The name of the catalog containing the assertion.
CONSTRAINT_SCHEMA
VARCHAR(128)
The name of the schema containing the assertion.
CONSTRAINT_NAME
VARCHAR(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.

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
VARCHAR(128)
The name of the catalog containing the character set.
CHARACTER_SET_SCHEMA
VARCHAR(128)
The name of the schema containing the character set.
CHARACTER_SET_NAME
VARCHAR(128)
The name of the character set.
FORM_OF_USE
VARCHAR(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
VARCHAR(128)
The name of the catalog containing the default collation for the character set.
DEFAULT_COLLATE_SCHEMA
VARCHAR(128)
The name of the schema containing the default collation for the character set.
DEFAULT_COLLATE_NAME
VARCHAR(128)
The name of the default collation for the character set.

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
VARCHAR(128)
The name of the catalog containing the check constraint.
CONSTRAINT_SCHEMA
VARCHAR(128)
The name of the schema containing the check constraint.
CONSTRAINT_NAME
VARCHAR(128)
The name of the check constraint.
CHECK_CLAUSE
NCHAR VARYING(400)
The character representation of the search condition used in the check clause. If the character representation does not fit, the value is NULL.

COLLATIONS

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

Column name
Data type
Description
COLLATION_CATALOG
VARCHAR(128)
The name of the catalog containing the collation.
COLLATION_SCHEMA
VARCHAR(128)
The name of the schema containing the collation.
COLLATION_NAME
VARCHAR(128)
Name of the collation.
CHARACTER_SET_CATALOG
VARCHAR(128)
The name of the catalog containing the character set on which the collation is defined.
CHARACTER_SET_SCHEMA
VARCHAR(128)
The name of the schema containing the character set on which the collation is defined.
CHARACTER_SET_NAME
VARCHAR(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.

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
VARCHAR(128)
The name of the catalog containing the domain.
DOMAIN_SCHEMA
VARCHAR(128)
The name of the schema containing the domain.
DOMAIN_NAME
VARCHAR(128)
The name of the domain.
TABLE_CATALOG
VARCHAR(128)
The name of the catalog containing the table.
TABLE_SCHEMA
VARCHAR(128)
The name of the schema containing the table.
TABLE_NAME
VARCHAR(128)
The name of the table.
COLUMN_NAME
VARCHAR(128)
The name of the column.

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
VARCHAR(128)
The name of the ident who granted the privilege.
GRANTEE
VARCHAR(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
VARCHAR(128)
The name of the catalog containing the table.
TABLE_SCHEMA
VARCHAR(128)
The name of the schema containing the table.
TABLE_NAME
VARCHAR(128)
The name of the table containing the column on which the column privilege has been granted.
COLUMN_NAME
VARCHAR(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.

COLUMNS

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

Column name
Data type
Description
TABLE_CATALOG
VARCHAR(128)
The name of the catalog containing the table or view.
TABLE_SCHEMA
VARCHAR(128)
The name of the schema containing the table or view.
TABLE_NAME
VARCHAR(128)
The name of the table or view.
COLUMN_NAME
VARCHAR(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(400)
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
CHARACTER
CHARACTER LARGE OBJECT
NATIONAL CHARACTER
NATIONAL CHARACTER VARYING
CHARACTER VARYING
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.
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
VARCHAR(128)
The name of the catalog containing the character set used by the column.
CHARACTER_SET
_SCHEMA
VARCHAR(128)
The name of the schema containing the character set used by the column.
CHARACTER_SET_NAME
VARCHAR(128)
The name of the character set used by the column.
COLLATION_CATALOG
VARCHAR(128)
The name of the catalog containing the collation used by the column.
COLLATION_SCHEMA
VARCHAR(128)
The name of the schema containing the collation used by the column.
COLLATION_NAME
VARCHAR(128)
The name of the collation used by the column.
DOMAIN_CATALOG
VARCHAR(128)
The name of the catalog containing the domain used by the column.
DOMAIN_SCHEMA
VARCHAR(128)
The name of the schema containing the domain used by the column.
DOMAIN_NAME
VARCHAR(128)
The name of the domain used by the column.
USER_DEFINED_TYPE
_CATALOG
VARCHAR(128)
The name of the user-defined type catalog.
USER_DEFINED_TYPE
_SCHEMA
VARCHAR(128)
The name of the user-defined type schema.
USER_DEFINED_TYPE
_NAME
VARCHAR(128)
The name of the user-defined type name.

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
VARCHAR(128)
The name of the catalog containing the table.
TABLE_SCHEMA
VARCHAR(128)
The name of the schema containing the table.
TABLE_NAME
VARCHAR(128)
The name of the table.
COLUMN_NAME
VARCHAR(128)
The name of the table column.
CONSTRAINT_CATALOG
VARCHAR(128)
The name of the catalog containing the constraint.
CONSTRAINT_NAME
VARCHAR(128)
The name of the constraint.

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
VARCHAR(128)
The name of the catalog containing the table.
TABLE_SCHEMA
VARCHAR(128)
The name of the schema containing the table.
TABLE_NAME
VARCHAR(128)
The name of the table.
CONSTRAINT_CATALOG
VARCHAR(128)
The name of the catalog containing the constraint.
CONSTRAINT_SCHEMA
VARCHAR(128)
The name of the schema containing the constraint.
CONSTRAINT_NAME
VARCHAR(128)
The name of the constraint.

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
VARCHAR(128)
The name of the catalog containing the constraint.
CONSTRAINT_SCHEMA
VARCHAR(128)
The name of the schema containing the constraint.
CONSTRAINT_NAME
VARCHAR(128)
Name of the constraint.
DOMAIN_CATALOG
VARCHAR(128)
The name of the catalog containing the domain on which the constraint is defined.
DOMAIN_SCHEMA
VARCHAR(128)
The name of the schema containing the domain on which the constraint is defined.
DOMAIN_NAME
VARCHAR(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.

DOMAINS

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

.
Column name
Data type
Description
DOMAIN_CATALOG
VARCHAR(128)
The name of the catalog containing the domain.
DOMAIN_SCHEMA
VARCHAR(128)
The name of the schema containing the domain.
DOMAIN_NAME
VARCHAR(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
CHARACTER
CHARACTER LARGE OBJECT
NATIONAL CHARACTER
NATIONAL CHARACTER VARYING
CHARACTER VARYING
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
VARCHAR(128)
The name of the catalog containing the character set used by the domain. NULL if not CHARACTER data type.
CHARACTER_SET_SCHEMA
VARCHAR(128)
The name of the schema containing the character set used by the domain. NULL if not CHARACTER data type.
CHARACTER_SET_NAME
VARCHAR(128)
The name of the character set used by the domain. NULL if not CHARACTER data type.
COLLATION_CATALOG
VARCHAR(128)
The name of the catalog containing the default collation for the character set. NULL if not CHARACTER data type.
COLLATION_SCHEMA
VARCHAR(128)
The name of the schema containing the default collation for the character set. NULL if not CHARACTER data type.
COLLATION_NAME
VARCHAR(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(400)
This shows the default value for the domain.
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 DOMAIN_DEFAULT is syntactically suitable for use in specifying default-value in a CREATE TABLE or ALTER TABLE statement (except when TRUNCATED is shown).

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
VARCHAR(128)
The name of the catalog containing the table.
TABLE_SCHEMA
VARCHAR(128)
The name of the schema containing the table.
TABLE_NAME
VARCHAR(128)
The name of the table.
COLUMN_NAME
VARCHAR(128)
Name of the column.
REMARKS
NCHAR VARYING(254)
Remark for column.

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
VARCHAR(128)
The name of the authorization ident that created the databank.
DATABANK_NAME
VARCHAR(128)
The name of the databank.
DATABANK_TYPE
VARCHAR(20)
One of:
LOG
TRANS
NULL
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
VARCHAR(256)
File name for databank.

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
VARCHAR(128)
The name of the authorization ident that created the ident.
IDENT_NAME
VARCHAR(128)
The name of the ident.
IDENT_TYPE
VARCHAR(20)
One of:
GROUP
OS_USER
PROGRAM
USER
.

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
VARCHAR(128)
Catalog name for the secondary index.
INDEX_SCHEMA
VARCHAR(128)
Schema name for the secondary index.
INDEX_NAME
VARCHAR(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
VARCHAR(128)
Name of the table on which the index is defined.
COLUMN_NAME
VARCHAR(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
VARCHAR(128)
Catalog name for the collation. The value is NULL if the column is not of CHARACTER type.
COLLATION_SCHEMA
VARCHAR(128)
Schema name for the collation. The value is NULL if the column is not of CHARACTER type.
COLLATION_NAME
VARCHAR(128)
Name of the collation. The value is NULL if the column is not of CHARACTER type.

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
VARCHAR(128)
Catalog name for the secondary index.
INDEX_SCHEMA
VARCHAR(128)
Schema name for the secondary index.
INDEX_NAME
VARCHAR(128)
Name of the secondary index.
TABLE_NAME
VARCHAR(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.

EXT_OBJECT_IDENT_USAGE

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

Column name
Data type
Description
CREATOR_NAME
VARCHAR(128)
Name of authorization ident that created the object.
OBJECT_CATALOG
VARCHAR(128)
Catalog name for the object.
OBJECT_SCHEMA
VARCHAR(128)
Schema name for the object.
OBJECT_NAME
VARCHAR(128)
Name of the object.
OBJECT_TYPE
VARCHAR(20)
One of:
ASSERTION
BASE TABLE
CHARACTER SET
COLLATION
CONSTRAINT
DATABANK
DOMAIN
FUNCTION
IDENT
INDEX
MODULE
PROCEDURE
SCHEMA
SEQUENCE
SHADOW
SYNONYM
TRIGGER
VIEW
.
CREATION_DATE
TIMESTAMP
Time when object was created.
ALTERATION_DATE
TIMESTAMP
Time when object was last altered.
REMARKS
NCHAR VARYING
Remark for the object.

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
MODULE
PROCEDURE
SEQUENCE
SHADOW
TRIGGER
VIEW
.
USED_OBJECT_CATALOG
VARCHAR(128)
Catalog name for used object.
USED_OBJECT_SCHEMA
VARCHAR(128)
Schema name for used object.
USED_OBJECT_NAME
VARCHAR(128)
Name of used object
USED_SPECIFIC_NAME
VARCHAR(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
MODULE
PROCEDURE
SHADOW
STATEMENT
TRIGGER
VIEW
.
USING_OBJECT_CATALOG
VARCHAR(128)
Catalog name for using object
USING_OBJECT_SCHEMA
VARCHAR(128)
Schema name for using object
USING_OBJECT_NAME
VARCHAR(128)
Name of using object
USING_SPECIFIC_NAME
VARCHAR(128)
Specific name for using object

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
MODULE
PROCEDURE
SHADOW
STATEMENT
TRIGGER
VIEW
.
USING_OBJECT_CATALOG
VARCHAR(128)
Catalog name for using object.
USING_OBJECT_SCHEMA
VARCHAR(128)
Schema name for using object.
USING_OBJECT_NAME
VARCHAR(128)
Name of using object
USING_SPECIFIC_NAME
VARCHAR(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
MODULE
PROCEDURE
SEQUENCE
SHADOW
TRIGGER
VIEW
.
USED_OBJECT_CATALOG
VARCHAR(128)
Catalog name for used object
USED_OBJECT_SCHEMA
VARCHAR(128)
Schema name for used object
USED_OBJECT_NAME
VARCHAR(128)
Name of used object
USED_SPECIFIC_NAME
VARCHAR(128)
Specific name for used object

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
VARCHAR(128)
Name of authorization ident that has received the privilege.
OBJECT_CATALOG
VARCHAR(128)
Catalog name for object.
OBJECT_SCHEMA
VARCHAR(128)
Schema name for object.
OBJECT_NAME
VARCHAR(128)
Name of object on which privilege is granted.
OBJECT_TYPE
VARCHAR(20)
One of:
CHARACTER SET
DOMAIN
DATABANK
FUNCTION
IDENT
PROCEDURE
SEQUENCE
STATEMENT
.
PRIVILEGE_TYPE
VARCHAR(20)
One of:
EXECUTE
MEMBER
TABLE
USAGE
.
GRANTOR
VARCHAR(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.

EXT_ONEROW

Column name
Data type
Description
M
CHAR(1)
Contains the value M

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
VARCHAR(128)
The name of the schema to which the routine belongs.
SPECIFIC_NAME
VARCHAR(128)
The specific name of the routine
ROUTINE_LENGTH
INTEGER
The total length of the routine definition
ROUTINE_DEFINITION
NCHAR VARYING(800)
The source text for the routine

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
VARCHAR(128)
The name of the catalog containing the routine
ROUTINE_SCHEMA
VARCHAR(128)
The name of the schema containing the routine
ROUTINE_NAME
VARCHAR(128)
The name of the routine
SPECIFIC_NAME
VARCHAR(128)
The specific name of the routine
MODULE_NAME
VARCHAR(128)
The name of the module in which the routine is defined

EXT_SEQUENCES

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

Column name
Data type
Description
SEQUENCE_CATALOG
VARCHAR(128)
Catalog name for the sequence
SEQUENCE_SCHEMA
VARCHAR(128)
Schema name for the sequence.
SEQUENCE_NAME
VARCHAR(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
INTEGER
The initial value for the sequence.
INCREMENT
INTEGER
The increment for the sequence.
MAXIMUM_VALUE
INTEGER
The maximum value for the sequence.

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
VARCHAR(128)
Creator of the shadow.
SHADOW_NAME
VARCHAR(128)
Name of the shadow.
DATABANK_NAME
VARCHAR(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
VARCHAR(256)
Name of physical file for shadow.

EXT_SOURCE_DEFINITION

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

Column name
Data type
Description
OBJECT_CATALOG
VARCHAR(128)
Catalog name for object.
OBJECT_SCHEMA
VARCHAR(128)
Schema name for object.
OBJECT_NAME
VARCHAR(128)
Name of object.
SPECIFIC_NAME
VARCHAR(128)

COLUMN_NAME
VARCHAR(128)

OBJECT_TYPE
VARCHAR(20)
One of:
CONSTRAINT
FUNCTION
MODULE
PROCEDURE
TRIGGER
.
SOURCE_DEFINITION
NCHAR VARYING(800)
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 800 characters of the source.
2 = this is the second 800 characters of the source, etc.

EXT_STATEMENTS

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

Column name
Data type
Description

STATEMENT_CATALOG

VARCHAR(128)

Catalog name for precompiled statement.

STATEMENT_SCHEMA

VARCHAR(128)

Schema name for precompiled statement.

STATEMENT_NAME

VARCHAR(128)

Name of precompiled statement.

STATEMENT_TYPE

INTEGER

Type of precompiled statement.

STATEMENT_DEFINITION

NCHAR VARYING(400)

Definition text of precompiled statement. If the definition does not fit, the NULL value is shown. The definition can be found in EXT_STATEMENT_DEFINITION.

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

VARCHAR(128)

Schema name for precompiled statement.

STATEMENT_NAME

VARCHAR(128)

Name of precompiled statement.

STATEMENT_SEQUENCE_NO

INTEGER

The sequence number.

STATEMENT_DEFINITION

NCHAR VARYING(800)

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

EXT_STATISTICS

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

Column name
Data type
Description
TABLE_CATALOG
VARCHAR(128)
Catalog name for base table.
TABLE_SCHEMA
VARCHAR(128)
Schema name for base table.
TABLE_NAME
VARCHAR(128)
Name of base table.
STATISTICS_GATHERED
TIMESTAMP(2)
Time when statistics for this table was collected

EXT_SYNONYMS

The EXT_SYNONYMS system view shows synonyms on accessible tables.

Column name
Data type
Description
SYNONYM_CATALOG
VARCHAR(128)
Catalog name for synonym.
SYNONYM_SCHEMA
VARCHAR(128)
Schema name for synonym.
SYNONYM_NAME
VARCHAR(128)
Name of synonym.
TABLE_CATALOG
VARCHAR(128)
Catalog for table on which synonym is defined.
TABLE_SCHEMA
VARCHAR(128)
Schema name for table.
TABLE_NAME
VARCHAR(128)
Name of table.

EXT_SYSTEM_PRIVILEGES

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

Column name
Data type
Description
GRANTEE
VARCHAR(128)
Name of grantee.
PRIVILEGE_TYPE
VARCHAR(20)
One of:
BACKUP
DATABANK
IDENT
SCHEMA
SHADOW
STATISTICS
.
GRANTOR
VARCHAR(128)
Name of grantor.
IS_GRANTABLE
VARCHAR(3)
One of:
YES = grantee has grant option
NO = grantee has not grant option.

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
VARCHAR(128)
Catalog for table.
TABLE_SCHEMA
VARCHAR(128)
Schema name for table.
TABLE_NAME
VARCHAR(128)
Name of table.
DATABANK_CREATOR
VARCHAR(128)
Name of authorization ident that created databank.
DATABANK_NAME
VARCHAR(128)
Name of databank.

KEY_COLUMN_USAGE

The KEY_COLUMN_USAGE system view lists the table columns that are constrained as keys and owned by the current ident.

Column name
Data type
Description
CONSTRAINT_CATALOG
VARCHAR(128)
The name of the catalog containing the table constraint.
CONSTRAINT_SCHEMA
VARCHAR(128)
The name of the schema containing the table or view.
CONSTRAINT_NAME
VARCHAR(128)
The name of the table constraint.
TABLE_CATALOG
VARCHAR(128)
The name of the catalog containing the table or view.
TABLE_SCHEMA
VARCHAR(128)
The name of the schema containing the table or view.
TABLE_NAME
VARCHAR(128)
The name of the table or view.
COLUMN_NAME
VARCHAR(128)
The name of the column.
ORDINAL_POSITION
INTEGER
The ordinal position of the column within the key.

MODULES

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

Column name
Data type
Description
MODULE_CATALOG
VARCHAR(128)
Catalog name for module.
MODULE_SCHEMA
VARCHAR(128)
Schema name for module.
MODULE_NAME
VARCHAR(128)
Name of module.
DEFAULT_CHARACTER_SET
_CATALOG
VARCHAR(128)
Catalog name for default character set.
DEFAULT_CHARACTER_SET
_SCHEMA
VARCHAR(128)
Schema name for default character set.
DEFAULT_CHARACTER_SET
_NAME
VARCHAR(128)
The name for default character set.
DEFAULT_SCHEMA_CATALOG
VARCHAR(128)
Catalog name for default schema of the module.
DEFAULT_SCHEMA_NAME
VARCHAR(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 source definition view.
SQL_PATH
VARCHAR(1000)
The default path for the module

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
VARCHAR(128)
The catalog name for the specific name of the routine.
SPECIFIC_SCHEMA
VARCHAR(128)
The schema name for the specific name of the routine.
SPECIFIC_NAME
VARCHAR(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
CHAR(5)
Indicates whether the parameter is IN, OUT or INOUT.
PARAMETER_NAME
VARCHAR(128)
The name of the parameter.
DATA_TYPE
VARCHAR(30)
The data type of the parameter.
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
VARCHAR(128)
The name of the catalog containing the character set.
CHARACTER_SET
_SCHEMA
VARCHAR(128)
The name of the schema containing the character set.
CHARACTER_SET
_NAME
VARCHAR(128)
Name of the character set.
COLLATION_CATALOG
VARCHAR(128)
The name of the catalog containing the collation.
COLLATION_SCHEMA
VARCHAR(128)
The name of the schema containing the collation.
COLLATION_NAME
VARCHAR(128)
Name of the collation.
USER_DEFINED_TYPE
_CATALOG
VARCHAR(128)
The name of the user-defined type catalog.
USER_DEFINED_TYPE
_SCHEMA
VARCHAR(128)
The name of the user-defined type schema.
USER_DEFINED_TYPE
_NAME
VARCHAR(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).

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
VARCHAR(128)
The name of the catalog containing the referential constraint.
CONSTRAINT_SCHEMA
VARCHAR(128)
The name of the schema containing the referential constraint.
CONSTRAINT_NAME
VARCHAR(128)
The name of the referential constraint.
UNIQUE_CONSTRAINT
_CATALOG
VARCHAR(128)
The name of the catalog containing the unique constraint being referenced.
UNIQUE_CONSTRAINT
_SCHEMA
VARCHAR(128)
The name of the schema containing the unique constraint being referenced
UNIQUE_CONSTRAINT
_NAME
VARCHAR(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
.

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
VARCHAR(128)
The catalog name for the specific name of the routine.
SPECIFIC_SCHEMA
VARCHAR(128)
The schema name for the specific name of the routine.
SPECIFIC_NAME
VARCHAR(128)
The specific name of the routine.
ROUTINE_CATALOG
VARCHAR(128)
The name of the catalog containing the routine.
ROUTINE_SCHEMA
VARCHAR(128)
The name of the schema containing the routine.
ROUTINE_NAME
VARCHAR(128)
The name of the routine.
TABLE_CATALOG
VARCHAR(128)
The name of the catalog containing the table.
TABLE_SCHEMA
VARCHAR(128)
The name of the schema containing the table.
TABLE_NAME
VARCHAR(128)
The name of the table.
COLUMN_NAME
VARCHAR(128)
The name of the column.

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
VARCHAR(128)
The name of the ident who granted the privilege.
GRANTEE
VARCHAR(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
VARCHAR(128)
The catalog name for the specific name of the routine.
SPECIFIC_SCHEMA
VARCHAR(128)
The schema name for the specific name of the routine.
SPECIFIC_NAME
VARCHAR(128)
The specific name of the routine.
ROUTINE_CATALOG
VARCHAR(128)
The name of the catalog containing the routine.
ROUTINE_SCHEMA
VARCHAR(128)
The name of the schema containing the routine.
ROUTINE_NAME
VARCHAR(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.

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
VARCHAR(128)
The catalog name for the specific name of the routine.
SPECIFIC_SCHEMA
VARCHAR(128)
The schema name for the specific name of the routine.
SPECIFIC_NAME
VARCHAR(128)
The specific name of the routine.
ROUTINE_CATALOG
VARCHAR(128)
The name of the catalog containing the routine.
ROUTINE_SCHEMA
VARCHAR(128)
The name of the schema containing the routine.
ROUTINE_NAME
VARCHAR(128)
The name of the routine.
TABLE_CATALOG
VARCHAR(128)
The name of the catalog containing the table.
TABLE_SCHEMA
VARCHAR(128)
The name of the schema containing the table.
TABLE_NAME
VARCHAR(128)
The name of the table.

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
VARCHAR(128)
The catalog name for the specific name of the routine.
SPECIFIC_SCHEMA
VARCHAR(128)
The schema name for the specific name of the routine.
SPECIFIC_NAME
VARCHAR(128)
The specific name of the routine.
ROUTINE_CATALOG
VARCHAR(128)
The name of the catalog containing the routine.
ROUTINE_SCHEMA
VARCHAR(128)
The name of the schema containing the routine.
ROUTINE_NAME
VARCHAR(128)
The name of the routine.
MODULE_CATALOG
VARCHAR(128)
The name of the catalog containing the module to which the routine belongs.
MODULE_SCHEMA
VARCHAR(128)
The name of the schema containing the module to which the routine belongs.
MODULE_NAME
VARCHAR(128)
The name of the module to which the routine belongs.
ROUTINE_TYPE
VARCHAR(20)
Indicates whether the routine is a function or a procedure.
DATA_TYPE
VARCHAR(30)
The data type returned by the function.
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
VARCHAR(128)
The name of the catalog containing the character set.
CHARACTER_SET_SCHEMA
VARCHAR(128)
The name of the schema containing the character set.
CHARACTER_SET_NAME
VARCHAR(128)
Name of the character set.
COLLATION_CATALOG
VARCHAR(128)
The name of the catalog containing the collation.
COLLATION_SCHEMA
VARCHAR(128)
The name of the schema containing the collation.
COLLATION_NAME
VARCHAR(128)
Name of the collation.
USER_DEFINED_TYPE
_CATALOG
VARCHAR(128)
The name of the user-defined type catalog.
USER_DEFINED_TYPE
_SCHEMA
VARCHAR(128)
The name of the user-defined type schema.
USER_DEFINED_TYPE
_NAME
VARCHAR(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
CHAR(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
CHAR(20)
One of:
CONTAINS SQL
READS SQL
MODIFIES SQL
.
ROUTINE_BODY
CHAR(20)
One of:
SQL = the routine is an SQL routine
EXTERNAL = the routine is an external routine.
ROUTINE_DEFINITION
NCHAR VARYING(400)
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 EXT_SOURCE_DEFINITION.
EXTERNAL_NAME
VARCHAR(128)
The external name of the routine if it is an external routine, otherwise the NULL value is shown.
PARAMETER_STYLE
CHAR(20)
The parameter passing style of the routine if it is an external routine, otherwise the NULL value is shown.
SQL_PATH
VARCHAR(1000)
The path for the routine.
The NULL value is shown if no path is defined.
SCHEMA_LEVEL_ROUTINE
CHAR(3)
One of:
YES
= the routine was created on its own
NO = the routine was created in a module.
IS_RESULT
CHAR(3)
One of:
YES = the routine returns a result-set
NO = the routine does not return a result-set.

SCHEMATA

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

Column name
Data type
Description
CATALOG_NAME
VARCHAR(128)
The name of the catalog containing the schema.
SCHEMA_NAME
VARCHAR(128)
The name of the schema.
SCHEMA_OWNER
VARCHAR(128)
The name of the ident who created the schema.
DEFAULT_CHARACTER_SET_CATALOG
VARCHAR(128)
The name of the catalog that contains the default character set for the schema.
DEFAULT_CHARACTER_SET_SCHEMA
VARCHAR(128)
The name of the schema that contains the default character set for the schema.
DEFAULT_CHARACTER_SET_NAME
VARCHAR(128)
The name of the default character set for the schema.
SQL_PATH
VARCHAR(1000)
The default path for the 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.

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
VARCHAR(128)
The name of the catalog containing the table constraint.
CONSTRAINT_SCHEMA
VARCHAR(128)
The name of the schema containing the table or view.
CONSTRAINT_NAME
VARCHAR(128)
The name of the table constraint.
TABLE_CATALOG
VARCHAR(128)
The name of the catalog containing the table or view.
TABLE_SCHEMA
VARCHAR(128)
The name of the schema containing the table or view.
TABLE_NAME
VARCHAR(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.

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
VARCHAR(128)
The name of the ident who granted the privilege.
GRANTEE
VARCHAR(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
VARCHAR(128)
The name of the catalog containing the table.
TABLE_SCHEMA
VARCHAR(128)
The name of the schema containing the table.
TABLE_NAME
VARCHAR(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.

TABLES

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

Column name
Data type
Description
TABLE_CATALOG
VARCHAR(128)
The name of the catalog containing the table or view.
TABLE_SCHEMA
VARCHAR(128)
The name of the schema containing the table or view.
TABLE_NAME
VARCHAR(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.

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
VARCHAR(128)
The name of the catalog containing the translation.
TRANSLATION
_SCHEMA
VARCHAR(128)
The name of the schema containing the translation.
TRANSLATION_NAME
VARCHAR(128)
The name of the translation.
SOURCE_CHARACTER
_SET_CATALOG
VARCHAR(128)
The name of the catalog containing the source character set.
SOURCE_CHARACTER
_SET_SCHEMA
VARCHAR(128)
The name of the schema containing the source character set.
SOURCE_CHARACTER
_SET_NAME
VARCHAR(128)
The name of the source character set.
TARGET_CHARACTER
_SET_CATALOG
VARCHAR(128)
The name of the catalog containing the target character set.
TARGET_CHARACTER
_SET_SCHEMA
VARCHAR(128)
The name of the schema containing the target character set.
TARGET_CHARACTER
_SET_NAME
VARCHAR(128)
The name of the target character set.

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
VARCHAR(128)
The name of the catalog containing the trigger.
TRIGGER_SCHEMA
VARCHAR(128)
The name of the schema containing the trigger.
TRIGGER_NAME
VARCHAR(128)
The name of the trigger.
EVENT_OBJECT
_CATALOG
VARCHAR(128)
The name of the catalog containing the table or view on which the trigger is created.
EVENT_OBJECT
_SCHEMA
VARCHAR(128)
The name of the schema containing the table or view on which the UPDATE trigger is created.
EVENT_OBJECT
_TABLE
VARCHAR(128)
The name of the table or view on which the UPDATE trigger is created.
EVENT_OBJECT
_COLUMN
VARCHAR(128)
The name of the table or view column referenced in the column list of the UPDATE trigger event.

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
VARCHAR(128)
The name of the catalog containing the trigger.
TRIGGER_SCHEMA
VARCHAR(128)
The name of the schema containing the trigger.
TRIGGER_NAME
VARCHAR(128)
The name of the trigger.
TABLE_CATALOG
VARCHAR(128)
The name of the catalog containing the referenced table or view.
TABLE_SCHEMA
VARCHAR(128)
The name of the schema containing the referenced table or view.
TABLE_NAME
VARCHAR(128)
The name of the referenced table or view.
COLUMN_NAME
VARCHAR(128)
The name of the referenced column.

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
VARCHAR(128)
The name of the catalog containing the trigger.
TRIGGER_SCHEMA
VARCHAR(128)
The name of the schema containing the trigger.
TRIGGER_NAME
VARCHAR(128)
The name of the trigger.
TABLE_CATALOG
VARCHAR(128)
The name of the catalog containing the referenced table or view.
TABLE_SCHEMA
VARCHAR(128)
The name of the schema containing the referenced table or view.
TABLE_NAME
VARCHAR(128)
The name of the referenced table or view.

TRIGGERS

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

Column name
Data type
Description
TRIGGER_CATALOG
VARCHAR(128)
The name of the catalog containing the trigger.
TRIGGER_SCHEMA
VARCHAR(128)
The name of the schema containing the trigger.
TRIGGER_NAME
VARCHAR(128)
The name of the trigger.
EVENT_MANIPULATION
CHAR(20)
The data manipulation event triggering execution of the trigger (the trigger event).
One of:
INSERT
DELETE
UPDATE
.
EVENT_OBJECT_CATALOG
VARCHAR(128)
The name of the catalog containing the table or view on which the trigger is created.
EVENT_OBJECT_SCHEMA
VARCHAR(128)
The name of the schema containing the table or view on which the trigger is created.
EVENT_OBJECT_TABLE
VARCHAR(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(400)
The character representation of the search condition in the WHEN clause of the trigger. If the length of the text exceeds 400 characters, the NULL value will be shown.
ACTION_STATEMENT
NCHAR VARYING(400)
The character representation of the body of the trigger. If the length of the text exceeds 400 characters, the NULL value will be shown.
ACTION_ORIENTATION
CHAR(20)
One of:
ROW = the trigger is a row trigger
STATEMENT = the trigger is a statement trigger.
CONDITION_TIMING
CHAR(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.
CONDITION_REFERENCE
_OLD_TABLE
VARCHAR(128)
The identifier specified in the OLD TABLE clause.
CONDITION_REFERENCE
_NEW_TABLE
VARCHAR(128)
The identifier specified in the NEW TABLE clause.
CONDITION_REFERENCE
_OLD_ROW
VARCHAR(128)
The identifier specified in the OLD ROW clause.
CONDITION_REFERENCE
_NEW_ROW
VARCHAR(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.

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
VARCHAR(128)
The name of the ident who granted the privilege.
GRANTEE
VARCHAR(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
VARCHAR(128)
The name of the catalog that contains the object character set or collation.
OBJECT_SCHEMA
VARCHAR(128)
The name of the schema that contains the object character set or collation.
OBJECT_NAME
VARCHAR(128)
The name of the character set or collation.
OBJECT_TYPE
VARCHAR(128)
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.

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
VARCHAR(128)
The name of the catalog containing the view.
VIEW_SCHEMA
VARCHAR(128)
The name of the schema containing the view.
VIEW_NAME
VARCHAR(128)
The name of the view.
TABLE_CATALOG
VARCHAR(128)
The name of the catalog containing the table.
TABLE_SCHEMA
VARCHAR(128)
The name of the schema containing the table.
TABLE_NAME
VARCHAR(128)
The name of the table.
COLUMN_NAME
VARCHAR(128)
The name of the column.

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
VARCHAR(128)
The name of the catalog containing the view.
VIEW_SCHEMA
VARCHAR(128)
The name of the schema containing the view.
VIEW_NAME
VARCHAR(128)
The name of the view.
TABLE_CATALOG
VARCHAR(128)
The name of the catalog containing the table.
TABLE_SCHEMA
VARCHAR(128)
The name of the schema containing the table.
TABLE_NAME
VARCHAR(128)
The name of the table.

VIEWS

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

Column name
Data type
Description
TABLE_CATALOG
VARCHAR(128)
The name of the catalog containing the table or view.
TABLE_SCHEMA
VARCHAR(128)
The name of the schema containing the table or view.
TABLE_NAME
VARCHAR(128)
The name of the table or view.
VIEW_DEFINITION
NCHAR VARYING(400)
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.
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

X/Open-95
SQL-92

EXTENDED

The views with names beginning EXT_ are a Mimer SQL extension.

SQL/PSM

YES

All views with names beginning ROUTINE_ or MODULE_ are included in the SQL/PSM standard.

SQL-99

YES

All views with TRIGGER in the name are included in the SQL-99 standard.



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Documentation TOC PREV NEXT INDEX