System Stored Procedures | Inside Microsoft SQL Server 2000

SQL Server installs a large number of system stored procedures that are used mostly for administrative and informational purposes. In many cases, these are called behind the scenes by the SQL-DMO objects used by SQL Server Enterprise Manager and other applications. But the system stored procedures can also be called directly; only a few years ago, doing so was the primary mechanism by which SQL Server was administered. Old-time SQL Server users were indoctrinated into using system stored procedures.

With the great tools and interfaces that are a core part of SQL Server today, there's no longer much reason to work with these system stored procedures directly. But it's good to be familiar with them—understanding them can help you understand the operations that occur on the system tables and can take much of the mystery out of what's going on behind the scenes with the graphical tools. If you look carefully through the sysobjects system table (particular in the master database), you'll find system procedures and system functions that are not among the documented system routines. Typically, these routines exist to be called by some other system routine that is exposed; to support some SQL Server utility, such as Enterprise Manager; or to provide statistics to the Microsoft System Monitor. These procedures aren't documented for direct use because they exist only to support functionality exposed elsewhere—they don't provide that functionality independently.

There's nothing secret about these routines. For the most part, their text is exposed clearly in syscomments. You're welcome to explore them to see what they do and use them if you want. But unlike with the documented system routines, Microsoft has no commitment to maintaining the undocumented system routines or making them exhibit exactly consistent behavior in future releases. Of course, if your applications were to become dependent on one of these routines, you could certainly maintain your own version of it to perform exactly as you specify, or you could use one of them as a starting point and customize it to suit your needs. You'll probably want to use a different name for your own replacement routines so that they won't be overwritten during a service pack upgrade.

The SQL Server online documentation explains the specifics of each system routine, so I won't go into detail on each and every routine here. Since there are many more system stored procedures than system functions, the following sections discuss the broad categories for grouping stored procedures: General, Catalog, SQL Server Agent, Replication, and Extended.

General System Procedures

System stored procedures aid in the administration of your system, and they sometimes modify the system tables. You shouldn't configure the system to allow direct modification of the system tables because a mistake can render your database useless. That's why direct modification of system tables is prohibited by default. If modification is necessary, you can use a system stored procedure that is known to do the job correctly.

Below are some of the SQL Server system stored procedures, divided into four categories: basic, security, cursor, and distributed query procedures. Each procedure's name gives you a clue about its function.

Some basic system procedures:

 sp_helptext sp_helptrigger sp_attach_db sp_lock sp_help sp_helpconstraint sp_recompile sp_helpindex sp_who

Some of the security stored procedures:

 sp_addapprole sp_defaultlanguage sp_helplogins sp_helpntgroup sp_addlogin sp_addrolemember sp_changedbowner sp_grantdbaccess sp_changeobjectowner sp_grantlogin

The following cursor procedures are used to find information about existing cursors:

 sp_describe_cursor sp_describe_cursor_columns sp_describe_cursor_tables sp_cursor_list

Finally, here are some of the stored procedures available for managing distributed queries. Distributed queries access data from multiple heterogeneous data sources. You can store this data in the same computer or different computers, and you can access it by using OLE DB.

 sp_addlinkedserver sp_addlinkedsrvlogin sp_serveroption

Catalog Stored Procedures

Applications and development tools commonly need access to information about table names, column types, datatypes, constraints, privileges, and configuration options. All this information is stored in the system tables, which are also known as the system catalogs. But system tables might require changes between releases to support new features, so directly accessing the system tables could result in your application breaking from a new SQL Server release.

For this reason, SQL Server provides catalog stored procedures, a series of stored procedures that extract the information from the system tables, providing an abstraction layer that insulates your application. If the system tables are changed, the stored procedures that extract and provide the information will also be changed to ensure that they operate consistently (from an external perspective) from one release to another. Many of these procedures also map nearly identically to ODBC calls. The SQL Server ODBC driver calls these procedures in response to those function calls. While it's fine to directly query the system catalogs for ad hoc use, if you're deploying an application that needs to get information from the system tables, you should use these catalog stored procedures. Here's a partial list:

 sp_column_privileges sp_columns sp_stored_procedures sp_databases sp_table_privileges sp_tables

