System-Stored Procedures

The system-stored procedures, created when SQL Server is installed, are integral to the administration of SQL Server. The purpose of the system-stored procedures is to shield the administrator from having to query or edit the system and database catalog tables directly. Much of SQL Server administration can now be done through the Enterprise Manager, but it pays to be familiar with the system-stored procedures as well. For instance, to add a login in Enterprise Manager, right-click Logins, select New Login from the pop-up window, and fill in the appropriate information. That's fine for one new login, but what if you want to add 200 logins? In that case, write a script that calls the system-stored procedure sp _ addlogin and provides the appropriate values ( name , password, and so on) for the new logins.

It would be impractical to list all the system-stored procedures. A quick check of the master database lists almost 1,000 procedures. Books Online and MSDN are excellent resources for information on the system-stored procedures. Another way to familiarize yourself with the procedures available is to "walk through" the list in the Query Analyzer's object browser. I often find this is enough to jog my memory when I can't quite remember which procedure I need for a particular task. If you need more info , drag the procedure into the query window, and press Shift+F1. This will open the Books Online documentation for the procedure.

Special Characteristics of System-Stored Procedures

Generally, system-stored procedures share these attributes:

They are stored in the master database.
Their name is prefixed with sp_ .
The owner of the procedure is dbo (database owner).
They are global in scope. This means they can be executed from any database and will run in the context of that database.

Just as you should never directly modify system tables, the same holds true for system-stored procedures. If you want to change a system-stored procedure, copy the procedure definition from the text column of the syscomments table or from the procedures properties in Enterprise Manager; you can then paste it into a new stored procedure and modify it at will. If you mess it up, the original is still available.

NOTE

Be careful not to alter system-stored procedures that modify system tables. These procedures are complex and have many dependencies. If you trash your system and call Microsoft for support, don't be surprised if they tell you, "We don't support the direct modification of system objects. Have a nice day."