20 Undocumented T-SQL
I can’t stress enough how important it is to avoid relying on undocumented routines unless absolutely necessary. They’re undocumented for a reason. As a rule, the functions, DBCC command verbs, stored procedures, and trace flags that have been omitted from the SQL Server documentation have been left out because Microsoft doesn’t want you to use them. They can be dangerous—possibly even catastrophic—if used improperly. Wanton misuse of a DBMS’s undocumented features is a fast ticket to lost data and apathy from the vendor.
So be careful with the commands and syntax that follow. Use them sparingly and, even then, with due caution. A mangled server quickly extinguishes the joy you get from using this or that gadget simply because you’ve just learned that it exists.
If you decide to use undocumented routines in your own code, go into it with the full expectation that those routines may change in a future release of the product. The PWDENCRYPT() function below, for example, changed between releases 6.5 and 7.0 of SQL Server, and people who wrote code that relied on it ran into trouble when they migrated to 7.0.
Don’t expect vendor support for undocumented routines. When you see the word “undocumented,” read “unsupported.” Leaving a feature undocumented frees the vendor to change it at will without having to be concerned about breaking customer code. If you decide to base mission-critical code on undocumented aspects of the product, you do so at your own risk.
Undocumented Transact-SQL, as defined here, refers to commands, functions, trace flags, DBCC command verbs, and stored procedures not listed in the SQL Server Books Online. Some of these routines are found in other publicly available Microsoft documentation; some aren’t. For this chapter, the bottom line is this: If it isn’t in the BOL, it isn’t documented.
The DBCC (database consistency checker) command originally housed a small collection of database maintenance routines that were outside the realm and syntax of traditional Transact-SQL. The idea was to group these routines under an easy-to-remember, easy-to-use command “toolbox,” out of the way of normal queries. This worked well and was basically a good idea.
Since that time, Sybase and Microsoft have expanded DBCC’s original mission to include loads of functionality not foreseen by the original designers. The verb list for the command has grown to include dozens of things not related to database error checking—to the point of being extremely unwieldy and bordering a bit on the ridiculous. These days, DBCC does everything from checking databases for consistency to wrangling full-text indexes, from managing server caches to interacting with Performance Manager. It’s practically a language unto itself.
Many of these command verbs are not documented—some for very good reasons. Why some of them were not made separate Transact-SQL commands, only the vendors know.
Before we delve into DBCC’s undocumented command verbs, there are a few pointers to be aware of. First, include the WITH NO_INFOMSGS option to limit DBCC output to error messages. This makes the output from some loquacious commands like DBCC CHECKALLOC much more manageable without losing anything of real importance. Second, use DBCC HELP(commandverb) to list built-in help on DBCC command verbs. Most of the undocumented commands aren’t displayed by the command, but it never hurts to check. Last, use DBCC TRACEON(3604) to route DBCC output to your client connection rather than the system error log. Many of the undocumented commands send their output to the error log by default, so keep this in mind. If you execute one of the commands below and receive nothing back from the server, it’s likely that the command’s output went to the error log, and you need to use trace flag 3604 to route it to you instead.
This command adds an extended procedure to the list maintained by the server. It has the same basic functionality as the sp_addextendedproc stored procedure and is, in fact, called by the procedure. The procname parameter is the name of the extended procedure, and DLL is the name of the DLL in which it resides.
DBCC ADDEXTENDEDPROC(’xp_computemode’,’xp_stats.dll’)
This command adds an object instance to track in Performance Monitor. Stored procedures that initialize Performance Monitor counters use this to set up various areas of SQL Server for performance tracking. Object is the name of the object that contains the instance (e.g., “SQL Replication Agents”), and instance is the name of the instance to add (e.g., “Logreader”).
DBCC ADDINSTANCE("SQL Replication Agents", "Snapshot")
This command sets the table lock on bulk load option for a table and can improve performance for bulk inserts since it avoids setting a row lock for every inserted row. Dbid is the database ID, tabid is the table’s object ID, and setflag is a 1 or 0 indicating whether to set the option.
DECLARE @dbid int, @objid int
SELECT @dbid=DB_ID(’pubs’), @objid=OBJECT_ID(’titles’)
DBCC BCPTABLOCK(@dbid,@objid,1)
This command is used to dump the contents of SQL Server memory buffers. Buffers can be listed for a specific object or for an entire database.
DECLARE @dbid int, @objid int
SELECT @dbid=DB_ID(’pubs’), @objid=OBJECT_ID(’pubs..titles’)
SELECT COUNT(*) FROM pubs..titles -- Load up the buffers
DBCC BUFFER(@dbid,@objid,1,2)
(Results abridged)
This command lists the contents of the memory area beginning at startingaddress for length bytes. The address specified must be a valid address within the SQL Server process space.
This command is used to perform a variety of full-text-related functions. Funcid specifies what function to perform and what parameters are valid. Catid is the full-text catalog ID, and objid is the object ID of the affected object. Note that CALLFULLTEXT is valid only within a system stored procedure. This procedure must have its system bit set (see the undocumented procedure sp_MS_marksystemobject below for more info), and its name must begin with “sp_fulltext_.” Table 20.1 lists the supported functions:
USE master
GO
IF OBJECT_ID(’sp_fulltext_resource’) IS NOT NULL
DROP PROC sp_fulltext_resource
GO
CREATE PROC sp_fulltext_resource @value int -- value for ’resource_usage’
AS
DBCC CALLFULLTEXT(9,@value) -- FTSetResource (@value)
IF (@@error<>0) RETURN 1
-- SUCCESS --
RETURN 0 -- sp_fulltext_resource
GO
EXEC sp_MS_marksystemobject ’sp_fulltext_resource’
EXEC sp_fulltext_resource 3
This command sets database options. It performs many of the functions of sp_dboption and is, in fact, called by the procedure. Dbname is the name of the database, and option is a token specifying the option to set. Table 20.2 lists the valid values for option:
DBCC DBCONTROL(’pubs’,multi)
This command lists system-level information about the specified database, including its creation date, ID, status, next timestamp value, etc.
DBCC DBINFO(’pubs’)
DBINFO STRUCTURE:
DBINFO @0x0690F998
------------------
dbi_dbid = 5 dbi_status = 4194436 dbi_nextid = 1810821513
dbi_dbname = pubs dbi_maxDbTimestamp = 2000 dbi_version = 515
dbi_createVersion = 515 dbi_nextseqnum = 1900-01-01 00:00:00.000
dbi_crdate = 1998-11-13 03:10:45.610 dbi_filegeneration = 1
dbi_checkptLSN
--------------
m_fSeqNo = 65 m_blockOffset = 340 m_slotId = 1
dbi_dbbackupLSN
---------------
m_fSeqNo = 43 m_blockOffset = 326 m_slotId = 1
dbi_lastdbbackupLSN
-------------------
m_fSeqNo = 43 m_blockOffset = 332 m_slotId = 1
dbi_createIndexLSN
------------------
m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_sortord = 52 dbi_charset = 1 dbi_LcidCfg = 1033
dbi_CompFlagsCfg = 196609 dbi_maxLogSpaceUsed = 3828736
This command manually recovers a database. Normally, databases are recovered at system startup. If this did not occur—due to an error or the disabling of recovery (see trace flags 3607 and 3608 below)—DBCC DBRECOVER can be used to attempt a manual recovery. Dbname is the name of the database to recover.
DBCC DBRECOVER(’pubs’)
This command lists DBT (DB Table) and FCB (File Control Block) information for the specified database.
This command deletes a Performance Monitor object instance previously set up with DBCC ADDINSTANCE. Object is the name of the Performance Monitor object, and instance is the name of the instance to delete. Specify a wildcard for instance to delete multiple instances.
DBCC DELETEINSTANCE("SQL Replication Merge", "%")
This command lists system-level descriptive information for the specified object.
This command detaches a database from the server. The database can then be moved to another server and reattached with sp_attach_db. This function is called by the sp_detach_db stored procedure.
DBCC DETACHDB(’northwind2’)
This command flushes all data from memory. This is useful if you’re running benchmarks and don’t want caching to skew test results.
DBCC DROPCLEANBUFFERS
This command drops an extended procedure. It’s called by sp_dropextendedprocedure.
USE master
DBCC DROPEXTENDEDPROC(’xp_computemode’)
This command closes the current error log and starts another one, cycling the file extensions similarly to a server restart. It’s called by the sp_cycle_errorlog stored procedure.
DBCC ERRORLOG
This command lists extent information for all the extents belonging to an object. Dbname is the name of the database, tablename is the name of the table, and indid is the index ID of the index to list.
DBCC EXTENTINFO(’pubs’,’titles’,1)
file_id page_id pg_alloc ext_size obj_id index_id pfs_bytes avg_used
---------- ---------- ---------- ---------- ---------- ---------- ------------------ --------
1 120 1 1 261575970 1 0x6000000000000000 25
1 132 1 1 261575970 1 0x6000000000000000 25
This command forces a recompile of all the stored procedures in a database. Dbid is the database ID of the target database. This is handy when you’ve changed an option in the database that would materially affect the queries generated for its stored procedures. Sp_dboption, for example, uses DBCC FLUSHPROCINDB to ensure that changes to compile-time options are accommodated by a database’s stored procs.
DECLARE @dbid int
SET @dbid=DB_ID(’pubs’)
DBCC FLUSHPROCINDB(@dbid)
This command flushes the procedure cache. This is handy when you need to eliminate the effects of procedure caching on benchmark tests or when you want procedure execution plans to take new configuration values into account.
DBCC FREEPROCCACHE
This command lists system-level index information for the specified object.
DECLARE @dbid int, @objid int
SELECT @dbid=DB_ID(’pubs’), @objid=OBJECT_ID(’pubs..authors’)
DBCC IND(@dbid,@objid, 1)
This command blocks schema changes by other connections until the caller commits the current transaction. It also increments the schema_ver column in sysobjects. This command has no effect if executed outside a transaction.
USE pubs
BEGIN TRAN
DBCC LOCKOBJECTSCHEMA(’titleauthor’)
-- Comment out the COMMIT below and try a DDL modification to titleauthor
-- from another connection. Your new connection will wait until this one
-- commits.
COMMIT TRAN
This command displays log record information from the current database’s transaction log. You can use INSERT..EXEC() to trap this output in a table for further processing.
CREATE TABLE #logrecs
(CurrentLSN varchar(30),
Operation varchar(20),
Context varchar(20),
TransactionID varchar(20))
INSERT #logrecs
EXEC(’DBCC LOG(’’ubs’’’)
SELECT * FROM #logrecs
GO
DROP TABLE #logrecs
(Results abridged)
This command dumps the contents of a specific database page. Dbid|dbname is the ID or name of the database, filenum is the database file number containing the page, pagenum is the number of the page, printopt specifies what to print, and cacheopt specifies whether to dump the disk or memory version of the page (values 0 and 1, respectively).
Table 20.3 lists the valid values for printopt:
Note that this command requires DBCC TRACEON(3604) in order to direct its output to your client connection.
DBCC TRACEON(3604)
GO
DBCC PAGE(’pubs’,1,70,2,0)
GO
DBCC TRACEOFF(3604)
GO
(Results abridged)
This command lists page information for the specified index.
DECLARE @dbid int, @pagebin varchar(12), @pageid int, @fileid int, @objid int
SELECT TOP 1 @dbid=DB_ID(’pubs’), @objid=id, @pagebin=first
FROM pubs..sysindexes WHERE id=OBJECT_ID(’pubs..authors’)
EXEC sp_decodepagebin @pagebin, @fileid OUT, @pageid OUT
DBCC PRTIPAGE(@dbid, @objid, 2, @pageid)
(Results abridged)
This command sets the value of a Performance Monitor instance counter. You can use this when benchmarking query and stored procedure performance to set a user-definable counter inside Performance Monitor. In fact, this is how the sp_user_counternn procedures work—they call DBCC SETINSTANCE. Object is the name of the Performance Monitor object, instance is the name of the object’s instance to adjust, counter is the name of the performance counter to change, and val is the new value of the counter.
DBCC SETINSTANCE(’SQLServer:User Settable’, ’Query’, ’User counter 1’, 3)
This command lists system-level information for the specified table.
As I said earlier, undocumented Transact-SQL elements, including functions, are usually not documented for a reason. They can be dangerous or even catastrophic if improperly used. They may also change between releases. So, use good judgment when you decide whether to use these functions in your own code.
This command encrypts a string. It’s used internally by the server to encrypt Transact-SQL code stored in syscomments (when WITH ENCRYPTION is specified).
SELECT ENCRYPT(’VALET’)
----------------------------------------------------
0x594F55415245415348414D454C4553535359434F5048414E54
This command returns the current NT system ID for a specified user or group name as a varbinary(85). Prefix username with U to search for an NT user ID; prefix it with G to search for an NT group ID. Note that this function works only within system-stored procedures that have their system bit set—see the undocumented procedure sp_MS_marksystemobject below for more information.
USE master
GO
IF (OBJECT_ID(’sp_get_sid’) IS NOT NULL)
DROP PROC sp_get_sid
GO
CREATE PROCEDURE sp_get_sid
@loginame sysname
AS
DECLARE @sid varbinary(85)
IF (charindex(’’, @loginame) = 0)
SELECT SUSER_SID(@loginame) AS ’SQL User ID’
ELSE BEGIN
SELECT @sid=get_sid(’U’+@loginame, NULL)
IF @sid IS NULL
SELECT @sid=get_sid(’G’+@loginame, NULL) -- Maybe it’s a group
IF @sid IS NULL BEGIN
RAISERROR(’Couldn’t find an ID for the specified loginame’,16,10)
RETURN -1
END ELSE SELECT @sid AS ’NT User ID’
RETURN 0
END
GO
EXEC sp_MS_marksystemobject ’sp_get_sid’
EXEC sp_get_sid ’LEX_TALIONISKHEN’
NT User ID
--------------------------------------------------------------------------------
0x0105000000000005150000000A423635BE3136688847202CE8030000
While the OBJECT_ID() function itself is, of course, documented, its optional second parameter isn’t. Since you can pass a fully qualified object name as the first argument, OBJECT_ID() can return ID numbers for objects that reside in databases other than the current one. There may be times when you want to prevent this. For example, if you’re performing a task on an object that requires access to catalog information in the current database, you may need to ensure not only that the object name translates to a valid object ID but also that it’s a local object. Pass ’local’ as OBJECT_ID()’s second parameter in order to ensure that it sees objects in the current database only, like so:
USE pubs
SELECT OBJECT_ID(’Northwind..Orders’), OBJECT_ID(’Northwind..Orders’,’local’)
--------- ---------------------------------------------------
357576312 NULL
This command compares a string with an encrypted password. Str is the string to compare, pwd is the encrypted password to use, and oldenc is a 1 or 0 indicating whether old-style encryption was used to encrypt pwd. You can retrieve an encrypted password directly from the sysxlogins password column, or you can use the undocumented PWDENCRYPT() function to create one from a string (see below).
SELECT PWDCOMPARE(’enmity’, password, (CASE WHEN xstatus&2048=2048 THEN 1 ELSE 0 END))
FROM sysxlogins
WHERE name=’k_reapr’
-----------
1
This command encrypts a string using SQL Server’s password encryption algorithm. Stored procedures that manage SQL Server passwords use this function to encrypt user passwords. You can use the undocumented PWDCOMPARE() function to compare an unencrypted string with the return value of PWDENCRYPT().
SELECT PWDENCRYPT(’vengeance’) AS EncryptedString,PWDCOMPARE(’vengeance’,
PWDENCRYPT(’vengeance’), 0) AS EncryptedCompare
EncryptedString EncryptedCompare
EncryptedString EncryptedCompare
---------------- ----------------
________ 1
This command compares two timestamp values—returns 1 if they’re identical, raises an error if they’re not. The TSEQUAL() function has been around for years—it dates back to the days when Microsoft SQL Server was merely an OS/2 port of Sybase SQL Server. It’s not used as often any more, mainly because it’s no longer necessary. You can compare two timestamp columns directly and decide for yourself whether to raise an error. There’s also no performance advantage to using TSEQUAL rather than a simple equality comparison. Still, it’s not documented in the Books Online, so I’m compelled to include it here.
USE tempdb
CREATE TABLE #testts
(k1 int identity,
timestamp timestamp)
DECLARE @ts1 timestamp, @ts2 timestamp
SELECT @ts1=@@DBTS, @ts2=@ts1
SELECT CASE WHEN TSEQUAL(@ts1, @ts2) THEN ’Equal’ ELSE ’Not Equal’ END
INSERT #testts DEFAULT VALUES
SET @ts2=@@DBTS
SELECT CASE WHEN TSEQUAL(@ts1, @ts2) THEN ’Equal’ ELSE ’Not Equal’ END
GO
DROP TABLE #testts
---------
Equal
Server: Msg 532, Level 16, State 2, Line 16
The timestamp (changed to 0x0000000000000093) shows that the row has been updated by
another user.
Trace flags are special server settings that you can configure primarily by calling DBCC TRACEON() or via the –T server command-line option. Some options make sense only on a server-wide basis, so they’re best specified on the server command line. Most, however, are specified via DBCC TRACEON( flagnum), where flagnum is the flag you want to set. To set more than one flag at a time, separate them with commas.
Use DBCC TRACESTATUS( flagnum) to list whether a flag is enabled. Pass a –1 to return a list of all flags currently set. Here’s a simple DBCC TRACEON() / TRACESTATUS() example:
EXEC master..xp_logevent 99999,’CHECKPOINT before setting flag
3502’,informational
CHECKPOINT
DBCC TRACEON(3604,3502)
DBCC TRACESTATUS(-1)
EXEC master..xp_logevent 99999,’CHECKPOINT after setting flag
3502’,informational
CHECKPOINT
DBCC TRACEOFF(3604,3502)
DBCC TRACESTATUS(-1)
Here’s what the error log looks like as a result of these commands (trace flag 3502 enables extra CHECKPOINT log information):
1999-07-27 19:57:20.06 spid11 Error: 99999, Severity: 10, State: 1
1999-07-27 19:57:20.06 spid11 CHECKPOINT before setting flag 3502.
1999-07-27 19:57:20.06 spid11 DBCC TRACEON 3604, server process ID (SPID) 11.
1999-07-27 19:57:20.06 spid11 DBCC TRACEON 3502, server process ID (SPID) 11.
1999-07-27 19:57:20.07 spid11 Error: 99999, Severity: 10, State: 1
1999-07-27 19:57:20.07 spid11 CHECKPOINT after setting flag 3502.
1999-07-27 19:57:20.07 spid11 Ckpt dbid 4 started (100000)
1999-07-27 19:57:20.07 spid11 Ckpt dbid 4 phase 1 ended (100000)
1999-07-27 19:57:20.07 spid11 Ckpt dbid 4 complete
1999-07-27 19:57:20.07 spid11 DBCC TRACEOFF 3604, server process ID (SPID) 11.
1999-07-27 19:57:20.07 spid11 DBCC TRACEOFF 3502, server process ID (SPID) 11.
Table 16.7 lists some of the many undocumented SQL Server trace flags. (See the Books Online for a list of documented flags.) This list is not comprehensive—there are many undocumented flags not included here.
There are scads of undocumented procedures. By my count, there are nearly a hundred of them, not counting replication routines. I’ve listed most of them in Table 20.5. I haven’t included all of them here for a number of reasons. First, there are simply too many to cover with any sort of adequacy. That’s why I’ve intentionally omitted the undocumented routines related to replication. Also, some undocumented routines are so dangerous and add so little value to the Transact-SQL command set that they are best left undocumented. As they say, some things are better left unsaid. Last, some of the undocumented routines behave so erratically or are so reliant on code external to the server (e.g., in Enterprise Manager or SQL-DMO) that they are either unusable or of dubious value to the Transact-SQL developer. The idea here is to provide thorough coverage without being excessive
This chapter explored a number of SQL Server trace flags, DBCC commands, functions, variables, and stored procedures that are not documented in the Books Online. If you decide to use them in your own work, you should do so with care and with the expectation that they may change in a future release of the product. And don’t expect any support from Microsoft—that’s the whole idea of not documenting something—you don’t have to support it, and you can change it at will. Using the undocumented features of any product—SQL Server included—is generally inadvisable. You shouldn’t do it unless absolutely necessary.