SQL Server Agent Stored Procedures

SQL Server Enterprise Manager uses SQL Server Agent stored procedures to set up alerts and schedule tasks for execution. If your application needs to carry out tasks such as these, you can call the following procedures directly. They must be called from or qualified by the msdb database. Here are some of the SQL Server Agent stored procedures:

 sp_add_alert sp_add_job sp_add_jobstep sp_helptask sp_delete_alert sp_start_job sp_stop_job sp_help_alert

Web Assistant Procedures

SQL Server provides a number of procedures for creating and managing Web pages. These procedures are typically called from within the Web Assistant Wizard, but they're documented and you can call them directly. You can consider them a subcategory of the SQL Server Agent procedures because SQL Server Agent handles much of the automatic updating of Web pages. Here are the Web assistant procedures:

 sp_dropwebtask sp_enumcodepages sp_makewebtask sp_runwebtask

Replication Stored Procedures

You use replication stored procedures to set up and manage publication and subscription tasks. SQL Server Enterprise Manager typically provides a front end to these procedures, but you can also call them directly. SQL Server has many replication stored procedures; frankly, it's hard to manually use replication with these procedures. You can do it, though, if you're determined. Everything SQL Server Enterprise Manager does ultimately uses these system stored procedures. I urge you to use SQL Server Enterprise Manager or SQL-DMO if you need to add customized replication administration to your application.

Extended Stored Procedures

You can use extended stored procedures to create your own external routines in a language such as C and have SQL Server automatically load and execute those routines just like a regular stored procedure. As with stored procedures, you can pass parameters to extended stored procedures, and they can return result sets, return status values, or both. This allows you to extend SQL Server capabilities in powerful ways. Many SQL Server features introduced in the last few years have been implemented using extended stored procedures. These features include additions to SQL Server Enterprise Manager, login integration with Windows NT and Windows 2000 domain security, the ability to send and receive e-mail messages, and the ability to create a Web page based on a query.

Extended stored procedures are DLLs that SQL Server can dynamically load and execute. They aren't separate processes spawned by SQL Server—they run directly in the address space of SQL Server. The DLLs are created using the Open Data Services (ODS) API, which SQL Server also uses.

