SQL Information Schema Standard

This appendix describes the Information Schema views specified by the SQL2 (SQL-92) standard. These views must be supported by any database system claiming Intermediate or Full conformance to the standard; they are not required for the Entry conformance level. In practice, support for the full Information Schema views is slowly making its way into enterprise DBMS products. The views make a SQL2-compliant database self-describing. By querying them, a user can determine relevant information about all of the database objects (schemas, tables, columns, views, constraints, domains, character sets, etc.) accessible to him or her.

The following table provides information about schemas, tables, and columns:

SCHEMATA     Describes all schemas owned by the current user

TABLES       Describes all tables accessible to the current user

COLUMNS      Describes all columns of those tables owned/ accessible

             to the current user

This table provides information about views:

VIEWS                 Describes all views accessible to the current user

VIEW_TABLE_USAGE      Describes tables on which those views depend

VIEW_COLUMN_USAGE     Describes columns on which those views depend

This table provides information about constraints (unique, primary keys, foreign keys, check constraints, assertions):

TABLE_CONSTRAINTS         Describes all constraints on tables owned
                          by the user
REFERENTIAL_CONSTRAINTS   Describes all foreign key constraints owned
                          by the user
CHECK_CONSTRAINTS         Describes all check constraints owned by
                          the user
KEY_COLUMN_USAGE          Describes keys defined by the current user
ASSERTIONS                Describes all assertions owned by the
                          current user
CONSTRAINT_TABLE_USAGE    Describes all tables used by constraints
CONSTRAINT_COLUMN_USAGE   Describes columns used by constraints

This table provides information about privileges:

TABLE_PRIVILEGES        Describes privileges on tables

COLUMN_PRIVILEGES    Describes privileges on columns

USAGE_PRIVILEGES       Describes privileges on other database objects

This table provides information about domains:

DOMAINS                Describes domains accessible to the user

DOMAIN_CONSTRAINTS     Describes constraints that define those domains

DOMAIN_COLUMN_USAGE    Describes columns based on those domains

This table provides information about character sets:

CHARACTER_SETS         Describes character sets

COLLATIONS             Describes collating sequences

TRANSLATIONS           Describes translations between character sets

And here’s information about supported programming languages:

SQL_LANGUAGES          Describes supported languages and SQL APIs

The specific contents of each Information Schema view are described on the following pages.

1. The schemata View

The SCHEMATA view contains one row for each schema that is owned by the current user. Its structure is shown in the following table:

Column Name     Data Type       Description
CATALOG_NAME     VARCHAR(len)    Name of catalog containing
                                 this schema
SCHEMA_NAME      VARCHAR(len)    Name of schema described by
                                 this row
SCHEMA_OWNER     VARCHAR(len)    Name of schema’s creator

2. The tables View

The TABLES view contains one row for each table defined in the current catalog that is accessible to the current user. Its structure is shown in the following table:

3. The columns View

The COLUMNS view contains one row for each column of each table defined in the current catalog that is accessible to the current user. Its structure is shown in the following table:

Column Name         Data Type       Description

TABLE_CATALOG       VARCHAR(len)      Name of catalog containing

                                      the table definition containing this column

4. The views View

The VIEWS view contains one row for each view defined in the current catalog that is accessible to the current user. Its structure is shown in the following table:

5. The VIEW_TABLE_USAGE View

The VIEW_TABLE_USAGE view contains one row for each table on which a view defined in the current catalog by the current user depends. Its structure is shown in the following table:

6. The VIEW_COLUMN_USAGE View

The VIEW_COLUMN_USAGE view contains one row for each column on which a view defined in the current catalog by the current user depends. Its structure is shown in the following table:

7. The table_constraints View

The TABLE_CONSTRAINTS view contains one row for each table constraint defined for tables in the current catalog owned by the current user. Its structure is shown in the following table:

8. The REFERENTIAL_CONSTRAINTS View

The REFERENTIAL_CONSTRAINTS view contains one row for each referential constraint (foreign key/primary key relationship) defined for tables in the current catalog owned by the current user. Its structure is shown in the following table:

9. The check_constraints View

The CHECK_CONSTRAINTS view contains one row for each check constraint (check constraint, domain check constraint, or assertion definition) defined in the current catalog that is owned by the current user. Its structure is shown in the following table:

10. The key_column_usage View

The KEY_COLUMN_USAGE view contains one row for each column that participates in a key defined in the current catalog by the current user. Its structure is shown in the following table:

11. The assertions View

The ASSERTIONS view contains one row for each assertion defined in the current catalog that is owned by the current user. Its structure is shown in the following table:

12. The CONSTRAINT_TABLE_USAGE View

The CONSTRAINT_TABLE_USAGE view contains one row for each table used by a constraint (referential constraint, unique constraint, check constraint, or assertion) defined in the current catalog by the current user. Its structure is shown in the following table:

13. The constraint_column_usage View

The CONSTRAINT_COLUMN_USAGE view contains one row for each column used by a constraint (referential constraint, unique constraint, check constraint, or assertion) defined in the current catalog by the current user. Its structure is shown in the following table:

14. The table_privileges View

The TABLE_PRIVILEGES view contains one row for each privilege on tables defined in the current catalog that has been granted to the current user, granted to all users, or granted by the current user. Its structure is shown in the following table:

15. The column_privileges View

The COLUMN_PRIVILEGES view contains one row for each privilege on columns defined in the current catalog that has been granted to the current user, granted to all users, or granted by the current user. Its structure is shown in the following table:

16. The usage_privileges View

The USAGE_PRIVILEGES view contains one row for each privilege on objects defined in the current catalog that has been granted to the current user, granted to all users, or granted by the current user. Its structure is shown in the following table:

17. The domains View

The DOMAINS view contains one row for each domain defined in the current catalog that is accessible to the current user. Its structure is shown in the following table:

18. The domain_constraints View

The DOMAIN_CONSTRAINTS view contains one row for each domain constraint for a domain defined in the current catalog that is accessible to the current user. Its structure is shown in the following table:

19. The DOMAIN_COLUMN_USAGE View

The DOMAIN_COLUMN_USAGE view contains one row for each column used by a domain defined in the current catalog by the current user. Its structure is shown in the following table:

20. The CHARACTER_SETS View

The CHARACTER_SETS view contains one row for each character set defined in the current catalog that is accessible to the current user. Its structure is shown in the following table:

21. The collations View

The COLLATIONS view contains one row for each collation (sorting sequence) defined in the current catalog that is accessible to the current user. Its structure is shown in the following table:

22. The translations View

The TRANSLATIONS view contains one row for each translation (conversion from one character set to another) defined in the current catalog that is accessible to the current user. Its structure is shown in the following table:

23. The SQL_LANGUAGES View

The SQL_LANGUAGES view contains one row for each ANSI-standard language supported by this SQL implementation. Its structure is shown in the following table:

Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.

Leave a Reply

Your email address will not be published. Required fields are marked *