Chapter 5: System Stored Procedures


Overview

We could write a whole book only on system stored procedures. In fact someone already has; it's written by the Microsoft SQL Server team and is available under the System Stored Procedures section, which is included under the Transact-SQL Reference section of SQL Server Books Online. It covers over 250 system stored procedures that are documented in good detail. If you need information about a particular system stored procedure, this is the best place to begin with.

In this chapter, we will not repeat the material covered by SQL Server Books Online. Rather, we will be giving you an overview of system stored procedures, and their implementation by Microsoft. We will also look at some of the undocumented functionalities provided with SQL Server, which Microsoft uses to make their system stored procedures work. Along the way, we'll be giving you tips and tricks on when to use (and on avoiding) their quite useful, but obviously ‘unsupported’, undocumented functionalities.

Later, we will give our list of top 25 (in no particular order) documented system stored procedures. That said, if we get 1000 DBAs in a room and ask them to list their top 25 system stored procedures, we'll get 1000 (alright, maybe 999) different lists. So please don't quibble over our non-definitive list.

The actual number of system stored procedures can be found out by doing a query on the SYSOBJECTS table where type equals P (a stored procedure) or type equals X (an extended stored procedure). This would yield us a figure of over 1100 in SQL Server 2000 Service Pack 2. Using the same query on the msdb and distribution databases will give us over 350 more stored procedures which Microsoft had put in its build. However, the number of system stored procedures that have been actually documented by Microsoft is around 400.

Readers involved in managing SQL Servers, as a part of their day-to-day task, will find this chapter quite helpful. The section on Mining System Stored Procedures will be especially useful to those involved in T-SQL coding.

Let's now start our discussion with a brief overview of system stored procedures.




SQL Server 2000 Stored Procedures Handbook
SQL Server 2000 Stored Procedures Handbook (Experts Voice)
ISBN: 1590592875
EAN: 2147483647
Year: 2005
Pages: 100

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