Browse DevX
Sign up for e-mail newsletters from DevX


Two Dozen of My Favorite System Stored Procedures : Page 4

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

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

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!

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.

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