General Extended Procedures

Extended stored procedures enable significant functionality that does not exist natively inside SQL Server. Extended stored procedures is a misnomer that started several versions ago when they were first introduced. These are not stored procedures at all. An extended stored procedure is actually a function in a dynaic link library (dll). These dlls are constructed to follow an API specified by Microsoft. This enables third party developers to create enhanced functionality that would be extremely difficult or impossible to implement using Transact SQL. An example of this functionality would be a dll that provides a series of financial functions such as amortization, depreciation, net asset value, etc. Once a dll is created to Microsoft's specification, the system administrator can add one or more functions in the dll for use inside SQL Server. Once linked into SQL Server, it is called just like any other stored procedure.

Only the sa or a member of the systemadmin role can add or remove an extended stored procedure.  This authority can not be delegated.

The extended stored procedures that ship with SQL Server provide many file handling and NT features that are not native to SQL Server. These procedures offer some very powerful functionality. The list of extended procedures is relatively long. Unfortunately, Microsoft has not seen fit to document very many of these procedures. Many of the parameters have been found by trial and error.

I’ve attempted to provide as extensive of a listing as possible. If you encounter an extended stored procedure that is not on this list, you can attempt to determine its function and parameters by trial and error. (Many of them will return a usage statement.) Please note that some of these procedures can potentially make very permanent changes to your system that are not readily apparent. If you need to investigate an extended stored procedure, please do so on an isolated test system.

You also need to be aware of the security implications of these procedures. They will allow access to many system resources with much greater security permissions that the normal user should have. They will also provide a significant amount of information about your server and NT network as well.  Gaurd the permissions on these extended stored procedures very carefully.  In highly secure environments, you may even need to remove many of them.  Keep in mind that removing some of these procedures will cause loss of some functionality within the SQL Server client tools.

This list is an ongoing project and is by no means complete.  I have managed to document some of the extended procs, but not all of them.  After blowing up several systems and crashing SQL Server several times, some of these extended procs still elude documentation and usage.  If you have documented any of the procedures that are as yet undocumented on this list, please let me know and I will add the information.

xp_availablemedia

Shows the writable media available on the machine. Only those drives with media loaded appear.

xp_check_query_results

Verifies the results of a query with what is expected in terms of column names and datatypes

Parameters, output, and usage

xp_cmdshell

Opens an NT command shell where you can execute any NT command. This is a very powerful procedure that can pose a serious security risk. The command shell runs with the permissions of the account SQL Server is logged in as and not with the credentials of the user. Make sure you carefully evaluate the people that have execute permission for this procedure.

Parameters, output, and usage

SQL Server 7.0 Extended Stored Procedure Reference 1 2 3 4 5 6 7 8 9 10 11 12