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.