"UNDOCUMENTED"
"UNDOCUMENTED"
Definition: "Not supported by written evidence" ... (in BOL)
Since the very first version of SQL Server, there are many undocumented functions, store procedures, and features...
Many of them are only there for testing and deep debugging purposes (product support). Others are left so that the SQL Server product developers could freely change those features in later releases or even service packs.
There are a few issues that shouldn't be forgotten:
- Many of them are dangerous ...!;
- You can’t get help from Microsoft if undocumented features don't work the way you expect them to;
- An undocumented feature carries a risk that it will change at some point (... might not work after an upgrade...)!
By the other hand, it is stated that: - "Many of them are *extremely* useful for DBA’s ..." -> Paul S. Randal
There are obviously benefits on using "Undocumented" features/functions/SP's. Short-term speaking, some specific tasks are performed more quickly by using them. And we should also remember that alternatives aren't always available...
Let´s take a look on a brief list of some undocumented functions/SP’s/[Inline Variables]:
%%lockres%%
%%physloc%%
%%rowdump%%
dbcc buffer
dbcc dbinfo
dbcc dbrepair
dbcc dbtable
dbcc fileheader
dbcc flushprocindb
dbcc forceghostcleanup
dbcc ind
dbcc loginfo
dbcc page
fn_dblog
fn_dump_dblog
fn_physloccracker
fn_physlocformatter
pwdcompare
pwdencrypt
sp_catalogs_rowset
sp_catalogs_rowset_rmt
sp_catalogs_rowset2
sp_columns_rowset
sp_enumoledbdatasources
sp_linkedservers_rowset
sp_linkedservers_rowset2
sp_msaddguidcolumn
sp_msaddguidindex
sp_msdbuseraccess
sp_msdbuserpriv
sp_msdependencies
sp_msdrop_object
sp_msforeachdb
sp_msforeachtable
sp_mshelpcolumns
sp_mshelpindex
sp_mshelptype
sp_msindexspace
sp_mskilldb
sp_msloginmappings
sp_mstablekeys
sp_mstablerefs
sp_mstablespace
sp_msuniquename
sp_readerrorlog
sp_who2
xp_delete_file
xp_dirtree
xp_enum_oledb_providers
xp_enumerrorlogs
xp_fileexist
xp_fixeddrives
xp_get_mapi_default_profile
xp_get_mapi_profiles
xp_getnetname
xp_qv
xp_readerrorlog
xp_regaddmultistring
xp_regdeletekey
xp_regdeletevalue
xp_regenumvalues
xp_regread
xp_regremovemultistring
xp_regwrite
xp_subdirs
Let’s explore some of the previous listed functions/SP’s/[Inline Variables] with more detail …
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- dbcc ind -> Lists all of a table's data and index pages.
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-------------------------------------------------------------------------------------------------------------------------
-- Sample(s):
-- http://blogical.se/blogs/jahlen/archive/2010/08/15/exploring-sql-server-...
-- http://www.sqlskills.com/blogs/paul/post/inside-the-storage-engine-ghost...
-------------------------------------------------------------------------------------------------------------------------
Syntax:
DBCC IND
(
'database_name' | database_id,
table_name,
index_id
)
Sample command: DBCC IND('TEST', ‘TestTable’, -1)
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- dbcc page -> Displays the content of data and index pages.
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-------------------------------------------------------------------------------------------------------------------------
-- Sample(s):
-- http://blogical.se/blogs/jahlen/archive/2010/08/15/exploring-sql-server-...
-- http://www.sqlskills.com/blogs/paul/post/inside-the-storage-engine-ghost...
-------------------------------------------------------------------------------------------------------------------------
Syntax:
DBCC PAGE
(
'database_name' | database_id,
file_number,
page_number,
detail_level
)
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- fn_dblog & fn_dump_dblog
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
----------------------------------------
-- Sample(s):
-- http://www.sqlskills.com/blogs/paul/post/inside-the-storage-engine-ghost...
-- http://sqlserverpedia.com/blog/sql-server-bloggers/monitor-checkpoints-w...
-- http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2004/11/27/37.aspx
-- http://www.novicksoftware.com/udfofweek/Vol1/T-SQL-UDF-Volume-1-Number-2...
-- http://blogs.msdn.com/b/dfurman/rss.aspx?WeblogCategoryID=90714
-- http://www.sqlskills.com/BLOGS/PAUL/post/Finding-out-who-dropped-a-table...
-------------------------------------------------------------------------------------------------------------------------
fn_dblog
Syntax:
::fn_dblog(@StartingLSN, @EndingLSN)
And if you provide Null as the value for both the parameters, then you will get a running record of everything from the start to the end (not a good idea in a production environment but something to play around with in your development/test environment to get an understanding of the output).
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- fn_physlocformatter
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-------------------------------------------------------------------------------------------------------------------------
-- Sample(s):
-- http://www.mssqltips.com/tipprint.asp?tip=1925
-------------------------------------------------------------------------------------------------------------------------
sys.fn_PhysLocFormatter, in conjunction with an undocumented value, %%physloc%%, returns the physical row location in your result rows along with data values from a table
Sample Command:
SELECT sys.fn_PhysLocFormatter (%%physloc%%) AS RID, * FROM SampleTable;
GO
Results:
RID Col1 Col2 Col3 Col4
------------ ------- ------------- ------ ------
(1:1231:1) ABCDE 123 NULL CCCC
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- pwdcompare & pwdencrypt
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-------------------------------------------------------------------------------------------------------------------------
-- Sample(s):
-- http://msmvps.com/blogs/gladchenko/archive/2005/04/06/41083.aspx
-- http://technet.microsoft.com/en-us/library/dd822792.aspx
-------------------------------------------------------------------------------------------------------------------------
PWDENCRYPT (Transact-SQL)
Returns the SQL Server password hash of the input value that uses the current version of the password hashing algorithm
Syntax:
PWDENCRYPT ( 'password' )
PWDCOMPARE (Transact-SQL)
Hashes a password and compares the hash to the hash of an existing password. PWDCOMPARE can be used to search for blank SQL Server login passwords or common weak passwords.
Syntax:
PWDCOMPARE ( 'clear_text_password' , 'password_hash' [ , version ] )
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- sp_columns_rowset
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
This stored procedure returns the complete columns description,
including the length, type, name, and so on.
Syntax:
sp_columns_rowset tbname [, table_schema ] [, column_name]
where
tbname - is the table name. tbname is sysname.
table_schema - is the table schema. table_schema is sysname, with a default of NULL.
column_name - is the column name. column_name is sysname, with a default of NULL.
This is the example:
USE pubs
GO
EXEC sp_columns_rowset 'authors'
GO
SQL
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- sp_msdependencies
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
The sp_MSdependencies stored procedure can be used to check all the objects in the database that are dependant on a table. If your unsuccessfully trying to delete a record that has dependencies because of foreign key that has rules applied to it this can be your answer.
Syntax:
EXEC sp_msdependencies [table name]
Expected Results
oType oObjName oOwner oSequence
-------- ----------- --------- ----------
8 tblAdmin dbo 1
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- sp_msforeachdb
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
The sp_msforeachdb system stored procedure accepts a TSQL string to be executed against each database that resides on your SQL Server instance. This procedure is especially useful when you’re performing database administration and maintenance tasks, such as backup operations. This example loops through each database on the server and prints out the database name:
Syntax:
exec @RETURN_VALUE = sp_MSforeachdb @command1, @replacechar,
@command2, @command3, @precommand, @postcommandWhere:
@RETURN_VALUE - is the return value which will be set by "sp_MSforeachdb"
@command1 - is the first command to be executed by "sp_MSforeachdb" and is defined as nvarchar(2000)
@replacechar - is a character in the command string that will be replaced with the table name being processed (default replacechar is a "?")
@command2 and @command3 are two additional commands that can be run against each database
@precommand - is a nvarchar(2000) parameter that specifies a command to be run prior to processing any database
@postcommand - is also an nvarchar(2000) field used to identify a command to be run after all commands have been processed against all databases.
Get the name of all existing DB´s in your system:
EXEC sp_msforeachdb " select '?' "
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- sp_msforeachtable
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
Syntax:
exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2,
@command3, @whereand, @precommand, @postcommandWhere:
@RETURN_VALUE - is the return value which will be set by "sp_MSforeachtable"
@command1 - is the first command to be executed by "sp_MSforeachtable" and is defined as a nvarchar(2000)
@replacechar - is a character in the command string that will be replaced with the table name being processed (default replacechar is a "?")
@command2 and @command3 are two additional commands that can be run for each table, where @command2 runs after @command1, and @command3 will be run after @command2
@whereand - this parameter can be used to add additional constraints to help identify the rows in the sysobjects table that will be selected, this parameter is also a nvarchar(2000)
@precommand - is a nvarchar(2000) parameter that specifies a command to be run prior to processing any table
@postcommand - is also a nvarchar(2000) field used to identify a command to be run after all commands have been processed against all tables
Space Used of all tables in your database:
EXEC sp_MSforeachtable "EXEC sp_spaceused ?"
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- sp_mshelpcolumns
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
This stored procedure returns the complete schema for a table, including the length, type, name, and whether a column is computed.
Syntax:
sp_MShelpcolumns tablename [, flags] [, orderby] [, flags2]
where
tablename - is the table name. tablename is nvarchar(517).
flags - flags is int, with a default of 0.
orderby - orderby is nvarchar(10), with a default of NULL.
flags - flags2 is int, with a default of 0.
To get the full columns description for the authors table in the pubs database, run:
USE pubs
GO
EXEC sp_MShelpcolumns 'authors'
GO
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- sp_MShelpindex
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
This stored procedure returns information about name, status, fill factor, index columns names, and about used file group for the given table.
Syntax:
sp_MShelpindex tablename [, indexname] [, flags]
where
tablename - is the table name. tablename is nvarchar(517).
indexname - is the index name. indexname is nvarchar(258).
flags - flags is int, with a default of NULL.
To get the indexes description for the authors table in the pubs database, run:
USE pubs
GO
EXEC sp_MShelpindex 'authors'
GO
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- sp_mshelptype
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
This stored procedure returns many useful information about system data types and about user data types.
Syntax:
sp_MShelptype [typename] [, flags]
where
typename - is the type name. typename is nvarchar(517), with a default of NULL.
flags - flags is nvarchar(10), with a default of NULL.
To get information about all built-in and user defined data types in the pubs database, run:
USE pubs
GO
EXEC sp_MShelptype
GO
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- sp_msindexspace
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
This stored procedure returns the size in kb, which the indexes in the particular table use.
Syntax:
sp_MSindexspace tablename [, index_name]
where
tablename - is the table name. tablename is nvarchar(517).
index_name - is the index name. index_name is nvarchar(258),with a default of NULL.
To determine the space used by the indexes from the authors table in the pubs database, run:
USE pubs
GO
EXEC sp_MSindexspace 'authors'
GO
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- sp_mskilldb
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
sp_MSkilldb
This stored procedure sets database to suspect and let dbcc dbrepair to kill it. You should run this sp from the context of the master database. Use it very carefully.
Syntax:
sp_MSkilldb dbname
where
dbname - is the database name. dbname is nvarchar(258).
To kill the pubs database, run:
USE master
GO
EXEC sp_MSkilldb 'pubs'
GO
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- sp_mstablespace
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
sp_MStablespace
This stored procedure returns the number of rows and the space the table and index use.
Syntax:
sp_MStablespace [name] [, id]
where
name - is the table name. name is nvarchar(517).
id - id is int, with a default of NULL.
To determine the space used by the authors table in the pubs database,run:
USE pubs
GO
EXEC sp_MStablespace 'authors'
GO
Here is the result set from my machine:
Rows DataSpaceUsed IndexSpaceUsed
----------- ------------- --------------
23 8 24
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- xp_dirtree
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
Returns a complete listing of all subdirectories on the server; for each subdirectory listed its depth in the directory tree is also returned. If a depth is specified then only subdirectories up to and including the specified depth will be returned. If IncludeFiles is specified (as a 1) then files will also be returned and the result set will include an additional column to indicate if a row is a file or a directory.
Input: @filepath, @depth, @IncludeFiles
Output: subdirectory, depth, file
Note: file is only displayed if @IncludeFiles = 1
Permission If the calling user is ‘sa’ this Stored Procedure is executed in the context of the SQL Server system account. In all other cases the Stored Procedure will be executed in the context of the calling user (i.e. the Stored Procedure will impersonate the user). This impersonation will fail for the case that a SQL login is used and an empty set will be returned.
Syntax: xp_dirtree , ,
Examples: exec xp_dirtree 'c:' - Lists all dirs and sub-dirs on C:
exec xp_dirtree 'c:', 1 - Lists all dirs at the root level of C:
exec xp_dirtree 'c:', 1, 1 - Lists all dirs and files at the root level of C:
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- xp_enumerrorlogs
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
This extended stored procedure returns the list of all error logs with their last change date. To get the list of error logs, run:
EXEC master..xp_enumerrorlogs
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- xp_fileexist
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- Samples: http://www.mssqltips.com/tip.asp?tip=1272
This Stored Procedure can be used to determine whether a particular file exists on disk or
not.
Input:
Result: 0 / Error number
Permission If the calling user is ‘sa’ this Stored Procedure is executed in the context of the SQL Server system account. In all other cases the Stored Procedure will be executed in the context of the calling user (i.e. the Stored Procedure will impersonate the user). This impersonation will fail for the case that a SQL login is used and an empty set will be returned.
Syntax: EXECUTE xp_fileexist [, OUTPUT]
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- xp_fixeddrives
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
Returns a row for each fixed drive containing the drive name and the amount of disk space
available in MB.
Input: no input parameters
Output: (two columns – drive, MB free)
Permission If the calling user is ‘sa’ this Stored Procedure is executed in the context of the SQL Server system account. In all other cases the Stored Procedure will be executed in the context of the calling user (i.e. the Stored Procedure will impersonate the user). This impersonation will fail for the case that a SQL login is used and an empty set will be returned.
Syntax: exec @retval=xp_fileexist
Example: To see the list of drives, run:
EXEC master.xp_fixeddrives
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- xp_getnetname
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
This extended stored procedure returns the WINS name of the SQL Server that you're
connected to.
Input: no input parameters
Output: (optional) one column (Server Net Name)
Else single-row, single-column result set is returned
Syntax: exec @retval=xp_getnetname
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- xp_regread
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
Functionality: This Stored Procedure is used to read from the registry.
Input: @rootkey, @key, @value_name, [,@value] (can have 5 input parameters)
Comments: Error if <2 input parameters 5th param – “no_output” then no output is displayed
No error check if >5 params are given
Permission If the calling user is ‘sa’ this Stored Procedure is executed in the context of the SQL Server system account. The Stored Procedure ensures that other users are only granted access to a limited set of registry values.
Return: 0/ Error number
Syntax: EXECUTE xp_regread [@rootkey=]'rootkey', [@key=]'key' [,@value_name=]'value_name'] [, [@value=]@value OUTPUT]
Example: To read into the variable @test from the value 'TestValue' from the key 'SOFTWARE\Test' from the 'HKEY_LOCAL_MACHINE', run:
DECLARE @test varchar(20)
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Test', @value_name='TestValue', @value=@test
OUTPUT
SELECT @test
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- xp_subdirs
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
This extended stored procedure is used to get the list of folders for the folder named in the xp. In comparison with xp_dirtree, xp_subdirs returns only those directories whose depth = 1.
Example:
EXEC master..xp_subdirs 'C:\'
References:
http://sqlblog.com/blogs/aaron_bertrand/archive/2010/02/08/bad-habits-to...
http://blogical.se/blogs/jahlen/archive/2010/08/15/exploring-sql-server-...
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/identify-proc...
http://blogs.msdn.com/b/lcris/archive/2007/10/31/sql-server-undocumented...
http://vyaskn.tripod.com/sp_readerrorlog_undocumented.htm
http://www.techrepublic.com/blog/datacenter/take-advantage-of-undocument...
http://www.sswug.org/articles/viewarticle.aspx?id=48827
http://www.databasejournal.com/features/mssql/article.php/1441231/SQL-Se...
http://www.dotnetspider.com/resources/35208-Useful-Undocumented-Maintena...
http://www.kodyaz.com/articles/sql-server-exec-sp_msforeachdb-sp_msforea...
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
http://www.mssqltips.com/tip.asp?id=1925
http://www.sql-server-performance.com/articles/dev/extended_stored_proce...
http://www.sqlskills.com/BLOGS/PAUL/category/Undocumented-commands.aspx
http://www.sqlskills.com/blogs/paul/post/SQL-Server-2008-New-(undocumented)-physical-row-locator-function.aspx
Doc: MS_SQL_AGD_IGS_1.5 -> Microsoft SQL Server 2005 Engine Common Criteria Evaluation -> Author: Roger French
Book:
SQL Server 2008 Internals
Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, Conor Cunningham, Adam Machanic, and Ben Nevarez
Microsoft Press
This article was produced/tested/validated over the following SQL Server version:
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
- Clique Iniciar Sessão ou registar-se para colocar comentários