Writing an extended stored procedure sounds harder than it really is, which is probably why these procedures are somewhat underused. But writing one can be as simple as writing a wrapper around a C function. For example, consider the formatting capabilities in SQL Server's PRINT statement, which are limited and don't allow parameter substitution. The C language provides the sprintf function, which is powerful for formatting a string buffer and includes parameter substitution. It's easy to wrap the C sprintf function and create an extended stored procedure that calls it, resulting in the procedure xp_sprintf. To show you how easy this is, below is the entire source code for the procedure xp_sprintf. Note that most of this code is setup code, and at the heart is the call to the C run-time function sprintf:

 // XP_SPRINTF // // Format and store a series of characters and values into an // output string using sprintf // // Parameters: // srvproc - the handle to the client connection // // Returns: // XP_NOERROR or XP_ERROR // // Side Effects: // // SRVRETCODE xp_sprintf( SRV_PROC * srvproc ) { int numparams; int paramtype; int i; char string[MAXSTRLEN]; char format[MAXSTRLEN]; char values[MAXARGUMENTS][MAXSTRLEN]; char szBuffer[MAXSTRLEN]; // Get number of parameters // numparams=srv_rpcparams(srvproc); // Check number of parameters // if (numparams < 3) { // Send error message and return // LoadString(hModule, IDS_ERROR_PARAM, szBuffer, sizeof(szBuffer)); goto ErrorExit; } if (paramtype != SRVVARCHAR) { // Send error message and return // LoadString(hModule, IDS_ERROR_PARAM_TYPE, szBuffer, sizeof(szBuffer)); goto ErrorExit; } if (!srv_paramstatus(srvproc, 1)) { // Send error message and return // LoadString(hModule, IDS_ERROR_PARAM_STATUS, szBuffer, sizeof(szBuffer)); goto ErrorExit; } for (i = 2; i <= numparams; i++) { paramtype=srv_paramtype(srvproc, i); if (paramtype != SRVVARCHAR) { // Send error message and return // LoadString(hModule, IDS_ERROR_PARAM_TYPE, szBuffer, sizeof(szBuffer)); goto ErrorExit; } } for (i = 0; i < MAXARGUMENTS; i++) { memset(values[i], 0, MAXSTRLEN); srv_bmove(srv_paramdata(srvproc, i + 3), values[i], srv_paramlen(srvproc, i + 3)); } memset(string, 0, MAXSTRLEN); srv_bmove(srv_paramdata(srvproc, 2), format, srv_paramlen(srvproc, 2)); format[srv_paramlen(srvproc, 2)]='\0'; // This is the heart of the function — it simply wraps sprintf // and passes back the string sprintf(string, format, values[0], values[1], values[2], values[3], values[4], values[5], values[6], values[7], values[8], values[9], values[10], values[11], values[12], values[13], values[14], values[15], values[16], values[17], values[18], values[19], values[20], values[21], values[22], values[23], values[24], values[25], values[26], values[27], values[28], values[29], values[30], values[31], values[32], values[33], values[34], values[35], values[36], values[37], values[38], values[39], values[40], values[41], values[42], values[43], values[44], values[45], values[46], values[47], values[48], values[49]); srv_paramset(srvproc, 1, string, strlen(string)); return XP_NOERROR; ErrorExit: srv_sendmsg(srvproc, SRV_MSG_ERROR, SPRINTF_ERROR, SRV_INFO, (DBTINYINT) 0, NULL, 0, 0, szBuffer, SRV_NULLTERM); return XP_ERROR; }

Because extended stored procedures run in the same address space as SQL Server, they can be efficient; however, a badly behaved extended stored procedure can crash SQL Server, although this is unlikely. A server crash is more likely to result from someone's maliciousness rather than carelessness. But this is a definite area for concern, and you should understand the issues that are covered in the rest of this section.

An extended stored procedure runs on the thread that called it. Each calling thread executes using the operating system's structured exception handling constructs (most notably try-except). When a thread is poorly written and performs a bad operation, such as trying to reference memory outside its address space, it is terminated. But only that single connection is terminated, and SQL Server remains unaffected. Any resources held by the thread, such as locks, are automatically released.

In actual use, extended stored procedures don't introduce significant stability problems into the environment. Nonetheless, it's certainly possible for an extended stored procedure to twiddle some data structure within SQL Server (to which it would have access because the procedure is part of SQL Server's address space) that could disrupt SQL Server's operation or even corrupt data. If you're unlucky, this could happen as the result of a bug in the extended stored procedure; however, it's more likely that the procedure would cause an access violation and have its thread terminated with no ill effects.

A procedure could conceivably cause data corruption, but such data structures aren't exposed publicly, so it would be hard to write a malicious procedure. It is possible, however, and given the propensity of some malicious people to create viruses, we can't rule this problem out (although no such cases have been documented). The ultimate responsibility for protecting your data has to rest with your system administrator, who has control over which, if any, extended stored procedures can be added to the system.

A Word of Warning
Although extended stored procedures can be terrific additions to your applications and in most cases do not negatively impact your system's behavior, exceptions do exist. You can save yourself hours of grief if you keep the following points in mind:

Be sure to include full error checking and exception handling. An unhandled exception will usually bring down SQL Server and generate a Dr. Watson dump.

Be sure to stress test your extended procedures thoroughly. Even though they're not running on SQL Server itself, don't assume that you don't have to be concerned about how SQL Server will behave with hundreds or thousands of users accessing it.

Read and follow the guidelines in Microsoft Knowledge Base article Q190987, entitled "Extended Stored Procedures: What Everyone Should Know," which is included on the companion CD.

Only someone with the sysadmin role can register an extended stored procedure with the system (using sp_addextendedproc), and only a system administrator can grant others permission to execute the procedure. Extended stored procedures can be added only to the master database (eliminating their ability to be easily transferred to other systems via a backup and restore of databases, for example). Administrators should allow use of only the procedures that have been thoroughly tested and proven to be safe and nondestructive.

Ideally, administrators could also have access to the source code and build environment of the extended stored procedure to verify that it bears no malicious intent. (Some people say they don't even want their SQL Server administrator to be able to do this, because that person might not be trustworthy. If that's the case, you have bigger problems. If you can't trust your system administrator, you'd better get a new one!)

Even without extended stored procedures, an administrator can disrupt a SQL Server environment in many ways. (Munging the system tables would be a good start.) Of course, you can decide that no one will ever add extended stored procedures to your system. That's certainly a safe approach, but you give up a powerful capability by taking this route. (It's kind of like deciding never to ride in a car in order to avoid an accident.)

