Chapter 13: The System Catalog and INFORMATION_SCHEMA

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.

SQL System Catalogs

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.

Table 13-1: SQL Standard INFORMATION_SCHEMA Views

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.