exec xp_getfiledetails ‘c:\filetoload.csv’
Two procedures, sp_MSforeachdb and sp_MSforeachtable, are wrappers
around a cursor. They allow you to execute T-SQL code against each
database on your SQL Server and each table within the current database,
respectively. You cannot, however, use an sp_MSforeachtable command
within an sp_MSforeachdb command in SQL 2000 and prior. The cursor name
that was used within those procedures was the same (hCForEach) and would
therefore return an error saying that the cursor name was already in use
for each execution of the sp_MSforeachtable. In SQL Server 2005,
Microsoft resolved this issue. In order to “next” the command, you must
tell one of the procedures it will be using a different replacement
character other than the default question mark. I change the replacement
character in the database command because it’s easier.
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.
Return all drives.
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_dirtree ‘d:\mssql\’, 2
The xp_dirtree procedure allows you to view the folder tree and/or file
list beneath a folder. This procedure has several parameters that
control how deep the procedure searches and whether it returns files and
folders or folders only. The first parameter establishes the folder to
look in. (Recommendation: Do not run this procedure against the root of
the drive that Windows is installed on because it will take some time to
generate the tree and return the data.) The second parameter limits the
number of recursive levels that the procedure will dig through. The
default is zero or all levels. The third parameter tells the procedure
to include files. The default is zero or folders only, a value of 1
includes files in the result set. Specifying a third value not equal to
zero will add an additional column to the output called file which is a
bit field showing the entry in a folder or file.
The stored procedure sp_readerrorlog actually comes in two forms. Each
works the same; one is simply a wrapper for the second. The wrapper
stored procedure is sp_readerrorlog and it calls xp_readerrorlog. Both
have four input parameters, but only the first two are useful to us. The
first parameter establishes the file number that you wish to view. The
second is the log to view (1 or null for ERRORLOG, 2 for SQL Agent Log).
This allows you to view your error logs quickly and easily instead of
having to look at the bloated log viewer that now comes with SQL Server
2005 and SQL 2008.
Get the full directory tree.
DECLARE @file_exists intexec xp_fileexist ‘c:\importfile.csv’,
@file_exists OUTPUTSELECT @file_exists
Return only Platform and Comments records.
exec xp_subdirs ‘d:\mssql’
View the Prior SQL Agent Log file.
Use the xp_delete_file stored procedure introduced in SQL Server 2005
to delete files from SQL Server’s hard drive or a network share from
exec xp_makecab ‘c:\test.cab’, ‘mszip’, 1, ‘c:\test.txt’ ,
Print each database on the current server.
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.
exec xp_dirtree ‘d:\mssql\’
Back in SQL Server 2000, Microsoft gave us the ability to compress OS
files directly from T-SQL without having to shell out to DOS via
xp_cmdshell and run third-party software, like pkzip or winzip. That
command was xp_makecab. It allows you to specify a list of files you
want to compress as well as the cab file you want to put them in. It
even lets you select default compression, MSZIP compression (akin to the
.zip file format) or no compression. The first parameter gives the path
to the cab file in which you want to create or add files to. The second
parameter is the compression level. The third parameter applies if you
want to use verbose logging. Starting with the fourth parameter and on
down are the names of the files you want to compress. In my testing, I
was able to pass 45 file names to be compressed to the extended stored
procedure, which means that it is a very flexible solution to your data
Get the first three levels of the directory tree, including files.
exec xp_create_subdir ‘c:\MSSQL\Data’
The procedure xp_fixeddrives is one of the most useful procedures. It
presents a list of all drive letters and the amount of free space each
drive has. The parameter has a single optional input parameter that can
filter the results by drive type. A value of 3 will return all mass
storage devices (CD-ROM, DVD, etc.); a value of 4 will return the hard
drives; while a value of 2 will return removable media (USB thumb
drives, flash drives, etc.).
exec sp_readerrorlog 0, 1
exec xp_fixeddrives 2
sp_readerrorlog / xp_readerrorlog
exec xp_msver ‘Platform’, ‘Comments’
Using the parameter.
Without the parameter.
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
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.
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
exec sp_MSforeachdb ‘use [@] exec sp_MSforeachtable
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_MSforeachdb / sp_MSforeachtable
exec sp_MSforeachtable ‘print ”?”’
DECLARE @machinename sysnameexec xp_getnetname @machinename
Return hard drives only.
Introduced in SQL Server 2005, the xp_create_subdir stored procedure
is very handy because you can use it to create folders on SQL Server’s
hard drive or on a network share from within T-SQL.
This SQL Server stored procedure will tell you some basic authentication
information about the user executing it. It tells you the authentication
method (Windows versus SQL Login), the default domain of the server, the
audit level, as well as some internal separator information.
Print each table on the current server.
View the current SQL ERRORLOG file.
exec sp_MSforeachdb ‘print ”?”’
With the parameter.
exec xp_fileexist ‘c:\importfile.csv’
exec xp_unpackcab ‘c:\test.cab’, ‘c:\temp\’, 1
This SQL Server stored procedure, xp_fileexist, is used to determine if
a file exists on SQL Server’s hard drive or on a network share. It is
extremely useful in stored procedures that load data from flat files. It
allows you to check and see if the file exists before attempting to
blindly load the file. The procedure has two parameters. Use the first
parameter to determine if the file or folder you want exists. The second
is an output parameter, which when specified, returns a 1 or 0 if the
file exists or does not.
The chart below shows that while many of the procedures have been
carried through from one version of Microsoft SQL Server to another, new
stored procedures have been introduced, and some have been removed from
the install package. Most, if not all, of the procedures require the
user to be a member of the sysadmin fixed server role in order to
execute the procedures. The stored procedures that interact with the
file system also require that the user executing the procedure (as well
as SQL Server’s service account) have access to the file/folder.
Get the first two levels of the directory tree.
The procedure xp_getfiledetails is another extremely useful procedure,
which was last available in SQL Server 2000. This procedure returns
size, date and attribute information about the file specified, including
date and times created, accessed and modified.
Microsoft included several hundred stored procedures in the various
versions of Microsoft SQL Server and it has documented a good percentage
of them. But many stored procedures remain undocumented. Some are used
within the Enterprise Manager GUI in SQL 2000 and were not intended to
be used by other processes. Microsoft has slated some of these stored
procedures to be removed (or they have been removed) from future
versions of SQL Server. While these stored procedures can be very useful
and save you lots of time, they can be changed at any time in their
function or they can simply be removed.
exec xp_dirtree ‘d:\mssql\’, 3, 1
The procedure xp_getnetname returns the name of the physical machine
where Microsoft SQL Server is installed. You can have the machine name
returned as a record set or as a variable.
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.
Print each table name in the current database.
Microsoft removed this handy little procedure called
sp_executeresultset from SQL Server in SQL Server 2005. It allows you
to generate dynamic SQL code on the fly by using a SELECT query. Then,
the resulting SQL commands will be executed against the database. It
permits you to create a single piece of code that can, in a single step,
find the number of records in every table in your database (as the
example shows). This is an undocumented stored procedure and there is no
way of knowing why it was removed. But, alas, this handy utility is
exec sp_execresultset ‘SELECT ”SELECT ””” + name + ”””,
count(*) FROM ” + namefrom sysobjectswhere xtype = ”U”’
exec sp_readerrorlog 1, 2
No filter specified.
Procedure NameSQL 2000SQL 2005SQL 2008sp_executeresultsetX
Without the parameter.