Microsoft delivers a vast set of stored procedures as a part of SQL Server. They are designed to cover all aspects of system administration. Before Microsoft SQL Server 6.0, you had to use scripts from ISQL (a command line utility like SQLCMD) to control the server and the databases. Although administrators today customarily use Management Studio, system stored procedures are still very important, since Management Studio uses the same system stored procedures, through SQL-SMO, behind the scenes.
Note | SQL-SMO stands for SQL Server Management Objects. It is a collection of objects designed to manage tbe SQL Server environment. You can use it to create your own applications for managing SQL Server or to automate repetitive tasks. |
System stored procedures are stored in the system databases (master and sometimes msdb) and they have the prefix sp_. This prefix is more than just a convention. It signals to the server that the stored procedure is located in the master database and that it should be accessible from all databases without the user needing to insert the database name as a prefix to fully qualify the name of the procedure:
Exec sp_who -- instead of exec master.sys.sp_who
It also signals to the server that the stored procedure should be executed in the context of the current database. For example, the script shown in Figure 7-1 will return information about the current database, and not the master.
Figure 7-1: The system procedure works in the context of the current database.
Note | There is a small behavioral inconsistency between stored procedures in the master database and the msdb database. Stored procedures in the msdb database are delivered with SQL Server, but they must be referenced with the database name (for example, msdb.dbo.sp_update_job), and they do not work in the context of the current database. In this respect, you can understand them as "system-supplied stored procedures" rather than as "system stored procedures" as we have defined them. |