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.
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 Servers service account) have access to the file/folder.
| Procedure Name |
SQL 2000 |
SQL 2005 |
SQL 2008 |
| sp_executeresultset |
X |
|
|
| sp_MSforeachdb |
X |
X |
X |
| sp_MSforeachtable |
X |
X |
X |
| sp_readerrorlog |
X |
X |
X |
| xp_create_subdir |
|
X |
X |
| Xp_delete_file |
|
X |
X |
| xp_dirtree |
X |
X |
X |
| xp_fileexist |
X |
X |
X |
| xp_fixeddrives |
X |
X |
X |
| xp_getfiledetails |
X |
|
|
| xp_getnetname |
X |
X |
X |
| xp_loginconfig |
X |
X |
X |
| xp_makecab |
X |
|
|
| xp_msver |
X |
X |
X |
| xp_get_mapi_profiles |
X |
X |
X |
| xp_subdirs |
X |
X |
X |
| xp_test_mapi_profile |
X |
X |
X |
| xp_unpackcab |
X |
|
|
sp_executeresultset
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 gone.
exec sp_execresultset SELECT SELECT + name + ,
count(*) FROM + name
from sysobjects
where xtype = U
sp_MSforeachdb / sp_MSforeachtable
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 its easier.
Print each table name in the current database.
exec sp_MSforeachtable print
Print each database on the current server.
exec sp_MSforeachdb print
Print each table on the current server.
exec sp_MSforeachdb use [@] exec sp_MSforeachtable print
@. , @
sp_readerrorlog / xp_readerrorlog
The stored procedure sp_readerrorlog actually comes in two