Even if you prohibit foreign extended stored procedures from your system, don't go overboard with a sweeping rule that prevents the use of even the procedures provided by Microsoft to implement new features. Could one of these procedures have a bug that could disrupt SQL Server? Sure, but a bug is no more likely to occur than if the code for these procedures had simply been statically linked into the SQLSERVER.EXE file rather than implemented as a DLL and loaded on demand. Of course, Microsoft procedures are thoroughly tested before their release. The chance of a catastrophic bug occurring is pretty low. The fact that these are extended stored procedures in no way increases the risk of bugs. It's an engineering decision—and a smart one—that allows Microsoft to add more features to the product in a way that doesn't require extra change to the core product or additional resource use by environments that don't call these features.

By convention, most of the extended stored procedures provided with the product begin with xp_. Unlike the sp_ prefix, no special properties are associated with xp_. In fact, several extended stored procedures begin with sp_ (for example, sp_getbindtoken), which allows them to be called from any database without being fully qualified, so we could just call it with EXEC sp_getbindtoken instead of EXEC master.dbo.xp_getbindtoken. To ascertain whether a procedure is a regular stored procedure or an extended stored procedure, you shouldn't rely on the prefix of the name. Use the function OBJECTPROPERTY. For example, the following should return 1, indicating that sp_getbindtoken is an extended procedure:

 USE master SELECT OBJECTPROPERTY(OBJECT_ID('sp_getbindtoken'), 'IsExtendedProc')

If you substitute a "real" stored procedure name for sp_getbindtoken, such as sp_help, the function will return a 0.

As with stored procedures, some extended stored procedures that come with the product are not documented for direct use. These procedures exist to support functionality elsewhere—especially for SQL Server Enterprise Manager, SQL-DMO, and replication—rather than to provide features directly themselves.

The following extended stored procedures are provided and documented for direct use. First, here are the general extended stored procedures:

 xp_cmdshell xp_sprintf xp_sscanf

Here are some of the administration and monitoring extended stored procedures:

 xp_logevent xp_msver xp_sqlmaint

These are the integrated security-related extended stored procedures:

 xp_enumgroups xp_grantlogin xp_loginconfig xp_logininfo xp_revokelogin

And finally, here are the SQL mail-related extended stored procedures:

 xp_deletemail xp_findnextmsg xp_readmail xp_sendmail xp_startmail xp_stopmail

SQL Profiler Extended Procedures

SQL Profiler provides a graphical user interface to a set of extended stored procedures for monitoring dozens of aspects of SQL Server's internal behavior and for creating traces. You can also use extended stored procedures to create your own applications that monitor SQL Server. In Chapter 17, I'll discuss SQL Profiler and describe many of the extended procedures available for working with traces.