SQL Server 2000 is packed full of useful system stored procedures there for its own use that you can use as well.
This article will explore a couple dozen of the most useful hidden gems you can use to more easily manage your databases, improve the applications that use the data, and provide new ways to impress potential mates at parties. You'll learn when to use these system stored procedures, explore when to use them, and see lots of code.
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.
An important part of learning any development platform, whether it is a development platform like Visual Studio using C# or Visual Basic .NET or a server environment like SQL Server or Microsoft Exchange, is learning about the features built-in and available in the environment. There is no reason to write your own versions of available tools unless, of course, the built-in tool doesn't do what you need. But even then you can frequently build on the existing feature for your custom version, steal code and modify it to fit your needs, or learn about the internal workings of the environment.
SQL Server 2000's framework of platform code you can use in your apps is the extensive set of system stored procedures and functions. There are hundreds of nuggets that SQL Server uses for its own purposes that you can use as well. I've listed many of the categories Table 1. Many are documented in SQL Server Books Online (BOL) and various Knowledgebase articles while others are undocumented, usually because Microsoft didn't originally envision that developers and admins would have any reason to use them directly.
Finding the complete set of system stored procedures in SQL Server is easy: just explore the sysobjects table in the master database. Filter on FN, IF, and TF for functions, as well as P for stored procedure in the xtype field to filter out all the other objects. In Enterprise Manager, look at the master table's list of stored and extended stored procedures, and double-click on the name to view the source. Every so often a regular stored procedure will just be a wrapper for a call to an extended stored procedure, so you won't learn much from the T-SQL code. But usually you'll see exactly where SQL Server is retrieving system information from and you can use the same resources.
I hope that in this article you'll discover some new gems of your own and perhaps some ideas of new uses for a few system stored procedures that you already know about and use.
The Envelope Please…
In this article, I'll cover many of my favorite gems that I've discovered and used over my years of writing SQL Server-based applications. Any stored procedure built into SQL Server 2000 is fair game, whether it is documented or not. Don't be afraid of the undocumented label. While Microsoft cautions that such features can go away in future versions of SQL Server or change dramatically, these are so widely known that Microsoft faces immense pressure to maintain them for backwards compatibility. Just be aware that things can change in SQL Server 2005 and beyond but, as a practical matter, even documented features can change radically. I'll mark the undocumented stored procedures with an * in the section header so that you know when you may be treading on dangerous ground by using one.
The stored procedures I examine here are skewed neither toward admins nor developers. I'm a developer and that tends to bias my selections, but both groups will find interesting gems here. And if you have a favorite that I don't cover, please send me an e-mail about it and why you like it! I won't say much about the permissions necessary to run each procedure; that is pretty well documented in BOL. But most of the time you'll need sysadmin or ownership of the database to get the full range of information.
I won't cover every nuance of using these stored procedures because I'm going for breadth rather than depth to give you an idea of what is available. You'll find plenty of resources on the Web, and there are entire Web sites devoted to documenting the undocumented features of SQL Server. See the Recommended Reading sidebar for a short list of some of the best of these sites.
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.
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.
Don't be afraid of the undocumented label. While Microsoft cautions that such features can go away in future versions of SQL Server or change dramatically, these are so widely known that Microsoft faces immense pressure to maintain them for backwards compatibility.
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 at VB2Max at www.vb2themax.com/Item.asp?PageID=TipBank&ID=378, a site run by my buddy Francesco Balena in Italy. The idea goes something like this: Start by grabbing a session token using sp_getbindtoken.
DECLARE @bindToken varchar(255)EXECUTE sp_getbindtoken @bindToken OUTPUTSELECT @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.
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', 4GOsp_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: sp_altermessage, sp_dropmessage
SQL Server contains a rich set of error messages that it uses to annoy users, admins, 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 masterEXEC 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.
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 pubsGOsp_executesql N'USE Northwind'GOSELECT * FROM ShippersGO
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.
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 datasource. 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_infosp_server_info @attribute_id = 2
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 its startup setting changed, and it's restricted to stored procedures with no parameters.
sp_depends to the rescue! Passing the name of a database object will list all the dependencies, such as how this code lists several constraints, stored procedures, and views on the table.
It is easy enough to use.
USE master -- only in masterGOEXEC 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 not.
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.
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 datatypes 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.
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.
sp_depends to the rescue! Passing the name of a database object will list all the dependencies, such as how this code lists several constraints, stored procedures, and views on the table.
USE NorthwindGOsp_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 that 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 @retexec 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.
Related: 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.
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_outputexec 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.
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 Northwindexec 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.
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'
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 show 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.
Related: 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.
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.
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 NorthwindGOsp_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: 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: 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 logmaster..xp_readerrorlog-- ormaster..xp_readerrorlog 1-- First archived logmaster..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.
Related: 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.
This group of xp_reg procedures provide 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 OUTPRINT @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 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 NorthwindGOCHECKPOINTGOsp_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. Bogs” 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!
USE NorthwindDECLARE @SQLString NVARCHAR(500)DECLARE @ParmDefinition NVARCHAR(500)DECLARE @CustomerID nchar(5)DECLARE @ContactName varchar(30)SET @SQLString = N'SELECT @ContactNameOUT = ContactName ' + 'FROM Customers WHERE CustomerID = @CustID'SET @ParmDefinition = N'@CustID nchar(5), ' + '@ContactNameOUT nvarchar(30) OUTPUT'SET @CustomerID = 'DRACD'EXECUTE sp_executesql @SQLString, @ParmDefinition, @CustID = @CustomerID, @ContactNameOUT=@ContactName OUTPUTSELECT @ContactName
DECLARE @firstName varchar(15)DECLARE @lastName varchar(20)DECLARE @city varchar(25)DECLARE @state varchar(15)DECLARE @zip varchar(5)exec master..xp_sscanf 'Don Kiely Fairbanks Alaska 99708', '%s %s %s %s %s', @firstName OUTPUT, @lastName OUTPUT, @city OUTPUT, @state OUTPUT, @zip OUTPUTSELECT @firstName, @lastName, @city, @state, @zip
-- Use sp_getapplock as an application semaphore-- Must be within scope of transaction-- Step 1: In one instance of Query Analyzer, run this code:USE NorthwindGOBEGIN TRANSACTION DECLARE @result int EXEC @result = sp_getapplock @Resource = 'mySemaphore', @LockMode = 'Shared' PRINT @result-- Step 2: In another instance of QA, run this:USE NorthwindGOBEGIN TRANSACTION DECLARE @result int EXEC @result = sp_getapplock @Resource = 'mySemaphore', @LockMode = 'Shared' PRINT @result-- Step 3: Rollback transaction in both QA 1 and 2ROLLBACK TRANSACTION-- Step 4: Return to QA 1 and get a shared lockBEGIN TRANSACTION DECLARE @result int EXEC @result = sp_getapplock @Resource = 'mySemaphore', @LockMode = 'Shared' PRINT @result-- Step 5: Do the same in QA 2, but now get an Exclusive lock:BEGIN TRANSACTION DECLARE @result int EXEC @result = sp_getapplock @Resource = 'mySemaphore', @LockMode = 'Exclusive' PRINT @result-- This should block-- Step 6: In QA 1, rollback the transaction:ROLLBACK TRANSACTION-- Should now get the applock in QA 2, with result 1-- Step 7: Rollback QA2 transaction:ROLLBACK TRANSACTION
EXECUTE sp_makewebtask @outputfile = N'E:\Northwind.htm', @query=N'SELECT [ProductName], [QuantityPerUnit], ...[UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel] ...FROM [Products] WHERE [Products].Discontinued=0', ...@fixedfont=1, @bold=1, @HTMLheader=3, @webpagetitle=N'Northwind Products', @resultstitle=N'Query Results', @dbname=N'Northwind', @whentype=10, @datachg=N'TABLE=Products COLUMN=QuantityPerUnit, UnitPrice, ...UnitsInStock, UnitsOnOrder, Discontinued', @procname=N'Northwind Web Page', @codepage=65001, @charset=N'utf-8'
|Active Directory Procedures||Register SQL Server and databases in Active Directory|
|Catalog Procedures||ODBC data dictionary functions|
|Cursor Procedures||Implements cursor variable functionality|
|Database Maintenance Plan Procedures||Core maintenance tasks to ensure performance|
|Distributed Queries Procedures||Implement and manage Distributed Queries|
|Full-Text Search Procedures||Implement and query full-text indexes|
|Log Shipping Procedures||Configure and manage log shipping|
|OLE Automation Procedures||Use standard COM objects within a T-SQL batch|
|Replication Procedures||Manage replication|
|Security Procedures||Manage security|
|SQL Mail Procedures||Perform e-mail operations from within SQL Server|
|SQL Profiler Procedures||Used by SQL Profiler to monitor performance and activity|
|SQL Server Agent Procedures||Used by SQL Server Agent to manage activities|
|System Procedures||General maintenance of SQL Server|
|Web Assistant Procedures||Used by the Web Assistant|
|XML Procedures||XML text management|
|General Extended Procedures||Interface from SQL Server to external programs|
A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name, which are stored in a relational database management system (RDBMS) as a group, so it can be reused and shared by multiple programs.How do I make multiple stored procedures at once? ›
- Right click the database in Management Studio and click on Task.
- Click on Generate Scripts.
- In the dialog that comes up, click next as you specify the right options.
- Select the checkbox for "Stored Procedures"
- Select All the required stored procedures.
- Select option to generate script to a new window or file.
What Does Stored Procedure Mean? A stored procedure is a subroutine available to connected relational database system applications. Stored procedures must be called or invoked, as they are sets of SQL and programming commands that perform very specific functions.Do people still use stored procedures? ›
Stored procedures have been falling out of favour with some organizations for several years now. The preferred approach of these businesses for accessing their database(s) is to employ an Object-relational Mapper (ORM) such as NHibernate or Entity Framework.What are the two types of stored procedures? ›
Types of Stored Procedures
User-defined Stored Procedures. System Stored Procedures.
These stored procedures are just the mechanism the provider or driver uses to communicate user requests to an instance of SQL Server. They are intended only for the internal use of the provider or the driver. Calling them explicitly from a SQL Server-based application is not supported.Can we have 2 procedure with same name? ›
Overloading procedure names
A procedure is identified by its name and signature, which is the number of input arguments and the data types of the arguments. Two procedures in the same schema can have the same name if they have different signatures. In other words, you can overload procedure names.
You can use shift+click to select all the stored procedures and you can then right-click and script them to a file. Options 3: The simplest of them. Hope this may help.How do I join two stored procedures in SQL? ›
CAST(SUBSTRING(TABLE_NAME, 7, 5) AS int) AS SystemID -- Returning the substring as a integer of the table name.Can you give an example of a stored procedure? ›
A stored procedure is used in the SQL Server with the help of the " Execute " or " Exec " Keyword. For example, if we want to execute the stored procedure " Getstudentname ", then we will use the following statement.
A stored procedure is a set of instructions for a database, like a function in EGL. Stored procedures differ from prepared statements because the stored procedure is kept permanently in the database itself, while a prepared statement is local to your program or logic part and is cached by the database only temporarily.Why do people hate stored procedures? ›
Stored procedures are difficult to unit test. With an ORM, you can mock your database code so as to be able to test your business logic quickly. With stored procedures, you have to rebuild an entire test database from scratch. Stored procedures offer no performance advantage whatsoever.Is stored procedure a good idea? ›
They are secure, offer performance advantages and are easy to maintain and modify. Stored procedures can be a good avenue for simplifying the way you present data and deal with parameters in your application code versus using error-prone and lengthy ad-hoc queries.What is disadvantage of stored procedure? ›
The only disadvantage of a Stored Procedure is that it can be executed only in the database and utilizes more memory in the database server.What are two examples of procedure? ›
- Example 1 – Assemble a Program.
- Example 2 – Execute a Program.
- Example 3 – Edit a File.
The definition of procedure is order of the steps to be taken to make something happen, or how something is done. An example of a procedure is cracking eggs into a bowl and beating them before scrambling them in a pan. A particular method for performing a task.What are the three types of procedures? ›
- Transform procedures.
- Source procedures.
- Target procedures.
- Option 1 – The ROUTINES Information Schema View. You can use the ROUTINES information schema view to get a list of all user-defined stored procedures in a database. ...
- Option 2 – The sys.objects System Catalog View. ...
- Option 3 – The sys.procedures Catalog View.
Security: Stored procedures allow you to enhance the security of an application or a database by restricting the users from direct access to the table. Low network traffic: The server only passes the procedure name instead of the whole query, reducing network traffic.What is the difference between stored procedure and SQL? ›
Basic Differences between Stored Procedure and Function in SQL Server. The function must return a value but in Stored Procedure it is optional. Even a procedure can return zero or n values. Functions can have only input parameters for it whereas Procedures can have input or output parameters.
If multiple stored procedures have the same name and the same number of arguments, the procedures are overloaded (also known as overloaded UDRs). The JDBC driver throws an SQLException for overloaded stored procedures because the call cannot resolve to a single stored procedure.Can I call a stored procedure from another? ›
In large database applications, it is common to call one stored procedure from another stored procedure.Can we create multiple procedures with same name in same package? ›
Overloading of Names
Multiple stored procedures in the same schema can have the same name, as long as their signatures differ, either by the number of arguments or the argument types.
You can include multiple SQL statements on the SQL query panel. The exceptions are CALL and CREATE PROCEDURE statements.How do I create a script for all stored procedures in SQL Server? ›
- Connect to a server that's running SQL Server.
- Expand your Databases node.
- Expand your Programmability node.
- Expand your Stored Procedure node.
- Right-click the stored procedure dbo.uspGetBillOfMaterials > Script Stored Procedure As > DROP and CREATE To > New Query Editor Window:
Put differently, UNION allows you to write two separate SELECT statements, and to have the results of one statement display in the same table as the results from the other statement. SQL has strict rules for appending data: Both tables must have the same number of columns.What are the 4 types of SQL JOIN operations? ›
Four types of joins: left, right, inner, and outer.What is the most efficient way of joining 2 table in same database? ›
Method 1: Relational Algebra
Relational algebra is the most common way of writing a query and also the most natural way to do so. The code is clean, easy to troubleshoot, and unsurprisingly, it is also the most efficient way to join two tables.
No there is nothing wrong in linking two identical primary keys in different tables but the problem arises when the data type is declared as identity. When you declare some field as identity then the value is auto incremented and is decided by the seed if provided.What is stored procedure with real time example? ›
A stored procedure is a set of SQL statements, which has been created and stored in the database as an object. Stored procedure will accept the input and output parameters, so that a single procedure can be used over the network by several users, using different input.
You can create stored procedures by using REXX programs that can contain dynamic SQL. DBAs and programmers generally use REXX for administrative tasks. Traditional programming languages: C, C++, COBOL, PL/I, and Assembler.How do I save a stored procedure in SQL? ›
To save the modifications to the procedure definition, on the Query menu, select Execute. To save the updated procedure definition as a Transact-SQL script, on the File menu, select Save As. Accept the file name or replace it with a new name, and then select Save.What is another name for a stored procedure? ›
A stored procedure (also termed proc, storp, sproc, StoPro, StoredProc, StoreProc, sp, or SP) is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database data dictionary.What is the difference between stored procedure and procedure? ›
Answers. 1) Procedure is a block of PL/SQL code , it never stored in database. and 3) Stored procedure is block of PL/SQL code it is named and stored within the database.Are stored procedures faster? ›
Stored procedures are faster as compared to LINQ query since they have a predictable execution plan and can take the full advantage of SQL features. Hence, when a stored procedure is being executed next time, the database used the cached execution plan to execute that stored procedure.How does a stored procedure run? ›
There are two different ways to execute a stored procedure. The first and most common approach is for an application or user to call the procedure. The second approach is to set the procedure to run automatically when an instance of SQL Server starts.What happens if we delete a stored procedure? ›
Deleting a procedure can cause dependent objects and scripts to fail when the objects and scripts are not updated to reflect the removal of the procedure.How long should stored procedures be? ›
The typical stored procedure with business logic can be a single file with 1000+ lines of code.Are stored procedures faster than ORM? ›
The bottom line is stored procedures using a DBMS's native SQL language can be orders of magnitude faster than ANY ORM generated SQL. However ORM approach is more maintainable. Most ORM devs are not experts at advanced SQL, and have no understanding/experience of this.Are stored procedures scalable? ›
Stored procedures increase scalability by isolating application processing on the server. In addition, automatic dependency tracking for stored procedures aids the development of scalable applications.
Conclusion. Overall, stored procedures outperform dynamic SQL. They are faster, easier to maintain, and require less network traffic.Why stored procedure is not recommended? ›
Stored procedures are difficult to unit test. With an ORM, you can mock your database code so as to be able to test your business logic quickly. With stored procedures, you have to rebuild an entire test database from scratch. Stored procedures offer no performance advantage whatsoever.Are stored procedures faster than functions? ›
As you can see, the scalar functions are slower than stored procedures. In average, the execution time of the scalar function was 57 seconds and the stored procedure 36 seconds.Are stored procedures safe? ›
As seen from the process above, stored procedures are a secure and safe way to give access to your database. That means someone can only be able to do what is defined in stored procedures that you have given him permission to call. And that makes stored procedures great for securing data in a database.Is stored procedure better than view? ›
Stored Procedures are best used for INSERT-UPDATE-DELETE statements. Whereas Views are used for SELECT statements. You should use both of them. In views you cannot alter the data.What is a system process procedure? ›
The procedure outlines the order of specific steps required to achieve an end result, and a series of procedures taken together make up a process. Successful systems bring together processes and procedures in a way that defines them as a set of instructions.How many types of stored procedures are there? ›
The Stored Procedures stage supports three types of procedures: Transform procedures. Source procedures. Target procedures.What are the types of procedures in computer system? ›
Types of Procedures
Sub Procedures perform actions but do not return a value to the calling code. Event-handling procedures are Sub procedures that execute in response to an event raised by user action or by an occurrence in a program. Function Procedures return a value to the calling code.
The main manufacturing process types are project, jobbing, batch, line and continuous. Project processes produce products of high variety and low volume. A feature of a project process is that the location of the product is stationary.How do you write a system procedure? ›
- Step 1: Align to business process. ...
- Step 2: Define the scope of the procedure. ...
- Step 3: Gather process information. ...
- Step 4: Create a standard structure for procedures. ...
- Step 6: Communicate and provide training.
Your company might use a specific procedure for actions like sending files to clients or conducting office fire drills. You might encounter procedures at work through training manuals or in-person information sessions where superiors explain company procedures to their employees.How do I view a stored procedure in SQL? ›
Using SQL Server Management Studio
Expand Stored Procedures, right-click the procedure and then select Script Stored Procedure as, and then select one of the following: Create To, Alter To, or Drop and Create To. Select New Query Editor Window. This will display the procedure definition.
Procedures can be used throughout a program, making them simpler and quicker to code. Using procedures has an added benefit. If something needs to be changed in a procedure, it only needs to be changed once, within the procedure code. This change will then appear wherever the procedure is used in the program.What is main procedure? ›
A main procedure is a procedure that can be specified as the program entry procedure (and so receive control when an ILE program is first called).What is the main procedure of computer? ›
In computer programming, a procedure is a set of coded instructions that tell a computer how to run a program or calculation. Many different types of programming languages build a procedure. Depending on the programming language, a procedure may also be called a subroutine, subprogram or function.