Browse DevX
Sign up for e-mail newsletters from DevX


Two Dozen of My Favorite System Stored Procedures : Page 6

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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

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.

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.

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.

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.

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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



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