System Stored Procedures


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.

image from book
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.




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net