Two Dozen of My Favorite System Stored Procedures

n important part of learning any development platform, whether it is a development platform like Visual Studio using C# or Visual Basic .NET or a server environment like SQL Server or Microsoft Exchange, is learning about the features built-in and available in the environment. There is no reason to write your own versions of available tools unless, of course, the built-in tool doesn't do what you need. But even then you can frequently build on the existing feature for your custom version, steal code and modify it to fit your needs, or learn about the internal workings of the environment.

SQL Server 2000's framework of platform code contains an extensive set of system stored procedures and functions you can use in your own apps. There are hundreds of nuggets that SQL Server uses for its own purposes that you can use as well. I've listed many of the categories in Table 1. Many are documented in SQL Server Books Online (BOL) and various Knowledgebase articles while others are undocumented, usually because Microsoft didn't originally envision that developers and administrators would have any reason to use them directly.

Finding the complete set of system stored procedures in SQL Server is easy: just explore the sysobjects table in the master database. Filter on FN, IF, and TF for functions, as well as P for stored procedure in the xtype field to filter out all the other objects. In Enterprise Manager, look at the master table's list of stored and extended stored procedures, and double-click on the name to view the source. Every so often a regular stored procedure will just be a wrapper for a call to an extended stored procedure, so you won't learn much from the T-SQL code. But usually you'll see exactly where SQL Server is retrieving system information from and you can use the same resources.

I hope you'll discover some new gems of your own and perhaps some ideas of new uses for a few system stored procedures that you already know about and use.

Table 1: SQL Server has a wealth of built-in stored procedures in many categories.

Category	Description
Active Directory Procedures	Register SQL Server and databases in Active Directory
Catalog Procedures	ODBC data dictionary functions
Cursor Procedures	Implements cursor variable functionality
Database Maintenance Plan Procedures	Core maintenance tasks to ensure performance
Distributed Queries Procedures	Implement and manage Distributed Queries
Full-Text Search Procedures	Implement and query full-text indexes
Log Shipping Procedures	Configure and manage log shipping
OLE Automation Procedures	Use standard COM objects within a T-SQL batch
Replication Procedures	Manage replication
Security Procedures	Manage security
SQL Mail Procedures	Perform e-mail operations from within SQL Server
SQL Profiler Procedures	Used by SQL Profiler to monitor performance and activity
SQL Server Agent Procedures	Used by SQL Server Agent to manage activities
System Procedures	General maintenance of SQL Server
Web Assistant Procedures	Used by the Web Assistant
XML Procedures	XML text management
General Extended Procedures	Interface from SQL Server to external programs

The Envelope Please...

Don't be afraid of the undocumented label. While Microsoft cautions that such features can go away in future versions of SQL Server or change dramatically, these are so widely known that Microsoft faces immense pressure to maintain them for backwards compatibility.

In this article, I'll cover many of my favorite gems that I've discovered and used over my years of writing SQL Server-based applications. Any stored procedure built into SQL Server 2000 is fair game, whether it is documented or not. Don't be afraid of the undocumented label. While Microsoft cautions that such features can go away in future versions of SQL Server or change dramatically, these are so widely known that Microsoft faces immense pressure to maintain them for backwards compatibility. Just be aware that things can change in SQL Server 2005 and beyond but, as a practical matter, even documented features can change radically. I'll mark the undocumented stored procedures with an asterisk (*) in the section header so that you'll know when you may be treading on dangerous ground by using one.

The stored procedures I examine here are skewed neither toward administrators nor developers. I'm a developer and that tends to bias my selections, but both groups will find interesting gems here. And if you have a favorite that I don't cover, please send me an e-mail about it and why you like it! I won't say much about the permissions necessary to run each procedure; that is pretty well documented in BOL. But most of the time you'll need sysadmin privileges or ownership of the database to get the full range of information.

I won't cover every nuance of using these stored procedures because I'm going for breadth rather than depth to give you an idea of what is available. You'll find plenty of resources on the Web, and there are entire Web sites devoted to documenting the undocumented features of SQL Server.