The procedure xp_msver is very useful when looking for system information. It returns a wealth of information about the host operating system -- the SQL version number, language, CPU type, copyright and trademark information, Microsoft Windows version, CPU count and affinity settings, physical memory settings and your product key. This procedure has many input parameters that allow you to filter down the records that are returned. Each parameter is a sysname data type, which accepts the name of one of the records. If any parameters are specified, only the rows specified as a parameter are returned.
No filter specified.
exec xp_msver
Return only Platform and Comments records.
exec xp_msver 'Platform', 'Comments'
xp_get_mapi_profiles
The xp_get_mapi_profiles procedure assists you in configuring SQL Mail. When executed, it will call to Windows via the SQL Mail component of SQL Server and display a list of available MAPI profiles that are configured in Outlook and it specifies which profile is the default profile. If it doesn't display any records, then either Outlook is not configured correctly or SQL Server is not running under a domain account with Outlook profiles configured. In order to use this procedure in SQL Server 2005 or SQL Server 2008, you must enable the "SQL Mail XPs" option in the Surface Area Configuration tool or within the sp_configure procedure.
exec xp_get_mapi_profiles
xp_subdirs
The xp_subdirs procedure displays a subset of the information avaialble through xp_dirtree. Xp_subdirs will display all the subfolders in a given folder. It can be very handy when you are building a directory tree within a table dynamically and you do not want to worry about the extra parameters of the xp_dirtree procedure.
exec xp_subdirs 'd:mssql'
xp_test_mapi_profiles
The procedure xp_test_mapi_profiles is another undocumented stored procedure that is very useful when you are setting up SQL Mail. It will start, then stop, a MAPI session to ensure that MAPI is configured correctly and working within the confines of Microsoft SQL Server. I should note that it does not verify the mail server configuration within the MAPI client (Outlook) nor does it send a test message.
The procedure accepts a single input parameter. That parameter is the name of the MAPI profile you wish to test. Like the xp_get_mapi_profiles procedure, for this stored procedure to function in SQL Server 2005 and SQL Server 2008, you must enable the "SQL Mail XPs" option in the Surface Area Configuration tool or within the sp_configure procedure.
When working with the SQL Mail stored procedures, be aware that SQL Mail is still slated for removal from the Microsoft SQL Server platform. That means the procedures sp_get_mapi_profiles and xp_test_mapi_profiles are slated for removal, as they are part of the SQL Mail subsystem. You should do all mail work on SQL Server 2005 and later using Database Mail instead of SQL Mail to ensure code portability with future versions of SQL Server. Microsoft initially slated SQL Mail for removal in SQL Server 2008, however, based on its inclusion in the current beta release, its future in SQL Server 2008 is unknown.
xp_unpackcab
Along with the xp_makecab procedure comes the xp_unpackcab extended stored procedure, and it does just what it says: It extracts files from cab files. The first paramater is the cab file, the second is the path you want to extract to and the third is verbose logging. A fourth paramater lets you specify the "extract to" file name.
exec xp_unpackcab 'c:test.cab', 'c:temp', 1
While this is not intended to be a complete list of the undocumented stored procedures in SQL Server, it does provide a reference point for many of these procedures with the hope of making the lives of the SQL Server administrators easier. Remember, you should never count on these procedures surviving from one SQL Server version to the next, nor should you expect their code base to remain the same between versions. That said, go code and enjoy.
All information provided about Microsoft SQL Server 2008 (Katmai) is based on beta edition 10.0.1019 of the software and is subject to change without notice.