Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


INFO_SCHEM


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

View name
Description
CHARACTER_SETS
Accessible character sets and their default collations.
COLLATIONS
Accessible collations.
COLUMN_PRIVILEGES
Privileges on table columns.
COLUMNS
Accessible table columns.
INDEXES
Accessible indexes.
SCHEMATA
All schemas in the database.
SERVER_INFO
Attributes of the current database server.
SQL_LANGUAGES
All supported SQL standards and dialects.
TABLE_PRIVILEGES
Privileges on tables.
TABLES
Accessible tables.
TRANSLATIONS
Accessible character set translations.
USAGE_PRIVILEGES
Privileges on character sets and collations.
VIEWS
Accessible views.

INFO_SCHEM.CHARACTER_SETS

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

The view includes one row describing the character set named SQL_TEXT.

Column name
Data type
Description
CHARSET_CAT
NCHAR VARYING(128)
The name of the catalog containing the character set.
CHARSET_SCHEM
NCHAR VARYING(128)
The name of the schema containing the character set.
CHARSET_NAME
NCHAR VARYING(128)
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.
NUM_CHARS
INTEGER
The number of characters in the character set.
DEF_COLLATE_CAT
NCHAR VARYING(128)
The name of the catalog containing the default collation for the character set.
DEF_COLLATE_SCHEM
NCHAR VARYING(128)
The name of the schema containing the default collation for the character set.
DEF_COLLATE_NAME
NCHAR VARYING(128)
The name of the default collation for the character set.
REMARKS
NCHAR VARYING(254)
May contain descriptive information about the character set.

INFO_SCHEM.COLLATIONS

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

The view includes one row describing the collation named SQL_TEXT which is defined on the character set named SQL_TEXT and is the default collation for that character set.

Column name
Data type
Description
COLLATION_CAT
NCHAR VARYING(128)
The name of the catalog containing the collation.
COLLATION_SCHEM
NCHAR VARYING(128)
The name of the schema containing the collation.
COLLATION_NAME
NCHAR VARYING(128)
Name of the collation.
CHARSET_CAT
NCHAR VARYING(128)
The name of the catalog containing the character set on which the collation is defined.
CHARSET_SCHEM
NCHAR VARYING(128)
The name of the schema containing the character set on which the collation is defined.
CHARSET_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.
REMARKS
NCHAR VARYING(254)
May contain descriptive information about the collation.

INFO_SCHEM.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.

Granting a privilege on an entire table implicitly grants privileges on each of the table columns (as well as on future columns in the table), each of which is shown in this view.

The view includes rows that represent the implicit privileges that the creator of a table has on the table.

A row will be shown for each instance of a privilege granted on each table column (whether or not the specified privilege can be granted or revoked on individual columns), the exception is DELETE which can never apply to an individual column.

Column name
Data type
Description
GRANTOR
NCHAR VARYING(128)
The name of the ident who granted the privilege. For rows that describe the implicit privileges that a table creator has on each column in a table, the name _SYSTEM is shown.
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_CAT
NCHAR VARYING(128)
The name of the catalog containing the table.
TABLE_SCHEM
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: 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 the WITH GRANT OPTION
NO
= the privilege is held without the WITH GRANT OPTION.
REMARKS
NCHAR VARYING(254)
May contain descriptive information about the privilege.

INFO_SCHEM.COLUMNS

The COLUMNS system view lists the table columns that the current ident is privileged to access.

If the current ident holds one or more of INSERT, DELETE or SELECT privilege on a table, then one row is shown for each column in the table.

If the current ident holds REFERENCES or UPDATE privilege on one or more table columns, then one row is shown for each of the columns.

Column name
Data type
Description
TABLE_CAT
NCHAR VARYING(128)
The name of the catalog containing the table or view.
TABLE_SCHEM
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_DEF
NCHAR VARYING(200)
This shows the default value for the column.
For more information, see INFORMATION_SCHEMA.COLUMNS.
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
.
CHAR_MAX_LENGTH
INTEGER
For a CHARACTER data type, this shows the maximum length in characters. For all other data types it is the NULL value.
CHAR_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. (For single octet character sets, this is the same as CHAR_MAX_LENGTH).
NUM_PREC
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. NUM_PREC_RADIX indicates the units of measurement.
NUM_PREC_RADIX
INTEGER
For NUMERIC data types, the value 10 is shown because NUM_PREC specifies a number of decimal digits. For all other data types it is the NULL value. NUM_PREC and NUM_PREC_RADIX can be combined to calculate the maximum number that the column can hold.
NUM_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_PREC
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_PREC
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.
CHAR_SET_CAT
NCHAR VARYING(128)
The name of the catalog containing the character set used by the column.
CHAR_SET_SCHEM
NCHAR VARYING(128)
The name of the schema containing the character set used by the column.
CHAR_SET_NAME
NCHAR VARYING(128)
The name of the character set used by the column.
COLLATION_CAT
NCHAR VARYING(128)
The name of the catalog containing the collation used by the column.
COLLATION_SCHEM
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_CAT
NCHAR VARYING(128)
The name of the catalog containing the domain used by the column.
DOMAIN_SCHEM
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.
REMARKS
NCHAR VARYING(254)
May contain descriptive information about the column.

