The previous section describes the use of the general interfaces for accessing system base tables. 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.
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.
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 tutorial.
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.
USE sample;
EXEC sp_configure 'show advanced options' , 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'fill factor', 100;
RECONFIGURE WITH OVERRIDE;Code language: PHP (php)Generally, you do not have access to advanced configuration options of SQL Server. 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 the next chapter.)
System Functions
System functions are described in Chapter “Data Definition Language“. 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.
USE sample;
SELECT object_id
FROM sys.objects
WHERE name = 'employee';
SELECT object_id('employee');Code language: PHP (php)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])
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 Books Online.)
