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:
- sys.objects
- sys.columns
- sys.database_principals
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.
Table 9-1 – Selected Columns of the sys.objects Catalog View
| Column Name | escription |
|---|---|
| name | Object name |
| object_id | Object identification number, unique within a database |
| schema_id | ID of the schema in which the object is contained |
| type | Object type |
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.
Table 9-2 – Selected Columns of the sys.columns Catalog View
| Column Name | Description |
|---|---|
| object_id | ID of the object to which this column belongs |
| name | Column name |
| column_id | ID of the column (unique within the object) |
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.objects catalog view.
Table 9-3 – Selected Columns of the sys.database_principals Catalog View
| Column Name | Description |
|---|---|
| name | Name of principal |
| principal_id | ID of principal (unique within the database) |
| type | Principal type |
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. Examples 9.1 through 9.3 use existing catalog views to demonstrate how information concerning database objects can be queried.
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';Code language: PHP (php)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).
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';Code language: PHP (php)The result is
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';Code language: JavaScript (javascript)The result is
