What Is the System Catalog?

The system catalog is a collection of special tables in a database that are owned, created, and maintained by the DBMS itself. These system tables contain data that describes the structure of the database. The tables in the system catalog are automatically created when the database is created. They are usually gathered under a special system user-id with a name like SYSTEM, SYSIBM, MASTER, or DBA.

The DBMS constantly refers to the data in the system catalog while processing SQL statements. For example, to process a two-table SELECT statement, the DBMS must:

Verify that the two named tables actually exist.
Ensure that the user has permission to access them.
Check whether the columns referenced in the query exist.
Resolve any unqualified column names to one of the tables.
Determine the data type of each column.

By storing structural information in system tables, the DBMS can use its own access methods and logic to rapidly and efficiently retrieve the information it needs to perform these tasks.

If the system tables were used only internally to the DBMS, they would be of little interest to database users. However, the DBMS generally makes the system tables available for user access as well. If the system tables themselves are not made available, the DBMS generally provides views based on the system tables that offer a set of user-retrievable catalog information. User queries against the system catalogs or views are almost always permitted by personal computer and workgroup class databases. These queries are also supported by mainframe and enterprise DBMS products, but the database administrator may restrict system catalog access to provide an additional measure of database security. By querying the system catalogs, you can discover information about the structure of a database, even if you have never used it before.

User access to the system catalog is read-only. The DBMS prevents users from directly updating or modifying the system tables because such modifications would destroy the integrity of the database. Instead, the DBMS itself takes care of inserting, deleting, and updating rows of the system tables as it modifies the structure of a database. Data Definition Language (DDL) statements such as CREATE, ALTER, DROP, GRANT, and REVOKE produce changes in the system tables as a byproduct of their actions. In some DBMS products, even DML statements that modify the database, such as INSERT and DELETE, may produce changes in the system tables, which keep track of how many rows are in each table.

1. The Catalog and Query Tools

One of the most important benefits of the system catalog is that it makes possible user-friendly query tools, as shown in Figure 16-1. The objective of such tools is to let users simply and transparently access the database without learning the SQL language. Typically, a tool leads the user through a series of steps like this one:

The user gives a name and password for database access.
The query tool displays a list of available tables.
The user chooses a table, causing the query tool to display a list of the columns it contains.
The user chooses columns of interest, perhaps by clicking their names as they appear on a PC screen.
The user chooses columns from other tables or restricts the data to be retrieved with a search condition.
The query tool retrieves the requested data and displays it on the user’s screen.

A general-purpose query tool like the one in Figure 16-1 will be used by many different users, and it will be used to access many different databases. The tool cannot possibly know in advance the structure of the database that it will access during any given session. Thus, it must be able to dynamically learn about the tables and columns of a database. The tool uses system catalog queries for this purpose.

2. The Catalog and the ANSI/ISO Standard

The ANSI/ISO SQL1 standard did not specify the structure and contents of the system catalog. In fact, the SQL1 standard does not require a system catalog at all. However, all of the major SQL-based DBMS products provide a system catalog in one form or another. The structure of the catalog and the tables it contains vary considerably from one brand of DBMS to another.

Because of the growing importance of general-purpose database tools that must access the system catalog, the SQL2 standard includes a specification of a set of views that provide standardized access to information typically found in the system catalog. A DBMS system that conforms to the SQL2 standard must support these views, which are collectively called the INFORMATION_SCHEMA. Because this schema is more complex than the actual system catalogs used by commercial DBMS products, and is only slowly being supported, it is described in a separate section near the end of this chapter titled “The SQL2 Information Schema.”

3. Catalog Contents

Each table in the system catalog contains information about a single kind of structural element in the database. Although the details vary, almost all commercial SQL products include system tables that describe each of these five entities:

Tables. The catalog describes each table in the database, identifying its table name, its owner, the number of columns it contains, its size, and so on.
Columns. The catalog describes each column in the database, giving the column’s name, the table to which it belongs, its data type, its size, whether NULLs are allowed, and so on.
Users. The catalog describes each authorized database user, including the user’s name, an encrypted form of the user’s password, and other data.
Views. The catalog describes each view defined in the database, including its name, the name of its owner, the query that defines the view, and so on.
Privileges. The catalog describes each set of privileges granted in the database, including the names of the grantor and grantee, the privileges granted, the object on which the privileges have been granted, and so on.

Table 16-1 shows the names of the system tables that provide this information in each of the major SQL-based DBMS products. The remainder of this chapter describes some typical system tables in more detail and gives examples of system catalog access. Because of the wide variations in system catalogs among DBMS brands, a complete description of the system catalogs and complete examples for all of the major DBMS brands is beyond the scope of this book. With the information provided here, you should be able to consult the system documentation for your DBMS brand and construct the appropriate system catalog queries.