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
, 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.
The Envelope Please...
Active Directory Procedures
Register SQL Server and databases in Active Directory
ODBC data dictionary functions
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
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
General maintenance of SQL Server
Web Assistant Procedures
Used by the Web Assistant
XML text management
General Extended Procedures
Interface from SQL Server to external programs
|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.