Browse DevX
Sign up for e-mail newsletters from DevX


Two Dozen of My Favorite System Stored Procedures : Page 7

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: 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!

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!

Don Kiely, MVP, MSCD, is senior technology consultant specializing in developing secure desktop and Web applications that integrate databases, Microsoft Office, and related technologies, using tools including SQL Server, Visual Basic, ASP.NET and XML. When he isn't writing software, he's writing and about technology, speaking about it at conferences, and training others.
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