As with any software, SQL Server contains many back doors and other undocumented features that programmers use. Using only documented and supported features has many benefits: Your code is less prone to failures, you can more smoothly upgrade to newer SQL Server releases, porting your code to other platforms is easier, and so on. However, programmers sometimes use undocumented features and back doors to gain short-term benefits such as finishing a specific task quickly and because alternatives aren't always available. Microsoft left many features undocumented so that the SQL Server product developers could freely change those features in later releases or even service packs. Therefore, an undocumented feature carries a risk that it will change at some point. Also, you can't get help from Microsoft if undocumented features don't work the way you expect them to.
Programmers often use back doors to circumvent product limitations, such as not allowing the use of ORDER BY in a view—but these limitations were generally imposed for good reasons, such as to comply with ANSI standards. So, despite the short-term benefits of using back doors, you'll most likely pay when you try to upgrade your SQL Server—the undocumented features you used might not work anymore. This article is the first in a series exploring some undocumented features and back doors that T-SQL programmers use. Though I don't recommend using them, I hope to familiarize you with them so that you're better prepared when you run across such code—and, I have to admit, because back doors are fun! This article looks at features related to stored procedures.
Special Procedures
T-SQL developers and DBAs commonly use special procedures for metadata access. You create a special procedure in the master database, providing a name that starts with the prefix sp_. You don't need to qualify special procedures with the database name when you execute them from a database other than master, and SQL Server resolves references to system objects in the procedures against the database you're executing them from. For example, if you run sp_help in the Northwind database, you get a list of the objects in Northwind.
Sp_help performs a query against sysobjects, which in this case SQL Server resolves against the sysobjects table in the Northwind database. A little-known fact is that you can force SQL Server to resolve system objects against a different database than the one you're connected to by qualifying the special procedure with the database name, even though the procedure doesn't reside in that database. For example, if you run the following code, you get a list of the objects in the Pubs database:
USE Northwind
EXEC pubs..sp_help
To demonstrate how user-defined special procedures work, run the following code to create the procedure sp_getusertables, which retrieves the list of user tables:
USE master
GO
CREATE PROC dbo.sp_getusertables
AS SELECT name FROM sysobjects
WHERE type = 'u'