INFO_SCHEM.INDEXES

The INDEXES system view lists the index columns created on tables or views to which the current ident has SELECT access.

Column name
Data type
Description
TABLE_CAT
NCHAR VARYING(128)
The name of the catalog containing the table or view.
TABLE_SCHEM
NCHAR VARYING(128)
The name of the schema containing the table or view.
TABLE_NAME
NCHAR VARYING(128)
The name of the base table on which the index column exists.
COLUMN_NAME
NCHAR VARYING(128)
The name of the column of the base table and index.
INDEX_NAME
NCHAR VARYING(128)
The unique name of the index.
ORDINAL_POSITION
INTEGER
The ordinal number of the column in the index. The ordering is determined by ordering of the columns in the CREATE INDEX statement. The numbering of the columns of the index starts at 1 and increases contiguously.
NON_UNIQUE
VARCHAR(3)
One of:
NO = only one row is allowed in the table identified by TABLE_NAME for each combination of values in the columns of the index
YES = the index is not a unique index.
ASC_OR_DESC
CHAR(1)
One of:
A = the order of the referenced index column is ascending
D = the order of the referenced index column is descending.
REMARKS
NCHAR VARYING(254)
May contain descriptive information about the index.

INFO_SCHEM.SCHEMATA

The SCHEMATA system view lists all the schemas in the database.

Column name
Data type
Description
CAT_NAME
NCHAR VARYING(128)
The name of the catalog containing the schema.
SCHEM_NAME
NCHAR VARYING(128)
The name of the schema.
SCHEM_OWNER
NCHAR VARYING(128)
The name of the ident who created the schema.
DEF_CHAR_SET_CAT
NCHAR VARYING(128)
The name of the catalog that contains the default character set for the schema.
DEF_CHAR_SET_SCHEM
NCHAR VARYING(128)
The name of the schema that contains the default character set for the schema.
DEF_CHAR_SET_NAME
NCHAR VARYING(128)
The name of the default character set for the schema.
REMARKS
NCHAR VARYING(254)
May contain descriptive information about the table.

INFO_SCHEM.SERVER_INFO

The SERVER_INFO system view lists the attributes of the database server to which the application is currently connected.

Each row provides information about one attribute. The attribute is identified in the SERVER_ATTRIBUTE column and the value of the attribute for the current server is shown in the ATTRIBUTE_VALUE column.

Column name
Data type
Description
SERVER_ATTRIBUTE
VARCHAR(254)
The name of the server attribute. One of:
CATALOG_NAME
COLLATION_SEQ
IDENTIFIER_LENGTH
INTERVAL_FRACTIONAL_PRECISION
INTERVAL_LEADING_PRECISION
ROW_LENGTH
TIME_PRECISION
TIMESTAMP_PRECISION
TXN_ISOLATION
USERID_LENGTH
ATTRIBUTE_VALUE
VARCHAR(254)
The value of the corresponding server attribute.
For CATALOG_NAME:
YES = the server supports catalog names, otherwise
NO.
For COLLATION_SEQ: either ISO 8859-1 or EBCDIC to indicate the assumed ordering of the character set for the server.
For IDENTIFIER_LENGTH: the maximum number of characters allowed for a user-defined name, shown as the character string representation of the decimal value.
For INTERVAL_FRACTIONAL_PRECISION: the default seconds precision for all interval data types with a seconds component.
For INTERVAL_LEADING_PRECISION: the default leading precision for all intervals.
For ROW_LENGTH: the maximum size of a row, shown as the character string representation of the decimal value.
For TIME_PRECISION: the default seconds precision for all objects of data type TIME.
For TIMESTAMP_PRECISION: the default seconds precision for all objects of data type TIMESTAMP.
For TXN_ISOLATION: the initial transaction isolation level assumed by the server. One of:
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
.
For USERID_LENGTH: the maximum number of characters of a user name (or authorization identifier), shown as the character string representation of the decimal value.

INFO_SCHEM.SQL_LANGUAGES

The SQL_LANGUAGES system view lists all the SQL standards and SQL dialects to which the SQL product claims conformance (including subsets defined by ISO and vendor-specific versions).

All versions can be shown in a single table. Each row has at least the columns described below, however additional columns may be specified for an individual vendor or specific international standard.

Column name
Data type
Description
SOURCE
VARCHAR(254)
The organization that defined the SQL version.
SOURCE_YEAR
VARCHAR(254)
The year the relevant source document was approved.
CONFORMANCE
VARCHAR(254)
The conformance level to the relevant document that the implementation claims.
INTEGRITY
VARCHAR(254)
(Meaning no longer defined).
IMPLEMENTATION
VARCHAR(254)
A character string, defined by the vendor, that uniquely defines the vendor's SQL product.
BINDING_STYLE
VARCHAR(254)
Included to envisage future adoption of direct, module or other binding styles.
PROGRAMMING_LANG
VARCHAR(254)
The host language for which the binding style is supported.

INFO_SCHEM.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.

An entry in this view does not assert that the grantee holds the privilege on any specific column of the table, because privileges can be granted on a table and then revoked on individual columns.

Information about privileges on specific columns is presented in the COLUMN_PRIVILEGES view.

Column name
Data type
Description
GRANTOR
NCHAR VARYING(128)
The name of the ident who granted the privilege. For rows that describe the implicit privileges that a table creator has on each column in a table, the name _SYSTEM is shown.
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_CAT
NCHAR VARYING(128)
The name of the catalog containing the table.
TABLE_SCHEM
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 column privilege that was granted. One of:
DELETE
INSERT
REFERENCES
SELECT
UPDATE
.
Rows where privilege type is REFERENCES or UPDATE 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.

INFO_SCHEM.TABLES

The TABLES system view lists the tables and views on which the current ident holds one or more privileges (INSERT, DELETE or SELECT; or REFERENCES or UPDATE on one or more columns).

Column name
Data type
Description
TABLE_CAT
NCHAR VARYING(128)
The name of the catalog containing the table or view.
TABLE_SCHEM
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.
REMARKS
NCHAR VARYING(254)
May contain descriptive information about the table.

INFO_SCHEM.TRANSLATIONS

The TRANSLATIONS system view lists the 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_CAT
NCHAR VARYING(128)
The name of the catalog containing the translation.
TRANSLATION_SCHEM
NCHAR VARYING(128)
The name of the schema containing the translation.
TRANSLATION_NAME
NCHAR VARYING(128)
The name of the translation.
SRC_CHARSET_CAT
NCHAR VARYING(128)
The name of the catalog containing the source character set.
SRC_CHARSET_SCHEM
NCHAR VARYING(128)
The name of the schema containing the source character set.
SRC_CHARSET_NAME
NCHAR VARYING(128)
The name of the source character set.
TGT_CHARSET_CAT
NCHAR VARYING(128)
The name of the catalog containing the target character set.
TGT_CHARSET_SCHEM
NCHAR VARYING(128)
The name of the schema containing the target character set.
TGT_CHARSET_NAME
NCHAR VARYING(128)
The name of the target character set.
REMARKS
NCHAR VARYING(254)
May contain descriptive information about the table.

INFO_SCHEM.USAGE_PRIVILEGES

The USAGE_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 character sets and collations.

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_CAT
NCHAR VARYING(128)
The name of the catalog that contains the object character set or collation.
OBJECT_SCHEM
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
VARCHAR(20)
One of:
CHARACTER SET = the privilege is held on a character set
COLLATION = the privilege is held on a collation.
PRIVILEGE_TYPE
VARCHAR(20)
This will always be USAGE.
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.

INFO_SCHEM.VIEWS

The VIEWS system view lists the viewed tables on which the current ident holds one or more privileges (INSERT, DELETE or SELECT; or REFERENCES or UPDATE on one or more columns).

The views listed in this system view also appear in the TABLES system view.

Column name
Data type
Description
TABLE_CAT
NCHAR VARYING(128)
The name of the catalog containing the table or view.
TABLE_SCHEM
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.
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.
REMARKS
NCHAR VARYING(254)
May contain descriptive information about the view.

Standard Compliance

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

Standard
Compliance
Comments

Mimer SQL extension
The INFO_SCHEM views are based on the
X/Open, CAE specification, Structured Query Language (SQL), Version 2.X/Open document number: C449. ISBN: 1-85912-151-9.


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX