RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Two Dozen of My Favorite System Stored Procedures

SQL Server contains a wealth of features that you can use in your own stored procedures and applications. This article explores a couple dozen of my favorite system stored procedures, including extended stored procedures and several that are undocumented.

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.



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.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date