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. (The detailed description of the master database can be found in Chapter 15.)
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 9-1 shows a simplified form of the Database Engine’s system information and different interfaces that you can use to access it.
NOTE This chapter shows you just an overview of the system catalog and the ways in which you can access metadata. Particular catalog views, as well as all other interfaces, that are specific for different topics (such as indices, security, etc.) are discussed in the corresponding chapters.
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.
The following section describes general interfaces. Proprietary interfaces are discussed later in the chapter.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.