Browse DevX
Sign up for e-mail newsletters from DevX


Two Dozen of My Favorite System Stored Procedures : Page 2

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

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.

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.

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.

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.

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