To keep track of all objects, their relationships, etc., the RDBMS use the same technique they are advocating — a set of relational tables and views. This approach was first defined in the SQL92 standard (ISO/IEC 9075-2:199x) and was implemented across all major RDBMS — to a certain degree.
In the SQL Standard a CATALOG is a collection of schemas that contains, among other things, INFORMATION_SCHEMA. It comprises the tables and/or views that provide all the information about all the other objects and records defined in the database: schemas, tables, privileges, and so on. The main idea is to provide both users and the RDBMS with a consistent standardized way of accessing metadata (the data about data: table definitions, user-defined types, etc.) as well as some system information. By definition, the INFORMATION_SCHEMA tables and views cannot be updated directly, though some vendors allow this (e.g., IBM DB2 UDB).
Table 13-1 shows INFORMATION_SCHEMA views as specified in SQL99 standards.
INFORMATION_SCHEMA view |
Description |
Implemented in RDBMS |
---|---|---|
ASSERTIONS |
Lists all the assertions created in the database; not implemented by any of the leading vendors. |
None |
CHARACTER_SETS |
Describes character set definitions accessible to the user; one row per set. |
None |
CHECK_CONSTRAINTS |
Describes check constraints on the tables accessible by the user; one row per constraint. |
SQL Server 2000 |
COLLATIONS |
Describes collations accessible to the user; one row per collation. |
None |
COLUMNS |
Describes columns accessible to the current SQL Server 2000 user for every table in the database; one row per column. |
IBM DB2 UDB, |
COLUMN_DOMAIN_USAGE |
Contains information about the objects for which the current user has permissions. |
SQL Server 2000 |
COLUMN_PRIVILEGES |
Describes privileges on the column level granted to the user; one row per privilege per column. |
SQL Server 2000 |
CONSTRAINT_COLUMN_USAGE |
Describes columns referenced in every constraint; one row per column. |
SQL Server 2000 |
CONSTRAINT_TABLE_USAGE |
Describes tables referenced in every constraint; one row per table per constraint. |
SQL Server 2000 |
DOMAINS |
Describes domains accessible to the user(data type, restrictions, etc.); one row per domain. |
SQL Server 2000 |
INFORMATION_SCHEMA_CATALOG_NAME |
Name of the database for the user; one row per name; not implemented by any of the leading vendors. |
None |
DOMAIN_CONSTRAINTS |
Describes domain constraints accessible to the user; one row per domain constraint. |
SQL Server 2000 |
KEY_COLUMN_USAGE |
Describes columns used in the key-based constraints(primary key, foreign key, unique, etc); one row per constraint. |
SQL Server 2000 |
REFERENTIAL_CONSTRAINTS |
Describes foreign key constrains for the tables accessible to the user; one row per constraint. |
SQL Server 2000 |
SCHEMATA |
Describes schemas contained in the database; SQL Server 2000 one row per schema. |
IBM DB2 UDB, |
SQL_LANGUAGES |
Contains information about languages supported by the RDBMS (i.e., C, FORTRAN, PL/I etc.). |
None |
TABLES |
Describes every table accessible to the user; SQL Server 2000 one row per table/view. |
IBM DB2 UDB, |
TABLE_CONSTRAINTS |
Describes constraints declared for the table(primary key, check constraint etc.); one row per constraint. |
SQL Server 2000 |
TABLE_PRIVILEGES |
Describes all the privileges granted to the user; one row per privilege. |
SQL Server 2000 |
TRANSLATIONS |
Translation definitions accessible to the user. |
None |
USAGE_PRIVILEGES |
Contains information about privileges granted to a user; one row per privilege. |
None |
VIEWS |
Describes every view accessible to the user; SQL Server 2000 one row per view. |
IBM DB2 UDB, |
VIEW_COLUMN_USAGE |
Describes columns referenced by the views accessible to the user; one row per column. |
SQL Server 2000 |
VIEW_TABLE_USAGE |
Describes tables referenced by views accessible to the user; one row per table. |
SQL Server 2000 |
Similar functionality has been implemented by the RDBMS vendors in views with different names or in a different way. Please refer to the particular RDBMS section of this chapter for more information. In Table 13-1 the column "Implemented in RDBMS" refers to the actual syntax of the view, that is, its name; some vendors choose to use different names and/or add their own views and tables to the System Catalog.