SQL Server System Stored Procedures

Two Dozen of My Favorite System Stored Procedures (devx.com, Article)
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.



sp_monitor
Let's get started with an easy one. sp_monitor displays statistics about the instance of SQL Server in which it runs. It takes no parameters and returns all kinds of interesting information, including how busy SQL Server has kept the machine's CPU, the number of input and output packets the server has processed, the number of errors it has encountered, a count of reads and writes, and the number of successful and attempted logins.

When you run this stored procedure from the master database. It reports each statistic as a number in the form of either number(number)-number% or number(number). The first number is since SQL Server restarted and the second is since sp_monitor was last run, so you can accumulate and explore statistics over time, and some values also provide the percentage difference between the two.

sp_bindsession
Related to: sp_getbindtoken

Support for transactions is one of the most important reasons to use database engines such as SQL Server; hopefully you make frequent, wise use of them in your applications. But did you know that you can bind operations on different connections in the same instance of SQL Server to the same transaction? This is conceptually a little like using the Distributed Transaction Coordinator to bind transactions across heterogeneous database engines. Once you let your imagination run free, it is easy to find a lot of uses for sp_bindsession and its sibling sp_getbindtoken.

A complete code example to demonstrate the use of sp_bindsession is a bit too complex to include here, but you can find a great example here. The idea goes something like this: Start by grabbing a session token using sp_getbindtoken.

   DECLARE @bindToken varchar(255)   EXECUTE sp_getbindtoken @bindToken OUTPUT   SELECT @bindToken AS Token

