Database Reference
In-Depth Information
SYSTEM CATALOG
Information about tables in the database is kept in the system catalog (or the catalog). The catalog is main-
tained automatically by the DBMS. When a user adds a new table, changes the structure of an existing table,
or deletes a table, the DBMS updates the catalog to reflect these changes.
This section describes the types of items kept in the catalog and the way in which you can query it to
determine information about the database structure. (This description represents the way catalogs are used
in a typical SQL implementation.) Although catalogs in individual relational DBMSs will vary from the exam-
ples shown here, the general ideas apply to most relational systems.
The catalog you will consider contains two tables: Systables (information about the tables known to SQL)
and Syscolumns (information about the columns or fields within these tables). An actual catalog contains
other tables as well, such as Sysindexes (information about the indexes that are defined on these tables) and
Sysviews (information about the views that have been created). Although these tables have many fields, only
a few are of concern here.
As shown in Figure 4-26, the Systables table contains the Name, Creator, and Colcount fields. The Name
field identifies the name of a table, the Creator field identifies the person or group that created the table, and
the Colcount field contains the number of fields in the table being described. If, for example, the user named
Brown created the Rep table and the Rep table has nine fields, there would be a row in the Systables table in
which the Name is Rep, the Creator is Brown, and the Colcount is 9. Similar rows would exist for all tables
known to the system.
140
Systables
Name
Creator
Colcount
Customer
Brown
10
Part
Brown
6
Orders
Brown
3
OrderLine
Brown
4
Rep
Brown
9
FIGURE 4-26
Systables table
The Syscolumns table contains the Colname, Tbname, and Coltype fields, as shown in Figure 4-27. The
Colname field identifies the name of a field in one of the tables. The table in which the field is found is stored
in the Tbname field, and the data type for the field is found in the Coltype field. There is a row in the Sys-
columns table for each field in the Rep table, for example. On each of these rows, Tbname is Rep. On one
of these rows, Colname is RepNum and Coltype is CHAR(2). On another row, Colname is LastName
and Coltype is CHAR(15).
