Chapter 16 – SQL Catalogs¶

INFORMATION_SCHEMA.INFORMATION_SCHEMA_CATALOG_NAME¶

Definition:

CREATE TABLE INFORMATION_SCHEMA_CATALOG_NAME (
      CATALOG_NAME SQL_IDENTIFIER,
      CONSTRAINT INFORMATION_SCHEMA_CATALOG_NAME_PRIMARY_KEY
            PRIMARY KEY (CATALOG_NAME),
      CONSTRAINT INFORMATION_SCHEMA_CATALOG_NAME_CHECK
            CHECK ((SELECT COUNT(*) FROM INFORMATION_SCHEMA_CATALOG_NAME)=1))

The INFORMATION_SCHEMA_CATALOG_NAME Base table identifies the Catalog that contains this Information Schema; the CATALOG_NAME Column contains the name of the relevant Catalog.

INFORMATION_SCHEMA.INFORMATION_SCHEMA_CATALOG_NAME_CARDINALITY¶

Definition:

CREATE ASSERTION INFORMATION_SCHEMA_CATALOG_NAME_CARDINALITY
      CHECK (1=(SELECT COUNT(*) FROM INFORMATION_SCHEMA_CATALOG_NAME))

The INFORMATION_SCHEMA_CATALOG_NAME_CARDINALITY Assertion ensures that there is exactly one row in the INFORMATION_SCHEMA_CATALOG_NAME Table.

INFORMATION_SCHEMA.CARDINAL_NUMBER¶

Definition:

CREATE DOMAIN CARDINAL_NUMBER AS INTEGER
      CONSTRAINT CARDINAL_NUMBER_DOMAIN_CHECK
            CHECK (VALUE >= 0)

The set of CARDINAL_NUMBER values includes all non-negative numbers that are less than your DBMS’s defined maximum for INTEGER values. (Note: This is probably an error in the Standard: CARDINAL_NUMBER should include all non-negative numbers that are less than or equal to your DBMS’s defined maximum for INTEGER values.)

INFORMATION_SCHEMA.CHARACTER_DATA¶

Definition:

CREATE DOMAIN CHARACTER_DATA AS CHARACTER VARYING(ML)
      CHARACTER SET SQL_TEXT

The set of CHARACTER_DATA values includes all character strings, from zero to ML characters long, that belong to the SQL_TEXT Character set. (ML is replaced by your DBMS’s defined maximum length for a variable-length character string.)

INFORMATION_SCHEMA.SQL_IDENTIFIER¶

Definition:

CREATE DOMAIN SQL_IDENTIFIER AS CHARACTER VARYING(L)
      CHARACTER SET SQL_TEXT

