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