DOWNLOAD THE CODE:
Download the Code 41044.zip

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'
   Prev. page   [1] 2 3 4 5     next page



You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

This article disappointed in the beginning but I found the last two pages pretty interesting and useful - thanks.

Cedric (since SQL Server 1.0A so not always in touch with the latest back door potential)

Cedric- January 27, 2004

Hi,

I found the article interesting, however, I cannot get the code samples to work.

In listing 4 the code

CREATE PROC sp_distinctcount @table_name AS SYSNAME, @col_name AS SYSNAME, @cnt AS OUTPUT

doesn't appear to define as datatype for @cnt and the syntax appears incomplete. I might be incoorect be I was expecting @cnt int OUTPUT.

Upon trying to execute

DECLARE @n AS int EXEC sp_distinctcount @table_name='authors', @col_name='au_lname', @cnt=@n OUTPUT PRINT 'Distinct count of last names of authors is: ' + CAST(@n AS varchar(10))

The error

Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 9 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

was returned.

Could you provide some assistance.

Regards

John Langley

john Langley- February 10, 2004

Itzik, Excellent article, not just saying it because I respect your knowledge and understanding of SQL Server, I found the information U had to share on special views and tables very useful. . . .I have to admit I stumbled across the executing a special procedure against any database some years ago, and I have found it very useful ever since

Olu Adedeji- February 19, 2004

Very intresting article. Thanks,Rimon.

Rimon- February 20, 2004

Script in listing 2 misses nvavchar in declare clause:

CREATE PROC sp_getcolumn @table_name AS SYSNAME, @col_name AS SYSNAME AS DECLARE @sql AS nvarchar (1000) -- here ....

MIkhail- February 23, 2004

Fix your script, guys Listing 4: Creation Script for the Sp_distinctcount Stored Procedure USE master GO

CREATE PROC sp_distinctcount @table_name AS SYSNAME, @col_name AS SYSNAME, @cnt AS INT OUTPUT -- here AS DECLARE @sql AS nvarchar (1000) SET @sql = N'SELECT @distinctcount = COUNT(DISTINCT [' + @col_name + N']) FROM [' + @table_name + N']'

EXEC sp_executesql @stmt = @sql, @params = N'@distinctcount int OUTPUT', -- here @distinctcount = @cnt OUTPUT GO

Mikhail- February 23, 2004

You wrote "T-SQL programmers can also use sp_executesql's undocumented output parameters.", but output parameters in sp_executesql are not undocumented. They are documented in KB262499: http://support.microsoft.com/?id=262499

Razvan Socol- February 26, 2004

Excelent article.

However, the arguments described for sp_execdirect are incorrect. The correct arguments for sp_execresultset are an nvarchar string containing the query and a bit field indicating if debug info needs to be show, instead of executing the resultset. The debug argument has a default of 0 (no debug info).

Karl Gram- February 27, 2004

When I run EXEC master..xp_execresultset with the accompaning query I get "The command(s) completed successfully." but no records. I have tried both with your example and my own. I am running everything from query analyzer.

(SQL2000)

Mark Murray- March 01, 2004

I'm trying to use the output parameters for sp_executesql you mentioned in your article, with no success. Here is the code:

ALTER PROCEDURE ap_GetRemoteServerVersion @ServerName varchar(50), @Results nvarchar(65) OUTPUT AS BEGIN DECLARE @SQL nvarchar(500)

SET @SQL = N'exec ' + @ServerName + '.master.dbo.xp_msver ProductVersion'

EXECUTE sp_executesql @Stmt = @SQL, @Params = N'@Results nvarchar(65) OUTPUT', @Results = @Results OUTPUT

END

This is the error message I receive when I run this code:

Procedure 'ap_GetRemoteServerVersion' expects parameter '@Results', which was not supplied.

My purpose in running this code is to capture "Character_Value" from the result set of xp_msver. This value is the SQL Server version information for a particular SQL Server. If you can tell me what I'm doing wrong, or know of another way to capture values from extended stored procedures, it would be greatly appreciated.

Result set for exec xp_msver 'ProductVersion':

Index Name Internal_Value Character_Value ----- -------------- -------------- --------------- 2 ProductVersion 458752 7.00.1063

Ken Powers- March 10, 2004

Hello Itzik,

Of course you are aware that there is a XP and SP version of ExecResultset. The SP version does not have the TargetDatabase as a parameter. But, your other backdoor trick of calling it from another database even though it lives in Master applies again. These different calls to generate record counts for all tables in pubs give the same result:

master..xp_ExecResultset N'select ''select count(*) as '' + quotename(name) + '' from '' + name from sysobjects where type = ''u'' order by name', N'pubs' go pubs..sp_ExecResultset N'select ''select count(*) as '' + quotename(name) + '' from '' + name from sysobjects where type = ''u'' order by name', 1 go

The code of the SP version can simply be retrieved with sp_helptext SP_ExecResultset (it's extremely ugly).

Thanks, Jacques Roumimper

Jacques Roumimper- March 26, 2004

the code sp_distinctcount contains the following errors: 1) @CNT AS INT OUTPUT 2) DECLARE @SQL AS NVARCHAR(1000)

michele- April 14, 2004

Hello Itzik, I used xp_execresultset to execute code. My intention was to execute code longer than 4000 characters. But the procedure truncates the code at 4000 characters. This means that it is not possible to execute code longer than 4000 characters with your method.

However, I did find a solution to execute code longer than 4000 characters. You have to write the code to a text file with BCP and run this text file with te osql command.

Clemens van der Veen.

cvdv- October 21, 2004

Article Rating 3 out of 5