Understanding the SQL Server System Catalog

The system catalog consists of tables describing the structure of objects such as databases, base tables, views, and indices. (These tables are called system base tables.) The Database Engine frequently accesses the system catalog for information that is essential for the system to function properly.

The Database Engine distinguishes the system base tables of the master database from those of a particular user-defined database. System tables of the master database belong to the system catalog, while system tables of a particular database form the database catalog. Therefore, system base tables occur only once in the entire system (if they belong exclusively to the master database), while others occur once in each database, including the master database.

In all relational database systems, system base tables have the same logical structure as base tables. As a result, the same Transact-SQL statements used to retrieve information in the base tables can also be used to retrieve information in system base tables.

NOTE

The system base tables cannot be accessed directly: you have to use existing interfaces to query the information from the system catalog.

There are several different interfaces that you can use to access the information in the system base tables:

• Catalog views   Present the primary interface to the metadata stored in system base tables. (Metadata is data that describes the attributes of objects in a database system.)
• Dynamic management views (DMVs) and functions (DMFs)   Generally used to observe active processes and the contents of the memory.
• Information schema   A standardized solution for the access of metadata that gives you a general interface not only for the Database Engine, but for all existing relational database systems (assuming that the system supports the information schema).
• System and property functions   Allow you to retrieve system information. The difference between these two function types is mainly in their structure. Also, property functions can return more information than system functions.
• System stored procedures   Some system stored procedures can be used to access and modify the content of the system base tables.

Figure 1 shows a simplified form of the Database Engine’s system information and different interfaces that you can use to access it.

Figure 1. Graphical presentation of different interfaces for the system catalog

These interfaces can be grouped in two groups: general interfaces (catalog views, DMVs and DMFs, and the information schema), and proprietary interfaces in relation to the Database Engine (system stored procedures and system and property functions).

NOTE

“General” means that all relational database systems support such interfaces, but use different terminology. For instance, in Oracle’s terminology, catalog views and DMVs are called “data dictionary views” and “V$ views,” respectively.