This returns an externally meaningless value such as [^_5DZY0L13\0OIBHi`XH-5--`B--, which you can pass to sp_bindsession to enroll the current connection in the transaction represented by the token. The session is unbound when the stored procedure returns or when you call sp_bindsession with an empty string.

sp_create_removable
Related to: sp_certify_removable

Have you ever needed to put a SQL Server database on a read-only media, such as a CD? Then you need sp_create_removable, which creates a removable media database. The stored procedure creates three or more files for the system catalog tables, transaction log, and one or more for the data tables, and places the database on those files. You can't use CREATE DATABASE or Enterprise Manager to create the removable database. You'll need to run sp_create_removable with sysadmin privileges.

Below is an example of using this stored procedure to create a WxmanagerRem removable database, specifying the size of each of the component file types. sp_certify_removable verifies that the resulting database is configured correctly to be read-only.

   sp_create_removable 'WxManagerRem',       'WxManagerRemSYS',      'E:\WxManagerRem.mdf', 4,       'WxManagerRemLog',      'E:\WxManagerRem.LDF', 2,      'WxManagerRemData',      'E:\WxManagerRem.ndf', 4   GO   sp_certify_removable 'WxManagerRem', 'auto'

Once the database is created, you can populate it with data and, as part of the installation procedure for your app, attach it to an instance of SQL Server.

sp_addmessage
Related to: sp_altermessage, sp_dropmessage

SQL Server contains a rich set of error messages that it uses to annoy users, administrators, and developers. Actually, SQL Server does a pretty decent job of letting you know exactly what went wrong with an errant T-SQL operation, and most development tools let you respond to problems and perhaps retry operations. There are hundreds of standard messages, many of which are formatted to allow parameter substitution for customized feedback, such as to specify the objects involved in the problem.

But sometimes it is handy to go beyond the standard messages provided by SQL Server. That's where sp_addmessage comes in. Once you create your own message with a unique error number, you can raise that error from your own stored procedures. Using sp_altermessage, you can even customize the contents of built-in messages. Be careful with this latter technique, since you have to be careful not to break something that SQL Server does with the messages!

Calling sp_addmessage is easy.

   USE master   EXEC sp_addmessage @msgnum = 60000,       @severity = 16,       @msgtext =          N'The item named %s already exists in %s.',       @lang = 'us_english'

The message number, severity, and text parameters are required, but you can also specify other options for each message such as how in the code above @lang is specified as us_english. You can have multiple versions of each message, each in a different language, and SQL Server will use the right one at run time based on the language settings of the server. You can also specify whether to log the message when it is used and whether it should replace any existing message with the same message number rather than throw an error.

You can drop all instances of a message number, no matter how many language variations you have, with the sp_dropmessage stored procedure and the all option.

   EXEC sp_dropmessage 60000, 'all'

In most cases, you should use error message numbers greater than 50000 and severity levels from 0 through 18. Severity levels higher than that require sysadmin privileges to issue a RAISERROR statement.

sp_executesql
A useful technique when you need utmost flexibility in a T-SQL application is to build the code dynamically and execute it on the fly. Microsoft has made such dynamic execution far more efficient than in earlier versions of SQL Server, such as by caching the execution plan for multiple executions. The danger, of course, is that building a SQL statement on the fly and concatenating parameter values is that it opens up the app to SQL injection attacks, so it should be used with caution and careful validation of any user-controllable input. Dynamic SQL also has different security issues. SQL Server will always validate the user's permissions on the underlying objects, even though there is an unbroken ownership chain from the stored procedure through objects referenced.

You can execute SQL dynamically using either the EXECUTE T-SQL statement or the sp_executesql stored procedure. sp_executesql is the more flexible of the two though, because it supports using parameters. The unlimited, comma-delimited list of parameters can include the data type so that SQL Server will do data type validation.

Listing 1 shows an example of sp_executesql that reads data from the Customers table in the Northwind database. This example uses a string of parameter names and data types in the @ParmDefinition string, including both a single input and a single output value. The single input parameter is in the @CustomerID variable. Notice too how the crux of the code, where the dynamic SQL is actually executed, uses the EXECUTE statement to actually run the code. This is an interesting example of combining the two statements that shows how each is used.

One thing you have to be careful of with dynamic SQL is the context of the statements. Consider this code.

   USE pubs   GO   sp_executesql N'USE Northwind'   GO   SELECT * FROM Shippers   GO

This batch starts by making pubs the default database and then executing a dynamic SQL statement that sets Northwind as the default database. Once the dynamic SQL has finished executing and by the time the SELECT statement executes, the database context is back in the pubs database, so the SELECT statement errors.

sp_server_info
Sometimes it is handy to find out things about the environment you're running in. sp_server_info returns a list of attributes about the SQL Server instance, the database gateway in use, or the underlying data source. The attributes returned include such mundane things as OWNER_TERM for what the database calls an owner (hopefully it is not a shock that it is "owner" for SQL Server), maximum length of table and other object names, whether certain operations are permitted, and the size of things such as the maximum number of columns in an index.

You can call sp_server_info in either of two ways. Without parameters, it returns a recordset with the complete list of attributes, including the attribute ID (an int), its name, and the current value. Or you can specify the attribute ID to return a single record with the attribute's information.

   sp_server_info   sp_server_info @attribute_id = 2

sp_procoption
This is one of those rather interesting anomalies in SQL Server. According to BOL, sp_procoption "sets procedure options." The interesting part is that "s" at the end of "options." Despite the description, sp_procoption sets exactly one option to configure a stored procedure for auto execution when SQL Server starts up. Only objects in the master database owned by dbo can have their startup setting changed, and that change is restricted to stored procedures with no parameters.

It is easy enough to use.

   USE master -- only in master   GO   EXEC sp_procoption 'sp_IndexRebuild',       'startup', 'true'

Simply pass the name of the stored procedure, specify the procedure option you want—the only option is 'startup'—and set to true to auto execute or false to prevent auto-execution.

According to the latest beta documentation I have for SQL Server 2005, there is still only going to be a single option for this procedure in the next version. One can only guess at why there is such a procedure seemingly designed for multiple options! It is particularly strange since this procedure replaced two other system stored procedures, sp_makestartup and sp_unmakestartup, in older versions of SQL Server. If anyone knows the story about this one trick pony, I'd love to hear it.

sp_datatype_info
Do you ever stop in the middle of an intense T-SQL coding session and try to remember some arcane feature of one of the T-SQL data types? Maybe you can't remember the exact number of bytes that an image type can hold, or whether a varbinary is 8,000 or 8,192 bytes. You can fire up BOL, or you can run sp_datatype_info.

This system stored procedure returns just about everything you need to know about the data types available in any SQL Server database. The list includes types that are used as identity fields, a couple of arcane types, and any user-defined data types in the current database. I just ran it on my development machine in master and ended up with 51, including some user defined types that are still in there from when I added some objects and types to a database after forgetting to change the current database. (Sigh).

Anyway, the information returned includes the internal type name and id, the precision of numeric types (essentially the number of digits), the characters used for literal values (such as ' for strings and $ for money), whether it is nullable, and plenty more.

Calling the procedure is straightforward. For a complete list of types, pass either a null or no parameter. For a single type, pass in the id value.

   sp_datatype_info   sp_datatype_info -11

sp_depends
Related to: sp_MSdependencies*

One of the things that drives me nuts sometimes about relational databases is the incredible weave of dependencies in any non-trivial database. The problem commonly rears its ugly head when I try to delete an object only to find out that it has all kinds of stuff that depends on that object, such as how foreign keys and stored procedures depend on the existence of a particular table. Usually I look at the error message, go delete what it tells me is the problem dependency, try to delete the object again, go delete the dependencies, and start over. Not a great way to make use of my time.

Passing the name of a database object to sp_depends lists all the dependencies, such as how this code lists several constraints, stored procedures, and views on the table. Fortunately, passing the name of a database object to sp_depends lists all the dependencies, such as how this code lists several constraints, stored procedures, and views on the table. USE Northwind GO sp_depends 'Order Details' The object you pass to the procedure can be a table, view, stored procedure, or trigger. The procedure returns up to two result sets one for the object's dependencies and another for objects that depend on the object. The one thing this procedure doesn't do is return a complete list of dependencies of the dependencies that may be nested. But it would be easy enough to use the list returned by sp_depends to get that information as well, or use the undocumented sp_MSdependencies. sp_depends doesn't return information about any dependencies that are outside the current database, so be careful if you have any cross-database dependencies. xp_sprintf and xp_sscanf I was quite surprised to find in SQL Server a couple of extended system stored procedures that mimic C string functions. I'm sure Microsoft included these to support parameterized string substitution in error messages. xp_sprintf formats and stores a series of characters and values in the string output parameter. The first parameter is the output string with %s characters for each place where you want to substitute a value. The remaining parameters provide the substitution values for each %s in the output string. The following code takes what at first seems to be a rather attractive reason to read CoDe and softens the message a bit to something the publishers can tolerate. DECLARE @ret varchar(500) exec master..xp_sprintf @ret OUTPUT, 'CoDe ... readers will%s receive a free%sHarley Davidson ... hog.', ' not', '' PRINT @ret exec master..xp_sprintf @ret OUT, 'CoDe readers ... will%sreceive a free%sHarley Davidson hog.', '', ' chance to win a ' PRINT @ret The results are as follows. CoDe readers will not receive a free Harley Davidson hog. CoDe readers will receive a free chance to win a Harley Davidson hog. xp_sscanf is kind of a weird analog to xp_sprintf. It reads data from the string provided into the argument locations given by each format argument. The first parameter is the input string that contains a space-delimited list of data you want to shred, and the second parameter is the format used to read the data. Listing 2 shows a simple example that returns my data as a single record with five fields. In this case I used the format string '%s %s %s %s %s' to simply read the raw data. You can also use constant strings to eliminate some of the data. I've found that getting xp_sscanf to work so that you get the results you want from strings takes a fair bit of work tweaking to get right because of some weird spacing issues. It's not a tool you'll use often, but it sure beats writing your own! xp_sqlmaint The sqlmaint utility is a great tool for performing various maintenance operations on your server. You can run DBCC checks, back up a database and log, update statistics, and rebuild indexes. It is perfect for creating a SQL Agent job to do the tasks at regular intervals. But what if you want to do any of those tasks from within a stored procedure, especially an auto execution procedure? One option would be to shell out using the dangerous xp_cmdshell, but a better option is to use the xp_sqlmaint stored procedure. Simply pass a string with space-delimited sqlmaint switches and it does the work. For example, the following code will run various DBCC checks on the Northwind database on the local server and output the results to a report on the e: drive: exec master..xp_sqlmaint '-S (local) -D Northwind -CkDB -CkAl -CkCat ... -Rpt e:\Nwind_chk.rpt' You can send the report to a text or HTML file, or send it as part of an e-mail. sp_processmail Related to: xp_deletemail, xp_findnextmsg, xp_readmail, xp_sendmail One of the geekier things you can do with SQL Server is to send it an e-mail in case you think it is lonely or down. Actually, you can send it a single query in the text of an e-mail and SQL Server will send the results of the query to the original sender and anyone on the cc: list as an attached file. E-mails sent to the server are processed when you run this stored procedure, so you'll probably want to run it at regular intervals to clear out the inbox, perhaps as part of a SQL Agent job. A typical call to sp_processmail looks like the statement below. This invocation will process messages with a subject line of "SQL:pubs," return the results in a text file attachment with extension TXT, use a comma to separate the data fields, and run the queries against the pubs database. sp_processmail @subject = 'SQL:pubs', @filetype = 'TXT', @separator = ',', @dbuse = 'pubs' Using e-mail with a SQL Server is a fairly complicated process, normally requiring that Outlook be installed on the server—ick!—and lots of configuration to get it all working. But this can be a handy technique to execute queries against a database, particularly when you are constrained by a network configuration that prevents other ways of connecting to a database. xp_cmdshell Despite its inherent risks, the xp_cmdshell extended system stored procedure is useful for a variety of purposes. You can use it to run any Windows command line from within a stored procedure. But be wary: leaving this stored procedure active on your server can give an attacker a dangerous tool. Keep it available only if you absolutely must use it! Here are some examples of its usage. It is as simple as passing the command line string as the argument and optionally passing no_output if you don't want SQL Server to pay any attention to any information returned by the command. exec master..xp_cmdshell 'dir e:\*.*' exec master..xp_cmdshell 'format j:', no_output exec master..xp_cmdshell 'format j:' The command line executes with the security context of the SQL Server service account. So if you have the account set to Local System or any other high-privileged account, the command executes with that account's privileges. sp_helpprotect Secure databases are critical to today's distributed applications, and SQL Server provides plenty of tools you can use to lock down the server. But making a server or database secure is a complex process, and it is far too easy to configure conflicting security settings. sp_helprotect is handy for getting information about how objects are configured for permissions in the current database. The procedure has several options, depending on the kind of information you need. At its simplest, you can call it without any parameters and get a complete list of permissions for every object in the database. This is great for a quick survey of how things are set, but can return an overwhelming amount of information that can obscure important problems. USE Northwind exec sp_helprotect You can also pass in an object name and get back the permissions for just that object. In a default, unmodified version of Northwind, the following statement will show the information in Figure 1, revealing that the public role has unfettered access to the data in the Categories table. All of the columns are self-explanatory except perhaps the Column column. It contains 'all' if the setting applies to all current columns of the object, 'new' if it applies to new columns, or a combination of the two. Figure 1: Getting Permissions: The figure shows the results after running sp_helprotect against Northwind's Categories table. exec sp_helprotect 'Categories' You can use the @permissionarea parameter to specify whether the results should include object permissions ('o'), statement permissions ('s'), or both. exec sp_helprotect @permissionarea='o s' You can also filter the results for either a specific grantor or grantee user using either the @grantorname or @username parameter. exec sp_helprotect @username='public' sp_getapplock Related to: sp_releaseapplock Transactions and object locks are a fact of life with relational databases. SQL Server has rich support for both along with many features that serve to relieve the severe concurrency problems that a heavily-used database can suffer. Most of the time you'll be in good shape using the built-in features and you don't have to worry about going deeper. But when you need to go deeper, take a look at sp_getapplock. BOL says that this stored procedure places a lock on an application resource. That simple description belies a lot of power and complexity for a feature that I can't begin to do justice to in a short part of an article like this. But the idea is that you can create what amounts to a custom, shared or exclusive semaphore to synchronize different instances of stored procedures. Basically, if you have a weird concurrency issue you have to code around that isn't related to or solvable by built-in object locks in SQL Server, an application lock might solve the problem. Listing 3 shows the basic idea. When creating shared locks, both instances of Query Analyzer can lock mySemaphore and do normal processing. But once a process requests and gets an exclusive lock on the semaphore, it signals to other processes that they can't do some operation until they are able to get their own lock. It's all quite similar to typical threading issues using a custom object in T-SQL. This is another system stored procedure you won't need to use often, but when you do, it is pretty slick. sp_makewebtask Related to: sp_dropwebtask, sp_runwebtask One of the coolest features to demo in SQL Server 2000 is how it can automatically create a Web page based on the results of a query. The scenario is that you have a Web page that displays data from the database that doesn't change too often, such as your product catalog. You can have SQL Server automatically regenerate the page whenever any data changes, such as a new product addition or a price change, and it can even upload it to your Web server. The magic is handled by the sp_makewebtask, sp_dropwebtask, and sp_runwebtask procedures. sp_makewebtask takes care of creating the static Web page, sp_runwebtask runs a previously defined Web task, and sp_dropwebtask drops a Web task. Listing 4 shows an example of the code that creates the page shown in Figure 2. Don't despair at the industrial design of the page! You can use a page template to format the data, and there are plenty of formatting options. Figure 2: Industrial but Functional: This generic Web page was generated by running sp_makewebtask.Frankly, it is far easier to set all this up using the Web Assistant Wizard in Enterprise Manager, but it's nice to be able to write the code oneself. sp_MSforeachdb*, sp_MSforeachtable* Have you ever wanted to do any kind of processing for every database on a server or every table in a database? If so, the undocumented stored procedures sp_MSforeachdb and sp_MSforeachtable are exactly what you need. These are just about as easy to use as they can be. Simply pass each procedure a string with the T-SQL command you want to execute for each database or table: sp_MSforeachdb "print '?' DBCC CHECKDB('?')" USE Northwind GO sp_MSforeachtable "print '?' DBCC DBREINDEX('?')" SQL Server substitutes each database or table name for the '?' placeholder and executes each command. Just be careful though! If you have a lot of databases or tables, commands such as DBCC can take a long time to run. xp_enumdsn* Some of the best system stored procedures in this article are the simplest, as you've already seen by reading this far. xp_enumdsn certainly fits into that category. It takes no parameters and returns a list of the data source names installed on the server where the code runs. This is quite handy for enumerating the DSNs instead of writing custom .NET code to do the same thing. xp_dirtree* Related to: xp_fileexist*, xp_getfiledetails It's not often that you'll need to examine the structure of the server's drive and retrieve a directory tree, but it can be handy for finding a file or deciding where to save something out to disk. The undocumented xp_dirtree retrieves the subdirectory structure of a given drive or folder. Just pass it a fully qualified drive or directory name and you'll get back a result set with two fields: the subdirectory name and a depth field that indicates how deep its position is in the hierarchy below the specified directory. master..xp_dirtree 'c:\Program Files' It's not blazingly fast for a directory as large as Program Files, but it beats shelling out to the command prompt or using COM operations to get the same information. xp_get_MAPI_profiles* Related to: xp_get_MAPI_default_profile*, xp_test_MAPI_profile* SQL Server has supported various functions using e-mail for a long time, so it shouldn't be any surprise that it has the internal features to manipulate e-mail and check the environment for e-mail support. One of the necessary components of e-mail on the server is a MAPI profile. You have to have at least one MAPI profile installed, and you can use xp_get_MAPI_profiles to get a list of the available profiles or xp_get_MAPI_default_profile to get the default profile. Once you have the profile name, you can use xp_test_MAPI_profile to test to make sure that you can use it successfully. You frequently have to deal with errors using SQLMail, and testing the profile ahead of time can avoid errors in your application. xp_getnetname* Another simple yet useful undocumented stored procedure is xp_getnetname. This procedure simply returns the machine name of the server, which can be useful for pointing code to the correct server. xp_readerrorlog* An important part of administering any SQL Server installation is to log activity and regularly monitor those logs for problems and suspicious activity. SQL Server has plenty of options for logging, but by default it creates logs and archives them continuously. That way you have a short history of the server when anything goes wrong. But monitoring logs can be a royal pain. While there are many third-party tools with lots of whiz-bang features that will drain your wallet, SQL Server has plenty of tools built-in. For simplicity, however, nothing beats the undocumented xp_readerrorlog procedure. It is clearly how Enterprise Manager retrieves the list of log entries for the current log under the "Management SQL Server Logs" section. You can call xp_readerrorlog without any parameters and get back the contents of the log SQL Server is currently using to log activity. Alternatively, you can pass an integer to indicate which log you want—1 is the current log, 2 is the most recently archived log, and so on. -- Read the current log master..xp_readerrorlog -- or master..xp_readerrorlog 1 -- First archived log master..xp_readerrorlog 2 One annoyance about getting the log information this way is that the first few records in the result set are header information, containing the log date, SQL Server version information, and the Microsoft copyright. If a line of text is too long (greater than about 255 characters), the output is continued to the next line and the ContinuationRow field will contain a 1 rather than a 0. If you use the stored procedure to create your own archive, you'll need to adjust the data to allow for these anomalies. xp_regread* Related to: xp_regdeletekey, xp_regdeletevalue, xp_regwrite, xp_addmultistr, xp_regenumvalues, xp_regremovemultistring Both SQL Server 2000 and the upcoming SQL Server 2005 are COM-based Windows applications and, as such, make heavy use of the Windows registry. Even though Microsoft for years has discouraged saving application-specific data in the registry to avoid performance woes due to bloating and security problems, SQL Server has to interact with the registry. And because it has several internal stored procedures that can interact with the registry, your custom T-SQL code can too. Both SQL Server 2000 and the upcoming SQL Server 2005 are COM-based Windows applications and, as such, make heavy use of the Windows registry. This group of xp_reg procedures provides many of the most useful features of the Win32 registry functions to read, write, delete, and enumerate registry keys and values. These are useful tools for an attacker, particularly since registry access permission through SQL Server is under the security context of the service process account, all too often Local System. That means that usually these procedures have unfettered access to pretty much every corner of the registry. So use these functions with caution and prevent their unauthorized use. Here is a fairly benign use of xp_regread to find out where the SQL Server help files are located. DECLARE @helpPath varchar(450) exec master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'SOFTWARE\Microsoft\Microsoft SQL ... Server\80\Tools\Help', @value_name='HelpPath', @value = @helpPath OUT PRINT @helpPath On my fairly standard installation of SQL Server on my development machine, this code returns this path. C:\Program Files\Microsoft SQL ... Server\80\Tools\Books Not overly useful information, but it shows just how easy it is to work with the registry. And with procedures like xp_regdeletekey you can really do some damage. But you didn't get any such ideas here! sp_fixindex* The stored procedure sp_fixindex is a handy undocumented procedure that uses the also undocumented DBCC REPAIRINDEX tool to fix corrupted indexes for system tables. It takes three parameters: the name of the database to repair, the table whose index you want to repair, and the index id of the problem index. The code below repairs the indexes of the systypes table in Northwind after forcing any dirty pages in memory to be written to disk with the CHECKPOINT statement. Note that no other users can be connected to the database when you run this code, so you may need to set the Single User option to true using the sp_dboption procedure in the master database. USE Northwind GO CHECKPOINT GO sp_fixindex 'Northwind', 'systypes', 1 The undocumented status of this stored procedure, like some others I've covered here, is mentioned in at least one Microsoft KnowledgeBase article: 106122, "HOW TO: Use the sp_fixindex Stored Procedure." Does that make it documented since Microsoft has publicly acknowledged its existence, even though it isn't in BOL? Only you can decide if this makes it worthy of use, since it still may not survive to the next version of the database. Much More to Explore Whew! That is a lot of system stored procedures to explore and make use of in your SQL Server and T-SQL development work. But there are many more that I considered including but didn't for various reasons mostly because I thought they were fairly well-known, were so esoteric to be of little use, or so convoluted and complex that they would need an entire article of their own. You can learn a lot about SQL Server by exploring its built-in features. Microsoft kept most such features easily accessible and close to the surface for us to explore and use, even if they didn't always choose to document the feature. Happy database coding!