The set of SQL_IDENTIFIER values includes all character strings, from one to L characters long, that are valid <regular identifier>s and <delimited identifier> bodies. (L is replaced by your DBMS’s defined maximum length for a <regular identifier> and <delimited identifier> body.

INFORMATION_SCHEMA.TIME_STAMP¶

Definition:

CREATE DOMAIN TIME_STAMP AS TIMESTAMP(2) DEFAULT CURRENT_TIMESTAMP(2)

The set of TIME_STAMP values includes an SQL timestamp value.

INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS¶

This View has the following Columns:

ADMINISTRABLE_ROLE_AUTHORIZATIONS shows the role authorizations that the current user may grant to others.

• GRANTEE and ROLE_NAME uniquely identify a role authorization which the current user may use.
• IS_GRANTABLE is ‘YES’ (the role authorization is grantable).

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS.

INFORMATION_SCHEMA.APPLICABLE_ROLES¶

This View has the following Columns:

APPLICABLE_ROLES shows the role authorizations that the current user may use.

• GRANTEE and ROLE_NAME uniquely identify a role authorization that the current user may use.
• IS_GRANTABLE is either ‘YES’ (the role authorization is grantable) or ‘NO’ (the role authorization is not grantable).

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.APPLICABLE_ROLES.

INFORMATION_SCHEMA.ASSERTIONS¶

This View has the following Columns:

ASSERTIONS shows the Assertions in this Catalog that are owned by the current user.

• CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME uniquely identify an Assertion owned by the current user.
• IS_DEFERRABLE is either ‘YES’ (the Assertion is INITIALLY DEFERRED) or ‘NO’ (the Assertion is NOT DEFERRABLE).
• INITIALLY_DEFERRED is either ‘YES’ (the Assertion is INITIALLY DEFERRED) or ‘NO’ (the Assertion is INITIALLY IMMEDIATE).

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.ASSERTIONS.

INFORMATION_SCHEMA.ATTRIBUTES¶

This View has the following Columns:

ATTRIBUTES shows the Attributes of UDTs in this Catalog on wich the current user has Privileges.

• UDT_CATALOG, UDT_SCHEMA, UDT_NAME, ATTRIBUTE_NAME uniquely identify an Attribute on wich the current user has Privileges.
• ORDINAL_POSITION is the position of Attribute in its UDT. The first Attribute has ordinal position 1, the second Attribute has ordinal position 2, etc. Warning: the position can change if ALTER TYPE is used to drop an Attribute.
• COLUMN_DEFAULT shows the Attribute´s default value (presumably the DBMS will CAST the value if it must). IT will be NULL if the Attribute was defined without a DEFAULT clause (and presumably if it was defined with DEFAULT NULL). COLUMN_DEFAULT will be ‘TRUNCATED’ if the default value was too long to be stored.
• IS_NULLABLE is either ‘YES’ (the Attribute is possibly nullable) or ‘NO’ (the Attribute is known not nullable).
• DATA_TYPE shows the Attribute´s <data type>: either ‘BINARY LARGE OBJECT’, ‘BIT’, ‘BIT VARYING’, ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’, ‘INTEGER’, ‘SMALLINT’, ‘NUMERIC’, ‘DECIMAL’, ‘REAL’, ‘DOUBLE PRECISION’, ‘FLOAT’, ‘DATE’, ‘TIME’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’, ‘BOOLEAN’, ‘USER-DEFINED TYPE’ – or something else, such as an implementation-defined data type.
• CHARACTER_MAXIMUM_LENGTH shows the Attribute´s maximum lwngth in CHARACTER_SET_NAME(for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’) or in bits (for ‘BIT’, ‘BIT VARYING’, ‘BINARY LARGE OBJECT’). For other <data type>s CHARACTER_MAXIMUM_LENGTH is NULL.
• CHARACTER_OCTET_LENGTH shows the Column’s maximum length in octets (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’, ‘BIT’, ‘BIT VARYING’, ‘BINARY LARGE OBJECT’). For other <data type>s, CHARACTER_OCTET_LENGTH is NULL.
• NUMERIC_PRECISION shows the Column’s precision (for ‘INTEGER’, ‘SMALLINT’, ‘NUMERIC’, ‘DECIMAL’, ‘REAL’, ‘FLOAT’, ‘DOUBLE PRECISION’, ‘DATE’, ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’). For other <data type>s, NUMERIC_PRECISION is NULL.
• NUMERIC_PRECISION_RADIX is either 2 or 10, depending on your DBMS (for ‘INTEGER’, ‘SMALLINT’, ‘DATE’, ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’), 10 (for ‘NUMERIC’, ‘DECIMAL’), 2 (for ‘REAL’, ‘FLOAT’, ‘DOUBLE PRECISION’. For other <data type>s, NUMERIC_PRECISION_RADIX is NULL.
• NUMERIC_SCALE is either 0 (for ‘INTEGER’, ‘SMALLINT’) or shows the Column’s scale (for ‘NUMERIC’, ‘DECIMAL’). For other <data type>s, NUMERIC_SCALE is NULL.
• DATETIME_PRECISION shows the Column’s fractional-seconds precision (for ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’). For other <data type>s, DATETIME_PRECISION is NULL.
• INTERVAL_TYPE shows the Column’s interval type (for ‘INTERVAL’): either ‘YEAR’, ‘MONTH’, ‘DAY’, ‘HOUR’, ‘MINUTE’, ‘SECOND’, ‘YEAR TO MONTH’, ‘DAY TO HOUR’, ‘DAY TO MINUTE’, ‘DAY TO SECOND’, ‘HOUR TO MINUTE’, ‘HOUR TO SECOND’, ‘MINUTE TO SECOND’. For other <data type>s, INTERVAL_TYPE is NULL.
• INTERVAL_PRECISION shows the Attribute´s precision for the interval leading field (for ‘INTERVAL’). For other <data type>s, INTERVAL_PRECISION is NULL.
• CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME uniquely identify a Character set the Attribute values belong to (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’). For other <data type>s these fields are NULL.
• COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME uniquely identify the Attribute´s default Collation (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’). For other <data type>s, these fields are NULL.
• DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME uniquely identify the Domain that the Attribute depends on. If no Domain was used in the Attribute definition, these fields are NULL.
• USER_DEFINED_TYPE_CATALOG, USER_DEFINED_TYPE_SCHEMA, USER_DEFINED_TYPE_NAME uniquely identify the UDT which is the Attribute´s data type (if any); otherwise, these fields are NULL.
• CHECK_REFERENCES is NULL if the Attribute´s <data type> is not a reference type. Otherwise, CHECK_REFERENCES is ‘YES’ (reference values are checked) or ‘NO’ (reference values are not checked.)
• CHECK_ACTION is NULL if the Attribute´s <data type> is not a reference type. Otherwise, CHECK_ACTION is ‘NO ACTION’ (<refernece scope check action> is NO ACTION).

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.ATTRIBUTES.

INFORMATION_SCHEMA.CHARACTER_SETS¶

This View has the following Columns:

CHARACTER_SETS shows the Character sets in this Catalog on which the current user has Privileges.

• CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, uniquely identify a Character set that the current user may use.
• FORM_OF_USE is a zero-length string. (Note: This may be an error in the Standart. FORM_OF_USE should probably name the Form-of-use for the Character set.)
• NUMBER_OF_CHARACTERS is a tero-length string. (Note: This is an error in the Standart. NUMBER_OF_CHARACTERS must be a number and therefore should probably show the number of characters in the Character set.)
• DEFAULT_COLLATE_CATALOG, DEFAULT_COLLATE_SCHEMA, DEFAULT_COLLATE_NAME uniquely identify the Character set´s default Collation.

INFORMATION_SCHEMA.CHECK_CONSTRAINTS¶

This View has the following Columns:

CHECK_CONSTRAINTS shows the CHECK Constraints in this Catalog that are owned by the current user. (THis category includes the user´s Domain Constraints, Assertions and some TABLEConstraints.)

• CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME uniquely identify a CHECK Constraint owned by the current user.
• CHECK_CLAUSE shows the Constraint´s CHECK clause in full. It will be NULL if the CHECK clause was too long to be stored.

INFORMATION_SCHEMA.COLLATIONS¶

This View has the following Columns:

COLLATIONS shows the Collations in this Catalog on which the current user has Privileges.

• COLLATION_CATALOG, COLLATION_SCHEMA, CHARACTER_NAME uniquely identify Collation that the current user may use.
• CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME uniquely identify the Character set of the Collation.
• PAD_ATTRIBUTE is either ‘NO PAD’ (the Collation has the NO PAD attribute) or ‘SPACE’ (the Collation has the PAD SPACE attribute).
• COLLATION_TYPE, COLLATION_DEFINITION, and COLLATION_DICTIONARY are zero-length strings. To date, the Standart makes no use of these fields.

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.COLLATIONS.

INFORMATION_SCHEMA.COLUMNS¶

This View has the following Columns:

COLUMNS shows the Columns in this Catalog that the current user has Privileges on.

• TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME uniquely
• identify a Column that the current user may use.
• ORDINAL_POSITION shows the position of the Column in its Table: the first Column has ordinal position 1, the second Column has ordinal position 2, etc. Warning: the position can change if ALTER TABLE is used to add or drop a Column.
• COLUMN_DEFAULT shows the Column’s default value (presumably the DBMS will CAST the value to a character string if it must). It will be NULL if the Column was defined without a DEFAULT clause (and presumably if it was defined with DEFAULT NULL) or if the Column’s default value comes only from a Domain. COLUMN_DEFAULT will be ‘TRUNCATED’ if the default value was too long to be stored.
• IS_NULLABLE is either ‘YES’ (the Column is possibly nullable) or ‘NO’ (the Column is known not nullable).
• DATA_TYPE shows the Column’s <data type>: either ‘BINARY LARGE OBJECT’, ‘BIT’, ‘BIT VARYING’, ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’, ‘INTEGER’, ‘SMALLINT’, ‘NUMERIC’, ‘DECIMAL’, ‘REAL’, ‘DOUBLE PRECISION’, ‘FLOAT’, ‘DATE’, ‘TIME’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’, ‘BOOLEAN’, ‘USER-DEFINED TYPE’ – or something else, such as an implementation-defined data type.
• CHARACTER_MAXIMUM_LENGTH shows the Column’s maximum length in characters (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’) or in bits (for ‘BIT’, ‘BIT VARYING’, ‘BINARY LARGE OBJECT’). For other <data type>s, CHARACTER_MAXIMUM_LENGTH is NULL.
• CHARACTER_OCTET_LENGTH shows the Column’s maximum length in octets (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’, ‘BIT’, ‘BIT VARYING’, ‘BINARY LARGE OBJECT’). For other <data type>s, CHARACTER_OCTET_LENGTH is NULL.
• NUMERIC_PRECISION shows the Column’s precision (for ‘INTEGER’, ‘SMALLINT’, ‘NUMERIC’, ‘DECIMAL’, ‘REAL’, ‘FLOAT’, ‘DOUBLE PRECISION’, ‘DATE’, ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’). For other <data type>s, NUMERIC_PRECISION is NULL.
• NUMERIC_PRECISION_RADIX is either 2 or 10, depending on your DBMS (for ‘INTEGER’, ‘SMALLINT’, ‘DATE’, ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’), 10 (for ‘NUMERIC’, ‘DECIMAL’), 2 (for ‘REAL’, ‘FLOAT’, ‘DOUBLE PRECISION’. For other <data type>s, NUMERIC_PRECISION_RADIX is NULL.
• NUMERIC_SCALE is either 0 (for ‘INTEGER’, ‘SMALLINT’) or shows the Column’s scale (for ‘NUMERIC’, ‘DECIMAL’). For other <data type>s, NUMERIC_SCALE is NULL.
• DATETIME_PRECISION shows the Column’s fractional-seconds precision (for ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’). For other <data type>s, DATETIME_PRECISION is NULL.
• INTERVAL_TYPE shows the Column’s interval type (for ‘INTERVAL’): either ‘YEAR’, ‘MONTH’, ‘DAY’, ‘HOUR’, ‘MINUTE’, ‘SECOND’, ‘YEAR TO MONTH’, ‘DAY TO HOUR’, ‘DAY TO MINUTE’, ‘DAY TO SECOND’, ‘HOUR TO MINUTE’, ‘HOUR TO SECOND’, ‘MINUTE TO SECOND’. For other <data type>s, INTERVAL_TYPE is NULL.
• INTERVAL_PRECISION shows the Column’s precision for the interval leading field (for ‘INTERVAL’). For other <data type>s, INTERVAL_PRECISION is NULL.
• CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME uniquely identify the Character set the Column values belong to (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’). For other <data type>s, these fields are NULL.
• COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME uniquely identify the Column’s default Collation (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’). For other <data type>s, these fields are NULL.
• DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME uniquely identify the Domain that the Column depends on. If no Domain was used in the Column definition, these fields are NULL.
• USER_DEFINED_TYPE_CATALOG, USER_DEFINED_TYPE_SCHEMA, USER_DEFINED_TYPE_NAME uniquely identify the UDT that the Column depends on. If the Column doesn’t use a UDT, these fields are NULL.
• CHECK_REFERENCES is NULL if the Column is part of a View or if its <data type> is not a reference type. Otherwise, CHECK_REFERENCES is ‘YES’ (reference values are checked) or ‘NO’ (reference values are not checked).
• CHECK_ACTION is NULL if the Column is part of a View or if its <data type> is not a reference type. Otherwise, CHECK_ACTION is ‘RESTRICT' (<reference scope check action> is RESTRICT) or ‘SET NULL' (<reference scope check action> is SET NULL).

INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE¶

This View has the following Columns:

COLUMN_DOMAIN_USAGE shows the Columns that depend on Domains in this Catalog, where the Domains are owned by the current user.

• DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME uniquely identify a Domain owned by the current user.
• TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME uniquely identify a Column that depends on the Domain.

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE.

INFORMATION_SCHEMA.COLUMN_PRIVILEGES¶

This View has the following Columns:

COLUMN_PRIVILEGES shows the Privileges on Columns belonging to Tables in this Catalog, where the Privileges are either available to, or granted by, the current user.

• GRANTOR shows the <AuthorizationID> of the user (or perhaps the Role) who granted the Privilege.
• GRANTEE shows the <AuthorizationID> of the user (or perhaps the Role) who may use the Privilege. By definition, if GRANTOR isn’t CURRENT_USER, then GRANTEE is either CURRENT_USER or ‘PUBLIC’.
• TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME uniquely identify a Column belonging to a Table in this Catalog.
• PRIVILEGE_TYPE shows the Privilege granted: either ‘INSERT’, ‘UPDATE’, ‘REFERENCES’, ‘SELECT’.
• IS_GRANTABLE is either ‘YES’ (Privilege was granted WITH GRANT OPTION) or ‘NO’ (Privilege was not granted WITH GRANT OPTION).

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.COLUMN_PRIVILEGES.

INFORMATION_SCHEMA.COLUMN_USER_DEFINED_TYPE_USAGE¶

This View has the following Columns:

COLUMN_USER_DEFINED_TYPE_USAGE shows the Columns that depend on a UDT in this Catalog, where the UDT is owned by the current user.

• USER_DEFINED_TYPE_CATALOG, USER_DEFINED_TYPE_SCHEMA, USER_DEFINED_TYPE_NAME uniquely identify a UDT that belongs to the current user.
• TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME uniquely identify a Column that depends on the UDT.

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.COLUMN_USER_DEFINED_TYPE_USAGE.

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE¶

This View has the following Columns:

CONSTRAINT_COLUMN_USAGE shows the Columns used by any Constraint or Assertion in this Catalog, where the Constraint or Assertion is owned by the current user.

• TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME uniquely identify a Column that appears in a Constraint/Assertion.
• CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME uniquely identify the Constraint/Assertion that uses the Column. The Constraint/Assertion is owned by the current user.

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.

INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE¶

This View has the following Columns:

CONSTRAINT_TABLE_USAGE shows the Tables used by any Constraint or Assertion in this Catalog, where the Constraint or Assertion is owned by the current user.

• TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME uniquely identify a Table that appears in a Constraint/Assertion.
• CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME uniquely identify the Constraint/Assertion that uses the Table. The Constraint/Assertion is owned by the current user.

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.

INFORMATION_SCHEMA.DIRECT_SUPERTABLES¶

This View has the following Columns:

DIRECT_SUPERTABLES shows the direct subtables in this Catalog that are related to a supertable, where the subtables are owned by the current user.

• TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME uniquely identify a Table owned by the current user.
• SUPERTABLE_NAME identifies the related supertable for the Table.

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.DIRECT_SUPERTABLES.

INFORMATION_SCHEMA.DIRECT_SUPERTYPES¶

This view has the following Column:

DIRECT_SUPERTYPES shows the direct subtypes in this Catalog that are related to a supertype where subtypes are owned by the current user.

• USER_DEFINED_TYPE_CATALOG, USER_DEFINED_TYPE_SCHEMA, USER_DEFINED_TYPE_NAME uniquely identify a UDT owned by the current user.
• SUPERTYPE_CATALOG, SUPERTYPE_SCHEMA, SUPERTYPE_NAME uniquely identify the related supertype for the UDT.

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.DIRECT_SUPERTYPES.

INFORMATION_SCHEMA.DOMAINS¶

This View has the following Columns:

DOMAINS shows the Domains in this Catalog that the current user has Privileges on.

• DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME uniquely identify a Domain that the current user may use, either because of a USAGE Privilege directly on the Domain or because of a Privilege on any Column that depends on the Domain.
• DATA_TYPE shows the Domain’s <data type>: either ‘BINARY LARGE OBJECT’, ‘BIT’, ‘BIT VARYING’, ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’, ‘INTEGER’, ‘SMALLINT’, ‘NUMERIC’, ‘DECIMAL’, ‘REAL’, ‘DOUBLE PRECISION’, ‘FLOAT’, ‘DATE’, ‘TIME’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’, ‘BOOLEAN’, ‘USER-DEFINED TYPE’ – or something else, such as an implementation-defined data type.
• CHARACTER_MAXIMUM_LENGTH shows the Domain’s maximum length in characters (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’) or in bits (for ‘BIT’, ‘BIT VARYING’, ‘BINARY LARGE OBJECT’). For other <data type>s, CHARACTER_MAXIMUM_LENGTH is NULL.
• CHARACTER_OCTET_LENGTH shows the Domain’s maximum length in octets (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’, ‘BIT’, ‘BIT VARYING’, ‘BINARY LARGE OBJECT’). For other <data type>s, CHARACTER_OCTET_LENGTH is NULL.
• COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME uniquely identify the Domain’s default Collation (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’). For other <data type>s, these fields are NULL.
• CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME uniquely identify the Character set the Domain values belong to (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’). For other <data type>s, these fields are NULL.
• NUMERIC_PRECISION shows the Domain’s precision (for ‘INTEGER’, ‘SMALLINT’, ‘NUMERIC’, ‘DECIMAL’, ‘REAL’, ‘FLOAT’, ‘DOUBLE PRECISION’, ‘DATE’, ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’). For other <data type>s, NUMERIC_PRECISION is NULL.
• NUMERIC_PRECISION_RADIX is either 2 or 10, depending on your DBMS (for ‘INTEGER’, ‘SMALLINT’, ‘DATE’, ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’), 10 (for ‘NUMERIC’, ‘DECIMAL’), 2 (for ‘REAL’, ‘FLOAT’, ‘DOUBLE PRECISION’. For other <data type>s, NUMERIC_PRECISION_RADIX is NULL.
• NUMERIC_SCALE is either 0 (for ‘INTEGER’, ‘SMALLINT’) or shows the Domain’s scale (for ‘NUMERIC’, ‘DECIMAL’). For other <data type>s, NUMERIC_SCALE is NULL.
• DATETIME_PRECISION shows the Domain’s fractional-seconds precision (for ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’). For other <data type>s, DATETIME_PRECISION is NULL.
• INTERVAL_TYPE shows the Domain’s interval type (for ‘INTERVAL’): either ‘YEAR’, ‘MONTH’, ‘DAY’, ‘HOUR’, ‘MINUTE’, ‘SECOND’, ‘YEAR TO MONTH’, ‘DAY TO HOUR’, ‘DAY TO MINUTE’, ‘DAY TO SECOND’, ‘HOUR TO MINUTE’, ‘HOUR TO SECOND’, ‘MINUTE TO SECOND’. For other <data type>s, INTERVAL_TYPE is NULL.
• INTERVAL_PRECISION shows the Domain’s precision for the interval leading field (for ‘INTERVAL’). For other <data type>s, INTERVAL_PRECISION is NULL.
• DOMAIN_DEFAULT shows the Domain’s default value (presumably the DBMS will CAST the value to a character string if it must). It will be NULL if the Domain was defined without a DEFAULT clause (and presumably if it was defined with DEFAULT NULL). DOMAIN_DEFAULT will be ‘TRUNCATED’ if the default value was too long to be stored.
• USER_DEFINED_TYPE_CATALOG, USER_DEFINED_TYPE_SCHEMA, USER_DEFINED_TYPE_NAME uniquely identify the UDT that the Domain depends on. If the Domain doesn’t use a UDT, these fields are NULL.

-SCOPE_CATALOG, SCOPE_SCHEMA, SCOPE_NAME uniquely identify the -Domain´s scope Table. IF the Domain has no scope, these fields are NULL.

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.DOMAINS.

INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS¶

This View has the following Columns:

DOMAIN_CONSTRAINTS shows the Domain Constraints (i.e.: the Constraints defined on Domains in this Catalog) that are owned by the current user.

• CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME uniquely identify a Domain Constraint owned by the current user.
• DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME uniquely identify the Domain that uses the Constraint.
• IS_DEFERRABLE is either ‘YES’ (the Constraint is DEFERRABLE) or ‘NO’ (the Constraint is NOT DEFERRABLE).
• INITIALLY_DEFERRED is either ‘YES’ (the Constraint is INITIALLY DEFERRED) or ‘NO’ (the Constraint is INITIALLY IMMEDIATE).

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS.

INFORMATION_SCHEMA.DOMAIN_USER_DEFINED_TYPE_USAGE¶

This View has the following Columns:

DOMAIN_USER_DEFINED_TYPE_USAGE shows the Domains that depend on UDTs in this Catalog, where the UDT is owned by the current user.

• USER_DEFINED_TYPE_CATALOG, USER_DEFINED_TYPE_SCHEMA, USER_DEFINED_TYPE_NAME uniquely identify a UDT owned by the current user.
• DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME uniquely identify a Domain that uses the UDT.

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.DOMAIN_USER_DEFINED_TYPE_USAGE.

INFORMATION_SCHEMA.ENABLED_ROLES¶

This View has the following Columns:

ENABLED_ROLES shows the enabled roles for the current SQL-session.

• ROLE_NAME uniquely identifies an enabled Role.

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.ENABLED_ROLES.

INFORMATION_SCHEMA.KEY_COLUMN_USAGE¶

This View has the following Columns:

KEY_COLUMN_USAGE shows the Columns in this Catalog that are keys in Constraints owned by the current user.

• CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME uniquely identify a Constraint owned by the current user.
• TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME uniquely identify a Column that is a key in the Constraint.
• ORDINAL_POSITION is the position of the Column within the Constraint.

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.KEY_COLUMN_USAGE.

INFORMATION_SCHEMA.METHOD_SPECIFICATION¶

This View has the following Columns:

METHOD_SIGNATURES shows the SQL-invoked routines in this Catalog that the current user has Privileges on.

• USER_DEFINED_TYPE_CATALOG, USER_DEFINED_TYPE_SCHEMA, USER_DEFINED_TYPE_NAME, METHOD_CATALOG, METHOD_SCHEMA, METHOD_NAME uniquely identify a SQL-invoked routine that the current user may use.
• DATA_TYPE shows the routine’s result <data type>: either ‘BINARY LARGE OBJECT’, ‘BIT’, ‘BIT VARYING’, ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’, ‘INTEGER’, ‘SMALLINT’, ‘NUMERIC’, ‘DECIMAL’, ‘REAL’, ‘DOUBLE PRECISION’, ‘FLOAT’, ‘DATE’, ‘TIME’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’, ‘BOOLEAN’, ‘USER-DEFINED TYPE’ – or something else, such as an implementation-defined data type.
• CHARACTER_MAXIMUM_LENGTH shows the routine’s result maximum length in characters (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’) or in bits (for ‘BIT’, ‘BIT VARYING’, ‘BINARY LARGE OBJECT’). For other <data type>s, CHARACTER_MAXIMUM_LENGTH is NULL.
• CHARACTER_OCTET_LENGTH shows the routine’s result maximum length in octets (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’, ‘BIT’, ‘BIT VARYING’, ‘BINARY LARGE OBJECT’). For other <data type>s, CHARACTER_OCTET_LENGTH is NULL.
• COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME uniquely identify the routine’s result default Collation (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’). For other <data type>s, these fields are NULL.
• NUMERIC_PRECISION shows the routine’s result precision (for ‘INTEGER’, ‘SMALLINT’, ‘NUMERIC’, ‘DECIMAL’, ‘REAL’, ‘FLOAT’, ‘DOUBLE PRECISION’, ‘DATE’, ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’). For other <data type>s, NUMERIC_PRECISION is NULL.
• NUMERIC_PRECISION_RADIX is either 2 or 10, depending on your DBMS (for ‘INTEGER’, ‘SMALLINT’, ‘DATE’, ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’), 10 (for ‘NUMERIC’, ‘DECIMAL’), 2 (for ‘REAL’, ‘FLOAT’, ‘DOUBLE PRECISION’. For other <data type>s, NUMERIC_PRECISION_RADIX is NULL.
• NUMERIC_SCALE is either 0 (for ‘INTEGER’, ‘SMALLINT’) or shows the routine’s result scale (for ‘NUMERIC’, ‘DECIMAL’). For other <data type>s, NUMERIC_SCALE is NULL.
• DATETIME_PRECISION shows the routine’s result fractional-seconds precision (for ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’). For other <data type>s, DATETIME_PRECISION is NULL.
• INTERVAL_TYPE shows the routine’s result interval type (for ‘INTERVAL’): either ‘YEAR’, ‘MONTH’, ‘DAY’, ‘HOUR’, ‘MINUTE’, ‘SECOND’, ‘YEAR TO MONTH’, ‘DAY TO HOUR’, ‘DAY TO MINUTE’, ‘DAY TO SECOND’, ‘HOUR TO MINUTE’, ‘HOUR TO SECOND’, ‘MINUTE TO SECOND’. For other <data type>s, INTERVAL_TYPE is NULL.
• SQL_DATA_ACCESS is either ‘NONE’ (the SQL-invoked routine does not possibly contain SQL), ‘CONTAINS’ (the SQL-invoked routine possibly contains SQL), ‘READS’ (the SQL-invoked routine possibly reads SQL-data) or ‘MODIFIES’ (the SQL-invoked routine possibly modifies SQL-data).
• IS_NULL_CALL is either ‘YES’ (the SQL-invoked routine is a null-call
• function) or ‘NO’ (the SQL-invoked routine is not a null-call function).
• METHOD_SIGNATURE_CREATED shows the CURRENT_TIMESTAMP from the time
• the SQL-invoked method signature was created.
• METHOD_SIGNATURE_LAST_ALTERED shows the CURRENT_TIMESTAMP from the time the SQL-invoked method signature was last altered. (This will be the same as the METHOD_SIGNATURE_CREATED value if the routine hasn’t been altered.)

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.METHOD_SIGNATURES.

INFORMATION_SCHEMA.METHOD_SIGNATURE_PARAMETERS¶

This View has the following Columns:

METHOD_SIGNATURE_PARAMETERS shows the parameters of the SQL-invoked methods in this Catalog that the current user has Privileges on.

• USER_DEFINED_TYPE_CATALOG, USER_DEFINED_TYPE_SCHEMA, USER_DEFINED_TYPE_NAME uniquely identify the UDT that a parameter is associated with.
• METHOD_CATALOG, METHOD_SCHEMA, METHOD_NAME uniquely identify the SQL-invoked method that contains the parameter.
• ORDINAL_POSITION shows the ordinal position of the parameter in its SQL-invoked method.
• PARAMETER_MODE is either ‘IN’ (the parameter is an input parameter), ‘OUT’ (the parameter is an output parameter) or ‘INOUT’ (the parameter is both an input and an output parameter).
• IS_RESULT is either ‘YES’ (the parameter is the RESULT parameter of a type-preserving function) or ‘NO’ (the parameter is not the RESULT parameter of a type-preserving function).
• AS_LOCATOR is either ‘YES’ (the parameter is passed AS LOCATOR) or ‘NO’ (the parameter is not passed AS LOCATOR).
• PARAMETER_NAME is the name of the parameter, if it was specified when the SQL-invoked routine was created. Otherwise, PARAMETER_NAME is NULL.
• DATA_TYPE shows the parameter’s <data type>: either ‘BINARY LARGE OBJECT’, ‘BIT’, ‘BIT VARYING’, ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’, ‘INTEGER’, ‘SMALLINT’, ‘NUMERIC’, ‘DECIMAL’, ‘REAL’, ‘DOUBLE PRECISION’, ‘FLOAT’, ‘DATE’, ‘TIME’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’, ‘BOOLEAN’, ‘USER-DEFINED TYPE’ – or something else, such as an implementation-defined data type.
• CHARACTER_MAXIMUM_LENGTH shows the parameter’s maximum length in characters (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’) or in bits (for ‘BIT’, ‘BIT VARYING’, ‘BINARY LARGE OBJECT’). For other <data type>s, CHARACTER_MAXIMUM_LENGTH is NULL.
• CHARACTER_OCTET_LENGTH shows the parameter’s maximum length in octets (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’, ‘BIT’, ‘BIT VARYING’, ‘BINARY LARGE OBJECT’). For other <data type>s, CHARACTER_OCTET_LENGTH is NULL.
• COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME uniquely identify the parameter’s default Collation (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’). For other <data type>s, these fields are NULL.
• CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME uniquely identify the Character set on which the parameter’s default Collation is defined.
• NUMERIC_PRECISION shows the parameter’s precision (for ‘INTEGER’, ‘SMALLINT’, ‘NUMERIC’, ‘DECIMAL’, ‘REAL’, ‘FLOAT’, ‘DOUBLE PRECISION’, ‘DATE’, ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’). For other <data type>s, NUMERIC_PRECISION is NULL.
• NUMERIC_PRECISION_RADIX is either 2 or 10, depending on your DBMS (for ‘INTEGER’, ‘SMALLINT, ‘DATE’, ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’), 10 (for ‘NUMERIC’, ‘DECIMAL’), 2 (for ‘REAL’, ‘FLOAT’, ‘DOUBLE PRECISION’. For other <data type>s, NUMERIC_PRECISION_RADIX is NULL.
• NUMERIC_SCALE is either 0 (for ‘INTEGER’, ‘SMALLINT’) or shows the parameter’s scale (for ‘NUMERIC’, ‘DECIMAL’). For other <data type>s, NUMERIC_SCALE is NULL.
• DATETIME_PRECISION shows the parameter’s fractional-seconds precision (for ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’). For other <data type>s, DATETIME_PRECISION is NULL.
• INTERVAL_TYPE shows the parameter’s interval type (for ‘INTERVAL’); either ‘YEAR’, ‘MONTH’, ‘DAY’, ‘HOUR’, ‘MINUTE’, ‘SECOND’, ‘YEAR TO MONTH’, ‘DAY TO HOUR’, ‘DAY TO MINUTE’, ‘DAY TO SECOND’, ‘HOUR TO MINUTE’, ‘HOUR TO SECOND’, ‘MINUTE TO SECOND’. For other <data type>s, INTERVAL_TYPE is NULL.
• INTERVAL_PRECISION shows the parameter’s precision for the interval leading field (for ‘INTERVAL’). For other <data type>s, INTERVAL_PRECISION is NULL.
• USER_DEFINED_TYPE_NAME, USER_DEFINED_TYPE_SCHEMA, USER_DEFINED_TYPE_NAME uniquely identify the UDT that the parameter uses. If no UDT is used, this field is NULL.

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.METHOD_SIGNATURE_PARAMETERS.

INFORMATION_SCHEMA.PARAMETERS¶

This View has the following Columns:

PARAMETERS shows the parameters of the SQL-invoked routines in this Catalog that the current user has Privileges on.

• SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME uniquely
• identify the SQL-invoked routine that contains the parameter being described.
• ORDINAL_POSITION shows the ordinal position of the parameter in its
• SQL-invoked routine.
• PARAMETER_MODE is either ‘IN’ (the parameter is an input parameter), ‘OUT’ (the parameter is an output parameter) or ‘INOUT’ (the parameter is both an input and an output parameter).
• IS_RESULT is either ‘YES’ (the parameter is the RESULT parameter of a type-preserving function) or ‘NO’ (the parameter is not the RESULT parameter of a type-preserving function).
• AS_LOCATOR is either ‘YES’ (the parameter is passed AS LOCATOR)
• or ‘NO’ (the parameter is not passed AS LOCATOR).
• PARAMETER_NAME is the name of the parameter, if it was specified when the
• SQL-invoked routine was created. Otherwise, PARAMETER_NAME is NULL.
• DATA_TYPE shows the parameter’s <data type>: either ‘BINARY LARGE OBJECT’, ‘BIT’, ‘BIT VARYING’, ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’, ‘INTEGER’, ‘SMALLINT’, ‘NUMERIC’, ‘DECIMAL’, ‘REAL’, ‘DOUBLE PRECISION’, ‘FLOAT’, ‘DATE’, ‘TIME’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’, ‘BOOLEAN’, ‘USER-DEFINED #TYPE’ – or something else, such as an implementation-defined data type.
• CHARACTER_MAXIMUM_LENGTH shows the parameter’s maximum length in characters (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’) or in bits (for ‘BIT’, ‘BIT VARYING’, ‘BINARY LARGE OBJECT’). For other <data type>s, CHARACTER_MAXIMUM_LENGTH is NULL.
• CHARACTER_OCTET_LENGTH shows the parameter’s maximum length in octets (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’, ‘BIT’, ‘BIT VARYING’, ‘BINARY LARGE OBJECT’). For other <data type>s, CHARACTER_OCTET_LENGTH is NULL.
• COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME uniquely identify the parameter’s default Collation (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’). For other <data type>s, these fields are NULL.
• CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME uniquely identify the Character set on which the parameter’s default Collation is defined.
• NUMERIC_PRECISION shows the parameter’s precision (for ‘INTEGER’, ‘SMALLINT’, ‘NUMERIC’, ‘DECIMAL’, ‘REAL’, ‘FLOAT’, ‘DOUBLE PRECISION’, ‘DATE’, ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’). For other <data type>s, NUMERIC_PRECISION is NULL.
• NUMERIC_PRECISION_RADIX is either 2 or 10, depending on your DBMS (for ‘INTEGER’, ‘SMALLINT, ‘DATE’, ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’), 10 (for ‘NUMERIC’, ‘DECIMAL’), 2 (for ‘REAL’, ‘FLOAT’, ‘DOUBLE PRECISION’. For other <data type>s, NUMERIC_PRECISION_RADIX is NULL.
• NUMERIC_SCALE is either 0 (for ‘INTEGER’, ‘SMALLINT’) or shows the parameter’s scale (for ‘NUMERIC’, ‘DECIMAL’). For other <data type>s, NUMERIC_SCALE is NULL.
• DATETIME_PRECISION shows the parameter’s fractional-seconds precision (for ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’). For other <data type>s, DATETIME_PRECISION is NULL.
• INTERVAL_TYPE shows the parameter’s interval type (for ‘INTERVAL’): either ‘YEAR’, ‘MONTH’, ‘DAY’, ‘HOUR’, ‘MINUTE’, ‘SECOND’, ‘YEAR TO MONTH’, ‘DAY TO HOUR’, ‘DAY TO MINUTE’, ‘DAY TO SECOND’, ‘HOUR TO MINUTE’, ‘HOUR TO SECOND’, ‘MINUTE TO SECOND’. For other <data type>s, INTERVAL_TYPE is NULL.
• INTERVAL_PRECISION shows the parameter’s precision for the interval leading field (for ‘INTERVAL’). For other <data type>s, INTERVAL_PRECISION is NULL.
• USER_DEFINED_TYPE_CATALOG, USER_DEFINED_TYPE_SCHEMA, USER_DEFINED_TYPE_NAME uniquely identify the UDT that the parameter uses. If no UDT is used, this field is NULL.

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS¶

This View has the following Columns:

REFERENTIAL_CONSTRAINTS shows the FOREIGN KEY Constraints in this Catalog, where the Constraints are owned by the current user.

• CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME uniquely
• identify a FOREIGN KEY Constraint owned by the current user.
• UNIQUE_CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, UNIQUE_CONSTRAINT_NAME uniquely identify the UNIQUE Constraint or PRIMARY KEY Constraint that contains the key which this FOREIGN KEY Constraint references.
• MATCH_OPTION will be one of: ‘NONE’ (no match type defined), ‘PARTIAL’ (match type of PARTIAL defined) or ‘FULL’ (match type of FULL defined).
• UPDATE_RULE shows the referential action that will take place on UPDATE: either ‘CASCADE’ (referential action of CASCADE defined), ‘SET NULL’ (referential action of SET NULL defined), ‘SET DEFAULT’ (referential action of SET DEFAULT defined), ‘RESTRICT' (referential action of RESTRICT defined) or ‘NO ACTION’ (referential action of NO ACTION defined).
• DELETE_RULE shows the referential action that will take place on DELETE: either ‘CASCADE’ (referential action of CASCADE defined), ‘SET NULL’ (referential action of SET NULL defined), ‘SET DEFAULT’ (referential action of SET DEFAULT defined), ‘RESTRICT' (referential action of RESTRICT defined) or ‘NO ACTION’ (referential action of NO ACTION defined).

INFORMATION_SCHEMA.ROLE_COLUMN_GRANTS¶

This View has the following Columns:

ROLE_COLUMN_GRANTS shows the Privileges on Columns in this Catalog that currently enabled Roles may use.

• GRANTOR shows the <AuthorizationID> of the user (or perhaps the Role) who granted this Privilege.
• GRANTEE shows the <AuthorizationID> of the Role that may use the Privilege.
• TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME uniquely identify the Column to which the Privilege applies.
• PRIVILEGE_TYPE shows the Privilege granted: either ‘INSERT’, ‘UPDATE’, ‘REFERENCES’ or ‘SELECT’.

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.ROLE_COLUMN_GRANTS.

INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS¶

This View has the following Columns:

ROLE_ROUTINE_GRANTS shows the Privileges on SQL-invoked routines in this Catalog that currently enabled Roles may use.

• GRANTOR shows the <AuthorizationID> of the user (or perhaps the Role) who granted this EXECUTE Privilege.
• GRANTEE shows the <AuthorizationID> of the Role that may use the Privilege.
• SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME uniquely identify the SQL-invoked routine to which the Privilege applies.
• PRIVILEGE_TYPE shows the Privilege granted: ‘EXECUTE’.
• IS_GRANTABLE is either ‘YES’ (Privilege was granted WITH GRANT OPTION) or ‘NO’ (Privilege was not granted WITH GRANT OPTION).

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS.

INFORMATION_SCHEMA.ROLE_TABLE_GRANTS¶

This View has the following Columns:

ROLE_TABLE_GRANTS shows the Privileges on Tables in this Catalog that currently enabled Roles may use.

• GRANTOR shows the <AuthorizationID> of the user (or perhaps the Role) who
• granted this Privilege.
• GRANTEE shows the <AuthorizationID> of the Role that may use the
• Privilege.
• TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME uniquely identify the
• Table to which the Privilege applies.
• PRIVILEGE_TYPE shows the Privilege granted: either ‘DELETE’,
• ‘INSERT’, ‘UPDATE’, ‘REFERENCES’, ‘TRIGGER’ or ‘SELECT’.

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.ROLE_TABLE_GRANTS.

INFORMATION_SCHEMA.ROLE_USAGE_GRANTS¶

This View has the following Columns:

ROLE_USAGE_GRANTS shows the USAGE Privileges on Objects in this Catalog that currently enabled Roles may use.

• GRANTOR shows the <AuthorizationID> of the user (or perhaps the Role) who
• granted this Privilege.
• GRANTEE shows the <AuthorizationID> of the Role that may use the
• Privilege.
• OBJECT_CATALOG, OBJECT_SCHEMA, OBJECT_NAME, OBJECT_TYPE uniquely identify the Object – either a Domain, Character set, Collation or Translation – that the Role may use.
• IS_GRANTABLE is either ‘YES’ (Privilege was granted WITH GRANT OPTION) or ‘NO’ (Privilege was not granted WITH GRANT OPTION).

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.ROLE_USAGE_GRANTS.

INFORMATION_SCHEMA.ROLE_USER_DEFINED_TYPE_GRANTS¶

This View has the following Columns:

ROLE_USER_DEFINED_TYPE_GRANTS shows the Privileges on UDTs in this Catalog that currently enabled Roles may use.

• GRANTOR shows the <AuthorizationID> of the user (or perhaps the Role) who granted this Privilege.
• GRANTEE shows the <AuthorizationID> of the Role that may use the Privilege.
• USER_DEFINED_TYPE_CATALOG, USER_DEFINED_TYPE_SCHEMA, USER_DEFINED_TYPE_NAME uniquely identify the UDT the Role may use.
• PRIVILEGE_TYPE shows the Privilege granted: ‘TYPE USAGE’.
• IS_GRANTABLE is either ‘YES’ (Privilege was granted WITH GRANT OPTION) or ‘NO’ (Privilege was not granted WITH GRANT OPTION).

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.ROLE_USER_DEFINED_TYPE_GRANTS.

INFORMATION_SCHEMA.ROUTINES¶

This View has the following Columns:

ROUTINES shows the SQL-invoked routines in this Catalog that the current user has Privileges on.

• SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME uniquely identify an SQL-invoked routine that the current user has Privileges on. Among the routines in this View, you will find all the functions which your DBMS defined in advance: the SQL built-in functions, e.g.: ABS, BIT_LENGTH, CHARACTER_LENGTH, CHAR_LENGTH, LOWER, MOD, OCTET_LENGTH, POSITION, SUBSTRING, UPPER.
• ROUTINE_TYPE is either ‘PROCEDURE’ (the SQL-invoked routine is an SQL-invoked procedure), ‘FUNCTION’ (the SQL-invoked routine is an SQL-invoked function that is not an SQL-invoked method) or ‘METHOD’ (the SQL-invoked routine is an SQL-invoked method).
• MODULE_CATALOG, MODULE_SCHEMA, MODULE_NAME are all NULL.
• USER_DEFINED_TYPE_CATALOG, USER_DEFINED_TYPE_SCHEMA, USER_DEFINED_TYPE_NAME uniquely identify the UDT that this SQL-invoked routine is a method of. If the routine is not defined as a method of a UDT, these fields are NULL.
• DATA_TYPE shows the routine’s result <data type>: either ‘BINARY LARGE OBJECT’, ‘BIT’, ‘BIT VARYING’, ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’, ‘INTEGER’, ‘SMALLINT’, ‘NUMERIC’, ‘DECIMAL’, ‘REAL’, ‘DOUBLE PRECISION’, ‘FLOAT’, ‘DATE’, ‘TIME’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’, ‘BOOLEAN’, ‘USER-DEFINED TYPE’ – or something else, such as an implementation-defined data type.
• CHARACTER_MAXIMUM_LENGTH shows the routine’s result maximum length in characters (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’) or in bits (for ‘BIT’, ‘BIT VARYING’, ‘BINARY LARGE OBJECT’). For other <data type>s, CHARACTER_MAXIMUM_LENGTH is NULL.
• CHARACTER_OCTET_LENGTH shows the routine’s result maximum length in octets (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’, ‘BIT’, ‘BIT VARYING’, ‘BINARY LARGE OBJECT’). For other <data type>s, CHARACTER_OCTET_LENGTH is NULL.
• COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME uniquely identify the routine’s result default Collation (for ‘CHARACTER’, ‘CHARACTER VARYING’, ‘CHARACTER LARGE OBJECT’). For other <data type>s, these fields are NULL.
• NUMERIC_PRECISION shows the routine’s result precision (for ‘INTEGER’, ‘SMALLINT’, ‘NUMERIC’, ‘DECIMAL’, ‘REAL’, ‘FLOAT’, ‘DOUBLE PRECISION’, ‘DATE’, ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’). For other <data type>s, NUMERIC_PRECISION is NULL.
• NUMERIC_PRECISION_RADIX is either 2 or 10, depending on your DBMS (for ‘INTEGER’, ‘SMALLINT, ‘DATE’, ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’), 10 (for ‘NUMERIC’, ‘DECIMAL’), 2 (for ‘REAL’, ‘FLOAT’, ‘DOUBLE PRECISION’. For other <data type>s, NUMERIC_PRECISION_RADIX is NULL.
• NUMERIC_SCALE is either 0 (for ‘INTEGER’, ‘SMALLINT’) or shows the routine’s result scale (for ‘NUMERIC’, ‘DECIMAL’). For other <data type>s, NUMERIC_SCALE is NULL.
• DATETIME_PRECISION shows the routine’s result fractional-seconds precision (for ‘TIME’, ‘TIMESTAMP’, ‘TIME WITH TIME ZONE’, ‘TIMESTAMP WITH TIME ZONE’, ‘INTERVAL’). For other <data type>s, DATETIME_PRECISION is NULL.
• INTERVAL_TYPE shows the routine’s result interval type (for ‘INTERVAL’): either ‘YEAR’, ‘MONTH’, ‘DAY’, ‘HOUR’, ‘MINUTE’, ‘SECOND’, ‘YEAR TO MONTH’, ‘DAY TO HOUR’, ‘DAY TO MINUTE’, ‘DAY TO SECOND’, ‘HOUR TO MINUTE’, ‘HOUR TO SECOND’, ‘MINUTE TO SECOND’. For other <data type>s, INTERVAL_TYPE is NULL.
• INTERVAL_PRECISION shows the routine’s result precision for the interval leading field (for ‘INTERVAL’). For other <data type>s, INTERVAL_PRECISION is NULL.
• TYPE_USER_DEFINED_TYPE_CATALOG, TYPE_USER_DEFINED_TYPE_SCHEMA, TYPE_USER_DEFINED_TYPE_NAME uniquely identify a UDT that the routine uses. If no UDT is used, these fields are NULL.
• SCOPE_CATALOG, SCOPE_SCHEMA, SCOPE_NAME uniquely identify the routine´s scope. If the routine has no scope, these fields are NULL.
• ROUTINE_BODY is either ‘SQL’ (the SQL-invoked routine is an SQL routine) or ‘EXTERNAL’ (the SQL-invoked routine is an external routine).
• If this SQL-invoked routine is an SQL routine that is not part of an SQL-server Module definition, ROUTINE_DEFINITION shows the routine body. If the routine body was too big to be stored, or if this is not an SQL routine that doesn’t belong to an SQL-server Module definition, ROUTINE_DEFINITION is NULL.
• If this SQL-invoked routine is an external routine (i.e.: ROUTINE_BODY is ‘EXTERNAL’), EXTERNAL_NAME shows its external name. If ROUTINE_BODY is ‘SQL’, EXTERNAL_NAME is NULL.
• If this SQL-invoked routine is an external routine (i.e.: ROUTINE_BODY is ‘EXTERNAL’), EXTERNAL_LANGUAGE shows the language it’s written in: either ‘ADA’, ‘C’, ‘COBOL’, ‘FORTRAN’, ‘MUMPS’, ‘PASCAL’ or ‘PLI’. If ROUTINE_BODY is ‘SQL’, EXTERNAL_LANGUAGE is NULL.
• If this SQL-invoked routine is an external routine (i.e.: ROUTINE_BODY is ‘EXTERNAL’), PARAMETER_STYLE shows its SQL parameter passing style: either ‘SQL’ or ‘GENERAL’. If ROUTINE_BODY is ‘SQL’, PARAMETER_STYLE is NULL.
• If this SQL-invoked routine is an external routine (i.e.: ROUTINE_BODY is ‘EXTERNAL’), IS_DETERMINISTIC is either ‘YES’ (routine was defined as DETERMINISTIC) or ‘NO’ (routine was not defined as DETERMINISTIC). If ROUTINE_BODY is ‘SQL’, IS_DETERMINISTIC is NULL.
• SQL_DATA_ACCESS is either ‘NONE’ (the SQL-invoked routine does not possibly contain SQL), ‘CONTAINS’ (the SQL-invoked routine possibly contains SQL), ‘READS’ (the SQL-invoked routine possibly reads SQL-data) or ‘MODIFIES’ (the SQL-invoked routine possibly modifies SQL-data).
• If this SQL-invoked routine is an SQL routine, SQL_PATH shows the SQL-path of the Schema that contains it. Otherwise, SQL_PATH is NULL.
• SCHEMA_LEVEL_ROUTINE is either ‘YES’ (this is a Schema-level routine) or ‘NO’ (this is not a Schema-level routine).
• If this is an SQL-invoked procedure that was defined by an SQL-invoked routine containing a <maximum dynamic result sets> clause in its definition, MAX_DYNAMIC_RESULT_SETS shows that value. Otherwise, MAX_DYNAMIC_RESULT_SETS is zero.
• IS_USER_DEFINED_CAST is either ‘YES’ (the routine is a user-defined cast) or ‘NO’ (the routine is not a user-defined cast).
• IS_IMPLICIT_INVOCABLE is eiter ‘YES’ (the routine can be implicitly invoked) or ‘NO’ (the routine cannot be implicitly invoked) or NULL (the routine is not a user-defined cast).
• ROUTINE_CREATED shows the CURRENT_TIMESTAMP from the time this SQL-invoked routine was created.
• ROUTINE_LAST_ALTERED shows the CURRENT_TIMESTAMP from the time this SQL-invoked routine was last altered. (This will be the same as the ROUTINE_CREATED value if the routine hasn’t been altered.)

INFORMATION_SCHEMA.ROUTINE_COLUMN_USAGE¶

This View has the following Columns:

ROUTINE_COLUMN_USAGE shows the Columns on which SQL-invoked routines in this Catalog depend, where the Columns are owned by the current user.

• SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME uniquely identify an SQL-invoked routine.
• TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME uniquely identify a Column that is owned by the current user and upon which this routine depends.

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.ROUTINE_COLUMN_USAGE.

INFORMATION_SCHEMA.ROUTINE_PRIVILEGES¶

This View has the following Columns:

ROUTINE_PRIVILEGES shows the Privileges on SQL-invoked routines in this Catalog, where the Privileges are either available to, or granted by, the current user.

• GRANTOR shows the <AuthorizationID> of the user (or perhaps the Role) who granted the Privilege.
• GRANTEE shows the <AuthorizationID> of the user (or perhaps the Role) who may use the Privilege. By definition, if GRANTOR isn’t CURRENT_USER, then GRANTEE is either CURRENT_USER or ‘PUBLIC’.
• SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME uniquely identify the SQL-invoked routine that this Privilege applies to.
• PRIVILEGE_TYPE shows the Privilege granted: ‘EXECUTE’.
• IS_GRANTABLE is either ‘YES’ (Privilege was granted WITH GRANT OPTION) or ‘NO’ (Privilege was not granted WITH GRANT OPTION).

INFORMATION_SCHEMA.ROUTINE_TABLE_USAGE¶

This View has the following Columns:

ROUTINE_TABLE_USAGE shows the Tables on which SQL-invoked routines in this Catalog depend, where the Tables are owned by the current user.

• SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME uniquely identify an SQL-invoked routine in this Catalog.
• TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME uniquely identify a Table that is owned by the current user and upon which this routine depends.

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.ROUTINE_TABLE_USAGE.

INFORMATION_SCHEMA.SCHEMATA¶

This View has the following Columns:

SCHEMATA shows the Schemas in this Catalog that are owned by the current user.

• CATALOG_NAME, SCHEMA_NAME uniquely identify a Schema owned by the user.
• SCHEMA_OWNER shows the <AuthorizationID> of the current user. This was the Schema owner specified in the Schema definition.
• DEFAULT_CHARACTER_SET_CATALOG, DEFAULT_CHARACTER_SET_SCHEMA, DEFAULT_CHARACTER_SET_NAME uniquely identify the Character set that was specified by a DEFAULT CHARACTER SET clause in the Schema definition. This is the default Character set for the Columns and Domains belonging to this Schema.
• SQL_PATH shows the contents of the Schema definition’s PATH clause, if there was one. If PATH was omitted, or if its value was too large to be stored, SQL_PATH is NULL.

INFORMATION_SCHEMA.SQL_FEATURES¶

This View has the following Columns:

SQL_FEATURES shows all the SQL Standard-defined features and subfeatures (see Appendix B – SQL Taxonomy for a list of these), and marks the ones your DBMS supports.

• FEATURE_ID, SUB_FEATURE_ID uniquely identify an SQL feature or subfeature. If SUB_FEATURE_ID is zero, this is a feature; otherwise this is a subfeature.
• FEATURE_NAME is the name assigned to the feature by the Standard.
• SUB_FEATURE_NAME is the name assigned to the subfeature by the Standard. If SUB_FEATURE_NAME is a zero-length string, this is a feature; otherwise this is a subfeature.
• IS_SUPPORTED is either ‘YES’ (the DBMS fully supports this feature/subfeature) or ‘NO’ (the DBMS doesn’t fully support this feature/subfeature). Note: if IS_SUPPORTED is ‘YES’ for a feature, then it must also be ‘YES’ for every subfeature of that feature.
• If your DBMS has had its conformance claim for this feature/subfeature independently tested, IS_VERIFIED_BY identifies the conformance test used to verify the claim. If no such verification exists, IS_VERIFIED_BY is NULL. (Note: if IS_SUPPORTED is ‘NO’ or NULL, IS_VERIFIED_BY must be NULL.)
• FEATURE_COMMENTS is either NULL, or shows any implementer’s comments that are pertinent to this feature/subfeature.

If you want to restrict your code to Core SQL, do not reference INFORMATION_SCHEMA.SQL_FEATURES.

INFORMATION_SCHEMA.SQL_IMPLEMENTATION_INFO¶

This View has the following Columns: