| Downloads |
|---|
| 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'
You can test this procedure by running the following code, which executes the procedure against Northwind and msdb:
USE Northwind EXEC sp_getusertables EXEC msdb..sp_getusertables
Note that you get different user tables for each database.
SQL Server always resolves special procedures that reference user objects against the master database. For example, first drop any tables called T1 in master, Northwind, and Pubs. Then, run the code that Listing 1 shows to create a table called T1 in the Northwind database and in the Pubs database and a special procedure that returns all rows from T1. Next, run the following code, which executes your procedure first in Northwind, then in Pubs:
EXEC Northwind..sp_getT1rows EXEC pubs..sp_getT1rows
You get error messages stating that the object name T1 is invalid because SQL Server looks for the table only in master, as the following messages show:
Server: Msg 208, Level 16, State 1, Procedure sp_getT1rows, Line 4 Invalid object name 'T1'. Server: Msg 208, Level 16, State 1, Procedure sp_getT1rows, Line 4 Invalid object name 'T1'.
Apparently, a back door lets you force SQL Server to resolve user objects the same as it does system objects—namely, in the context of the database you're connected to or the one you specify, if you do so explicitly. You mark the procedure as a system object by using the undocumented stored procedure sp_MS_marksystemobject:
USE master EXEC sp_MS_marksystemobject sp_getT1rows Now run the previous code snippet again: EXEC Northwind..sp_getT1rows EXEC pubs..sp_getT1rows
In the result, which Figure 1 shows, note that SQL Server accessed T1 first in Northwind, then in Pubs.
When you use dynamic execution in your stored procedure, SQL Server resolves user objects in the invoking database the same way it resolves system objects even if you don't mark the stored procedure as a system object. To demonstrate this behavior, run the code that Listing 2 shows to create a stored procedure that accepts a table and column name and returns all values in the given column in that table. When you force the execution of the procedure in Pubs and Northwind, SQL Server resolves the object names within the specified database, and the following code runs successfully:
EXEC pubs..sp_getcolumn @table_name = authors, @col_name = au_id EXEC Northwind..sp_getcolumn @table_name = Orders, @col_name = OrderID
Sp_ Prefix for Other Objects
The unique functionality of special procedures is common knowledge, but it applies to other object types as well. I learned from Kalen Delaney that if you create views and tables in master and prefix their names with sp_, they behave differently than the documented way that views and tables behave. A table prefixed with sp_ behaves similarly to a special procedure in that you can access the table from any database without qualifying it with master.
To demonstrate this concept, run the code that Listing 3 shows. The code creates the sp_digits table in master, then changes the database context to Pubs and issues a few INSERT statements without qualifying the table name. At callout A, the code changes the database context to Northwind, issues a few more INSERT statements, then changes the database context to tempdb and queries the table. You can modify and retrieve data from sp_digits regardless of your database context even if you don't qualify the name with master. The final query in Listing 3 should produce 10 rows, each containing a different digit in the range 0 to 9.
Views in the master database that have the sp_ prefix are also accessible from any database without the master qualifier, but they behave a bit differently from stored procedures. With special views, SQL Server always resolves references to system objects in the context of the master database no matter which database you're querying from. As an example, run the following code to create the sp_vgetusertables view:
USE master GO CREATE VIEW sp_vgetusertables AS SELECT name FROM sysobjects WHERE type = 'u'
Then, query the view from different databases:
USE master SELECT * FROM sp_vgetusertables USE Northwind SELECT * FROM sp_vgetusertables USE pubs SELECT * FROM sp_vgetusertables
Note that you always get the table list from master.
Output Parameters for Sp_executesql
To execute dynamically constructed T-SQL code, you use the EXEC command and the sp_executesql stored procedure. A question concerning dynamic execution that T-SQL programmers frequently post in the public SQL Server newsgroups (such as msnews.microsoft.com) is, How can I run a calculation within the dynamically executed code and return the result to the invoking batch? For example, suppose you want to write a stored procedure that accepts a table name and a column name and returns the number of distinct values in the specified column. You need to use dynamic execution, but SQL Server Books Online (BOL) says that sp_executesql can have only input parameters, not output parameters. However, the text of system stored procedures such as sp_helpmergearticleconflicts, sp_MSenum_replsqlqueues, and sp_MSenum_replqueues in the master database shows that SQL Server product developers use output parameters with sp_executesql.
T-SQL programmers can also use sp_executesql's undocumented output parameters. Run the code that Listing 4 shows to create the sp_distinctcount stored procedure. This stored procedure accepts table and column names and returns the distinct count of values in the column specified in the @cnt output parameter. The stored procedure's code constructs the T-SQL assignment SELECT statement (a SELECT statement that assigns values to variables) in the @sql variable. The query string assigns the query result to the @distinctcount variable, which is defined in the sp_executesql invocation's second argument as an int OUTPUT parameter. SQL Server passes the pointer for the @cnt parameter to the @distinctcount variable in sp_executesql's third argument.
To test the stored procedure, run it first against Pubs, providing the authors table and the au_lname column as arguments:
DECLARE @n AS int EXEC pubs..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))
Note that sp_distinctcount returns the value 22. Next, run it against Northwind, providing the Orders table and the CustomerID column as arguments:
DECLARE @n AS int EXEC Northwind..sp_distinctcount @table_name=Orders, @col_name=CustomerID, @cnt=@n OUTPUT PRINT 'Distinct count of customers that made orders is: ' + CAST(@n AS varchar (10))
This time, the procedure returns the value 89, the number of distinct values in the Orders.CustomerID column, showing that SQL Server used a different table in a different database.
Xp_execresultset
T-SQL programmers commonly want to execute dynamic code longer than 4000 characters and to execute code constructed by a query. But sp_executesql limits code to 4000 bytes—the nvarchar data type's limitation. And although running queries against system tables and INFORMATION_SCHEMA views is a convenient way to construct administrative code, you have to run the query and copy the code from the Query Analyzer Results pane to the Execution pane before you execute it.
The undocumented xp_execresultset extended stored procedure provides a way for programmers to satisfy both desires. As an example of executing dynamic code longer than 4000 characters, let's store some long, dynamically constructed code in separate rows of a table. Running the code in Listing 5, page 21, creates the TSQLCommands table and populates it with three rows that together construct a valid statement. Note a couple of important things here. First, you have to design the table where you store the command elements so that you'll be able to sort them correctly. I used the cmdrow column to store a sequence of numbers that determine the position of the command's element within the complete command. Second, you can't break a word in the middle and continue it in the next row. Before executing xp_execresultset, run the following query:
SELECT cmdtext FROM TSQLCommands WHERE cmdid = 1 ORDER BY cmdrow
The result is the valid T-SQL command that Figure 2 shows.
The xp_execresultset procedure accepts two arguments: an nvarchar string containing the query whose results you're executing and an nvarchar string containing the name of the database you're running the code against. To execute the results of the preceding query in Pubs, you would run the following code:
EXEC master..xp_execresultset N'SELECT cmdtext FROM tempdb..TSQLCommands WHERE cmdid = 1 ORDER BY cmdrow', N'pubs'
You get the au_lname and au_fname columns from the authors table, as Figure 3 shows.
As an example of executing query-constructed code, suppose you want to write code that counts the number of rows in all the base tables in your database. First, run the following code to create the #NumRows temporary table:
CREATE TABLE #NumRows(table_name SYSNAME, owner SYSNAME, numrows int)
Then, run the code at callout A in Listing 6 to execute the query that generates the INSERT statements that populate the table. You get the results that Figure 4 shows.
Usually, you'd copy the generated INSERT statements to Query Analyzer's Execution pane and execute them. However, you can use xp_execresultset to run the query, then execute its results as callout B in Listing 6 shows.
Note that because xp_execresultset accepts the query string as a character-based argument, you need to prefix and suffix the string with single quotes and duplicate each quote within the string. To examine the results, query the #NumRows temporary table:
SELECT * FROM #NumRows
In this article, I started exploring T-SQL back doors—undocumented features that T-SQL programmers use—mainly those related to stored procedures. In my next column, I'll discuss back doors related to user-defined functions (UDFs). As you've probably noticed, the back doors that I explored have a lot of useful functionality, but you need to be aware of the risks I mentioned. I hope to see those back doors become supported front doors in a future release of SQL Server.
