As already stated, the following interfaces are general interfaces:
- Catalog views
- DMVs and DMFs
- Information schema
1. Catalog Views
Catalog views are the most general interface to the metadata and provide the most efficient way to obtain customized forms of this information (see Examples 9.1 through 9.3).
Catalog views belong to the sys schema, so you have to use the schema name when you access one of the objects. This section describes the three most important catalog views:
- objects
- columns
- database_principals
NOTE You can find the description of other views either in different chapters of this book or in Microsoft Docs.
The sys.objects catalog view contains a row for each user-defined object in relation to the user’s schema. There are two other catalog views that show similar information: sys.system_objects and sys.all_objects. The former contains a row for each system object, while the latter shows the union of all schema-scoped user-defined objects and system objects. (All three catalog views have the same structure.) Table 9-1 lists and describes the most important columns of the sys.objects catalog view.
The sys.columns catalog view contains a row for each column of an object that has columns, such as tables and views. Table 9-2 lists and describes the most important columns of the sys.columns catalog view.
The sys.database_principals catalog view contains a row for each security principal (that is, user, group, or role in a database). Table 9-3 lists and describes the most important columns of the sys.database_principals catalog view. (For a detailed discussion of principal types, see Chapter 12.)
2. Querying Catalog Views
As already stated in this chapter, all system tables have the same structure as base tables. Because system tables cannot be referenced directly, you have to query catalog views, which correspond to particular system tables. (Views can be queried in the same way as user tables.) Examples 9.1 through 9.3 use existing catalog views to demonstrate how information concerning database objects can be queried.
Example 9.1
Get the table ID, user ID, and table type of the employee table:
USE sample;
SELECT object_id/ principal_id/ type
FROM sys.objects
WHERE name = ’employee’;
The result is
The object_id column of the sys.objects catalog view displays the unique ID number for the corresponding database object. The NULL value in the principal_id column indicates that the object’s owner is the same as the owner of the schema. U in the type column stands for the user (table).
Example 9.2
Get the names of all tables of the sample database that contain the project_no column:
USE sample;
SELECT sys.objects.name
FROM sys.objects INNER JOIN sys.columns
ON sys.objects.object_id = sys.columns.object_id
WHERE sys.objects.type = ‘U’
AND sys.columns.name = ‘project_no’;
The result is
Example 9.3
Who is the owner of the employee table?
SELECT sys.database_principals.name
FROM sys.database_principals INNER JOIN sys.objects
ON sys.database_principals.principal_id = sys.objects.schema_id
WHERE sys.objects.name = ’employee’
AND sys.objects.type = ‘U’;
The result is
3. Dynamic Management Views and Functions
Dynamic management views (DMVs) and functions (DMFs) return server state information that can be used to observe active processes and therefore to tune system performance or to monitor the actual system state. In contrast to catalog views, the DMVs and DMFs are based on internal structures of the system.
NOTE The main difference between catalog views and DMVs is in their application: catalog views display the static information about metadata, while DMVs (and DMFs) are used to access dynamic properties of the system. In other words, you use DMVs to get insightful information about the database, individual queries, or an individual user.
DMVs and DMFs belong to the sys schema and their names start with the prefix dm_ followed by a text string that indicates the category to which the particular DMV or DMF belongs.
The following list identifies and describes some of these categories:
- dm_db_* Contains information about databases and their objects
- dm_tran_* Contains information in relation to transactions
- dm_io_* Contains information about I/O activities
- dm_exec_* Contains information related to the execution of user code
NOTE The functionality and application areas of DMVs and DMFs are identical. (Only the syntax is slightly different.) For this reason, I will use “DMV” as a common name for both of them.
3.1. Information Schema
The information schema consists of read-only views that provide information about all tables, views, and columns of the Database Engine to which you have access. In contrast to the system catalog that manages the metadata applied to the system as a whole, the information schema primarily manages the environment of a database.
NOTE The information schema was originally introduced in the SQL92 standard. The Database Engine provides information schema views so that applications developed on other database systems can obtain its system catalog without having to use it directly. These standard views use different terminology, so when you interpret the column names, be aware that catalog is a synonym for database and that domain is a synonym for user-defined data type.
The following sections provide a description of the most important information schema views.
3.2. information_schema.tables
The information_schema.tables view contains one row for each table in the current database to which the user has access. The view retrieves the information from the system catalog using the sys.objects catalog view. Table 9-4 lists and describes the four columns of this view.
3.3. information_schema.columns
The information_schema.columns view contains one row for each column in the current database accessible by the user. The view retrieves the information from the sys.columns and sys .objects catalog views. Table 9-5 lists and describes the six most important columns of this view.
Example 9.4 shows the use of the information_schema.tables and information_schema.columns views. The query is equivalent to the query from Example 9.2.
Example 9.4
Get the names of all tables of the sample database that contain the project_no column:
USE sample;
SELECT t.table_name
FROM information_schema.tables t INNER JOIN
information_schema.columns c
ON t.table_name = c.table_name
AND c.column_name = ‘project_no’;
4. Proprietary Interfaces
The previous section describes the use of the general interfaces for accessing system base tables. (As a reminder, “general” means that all relational database systems support such interfaces.) You can also retrieve system information using one of the following proprietary mechanisms of the Database Engine:
- System stored procedures
- System functions
- Property functions
The following sections describe these interfaces.
4.1. System Stored Procedures
System stored procedures are used to provide many administrative and end-user tasks, such as renaming database objects, identifying users, and monitoring authorization and resources.
Almost all existing system stored procedures access system base tables to retrieve and modify system information.
NOTE The most important property of system stored procedures is that they can be used for easy and reliable modification of system base tables.
This section describes two system stored procedures: sp_help and sp_configure. Depending on the subject matter of the chapters, certain system stored procedures were discussed in previous chapters, and additional procedures will be discussed in later chapters of the book.
The sp_help system stored procedure displays information about one or more database objects. The name of any database object or data type can be used as a parameter of this procedure. If sp_help is executed without any parameter, information on all database objects of the current database will be displayed.
The sp_configure system stored procedure displays or changes global configuration settings for the current server.
Example 9.5 shows the use of the sp_configure system stored procedure.
Example 9.5
USE sample;
EXEC sp_configure ‘show advanced options’ , 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure ‘fill factor’, 100;
RECONFIGURE WITH OVERRIDE;
Generally, you do not have access to advanced configuration options of the Database Engine. For this reason, the first EXECUTE statement in Example 9.5 tells the system to allow changes of advanced options. With the next statement, RECONFIGURE WITH OVERRIDE, these changes will be installed. Now it is possible to change any of the existing advanced options. Example 9.5 changes the fill factor to 100 and installs this change. (Fill factor specifies the storage percentage for index pages and will be described in detail in Chapter 10.)
4.2. System Functions
System functions are described in Chapter 5. Some of them can be used to access system base tables. Example 9.6 shows two SELECT statements that retrieve the same information using different interfaces.
Example 9.6
USE sample;
SELECT object_id
FROM sys.objects
WHERE name = ’employee’;
SELECT object_id(’employee’);
The second SELECT statement in Example 9.6 uses the system function object_id to retrieve the ID of the employee table. (This information can be stored in a variable and used when calling a command, or a system stored procedure, with the object’s ID as a parameter.)
The following system functions, among others, access system base tables. The names of these functions are self-explanatory.
- OBJECT_ID(object_name)
- OBJECT_NAME(object_id)
- USER_ID([user_name])
- USER_NAME([user_id])
- DB_ID([db_name])
- DB_NAME([db_id])
4.3. Property Functions
Property functions return properties of database objects, data types, or files. Generally, property functions can return more information than system functions can return, because property functions support dozens of properties (as parameters), which you can specify explicitly.
Almost all property functions return one of the following three values: 0, 1, or NULL. If the value is 0, the object does not have the specified property. If the value is 1, the object has the specified property. Similarly, the value NULL specifies that the existence of the specified property for the object is unknown to the system.
The Database Engine supports, among others, the following property functions:
- OBJECTPROPERTY(id, property)
- COLUMNPROPERTY(id, column, property)
- FILEPROPERTY(filename, property)
- TYPEPROPERTY(type, property)
The OBJECTPROPERTY function returns information about objects in the current database (see Exercise E.9.2). The COLUMNPROPERTY function returns information about a column or procedure parameter. The FILEPROPERTY function returns the specified filename and property value for a given filename and property name. The TYPEPROPERTY function returns information about a data type. (The description of existing properties for each property function can be found in Microsoft Docs.)
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.