Transact SQL – Extended Stored Procedures


Via Transact SQL, SQL Server is one of the more scriptable database platforms.

Historically, some of that management is provided through Extended Stored Procedures.



File Management

Here is a list some of the functionalities that are useful for managing the OS File System.


Function Usage
xp_availablemedia How much storage is available on each drive
xp_create_subdir Create Subdirectory
xp_delete_file Remove Subdirectory
xp_dirtree List Directory Tree
xp_fileexist Checks whether file exist
xp_fixeddrives List logical drives on host
 xp_subdirs List Sub Folders under the given folder






exec master.dbo.xp_create_subdir 'E:\Microsoft\SQLSERVER\MSSQL\DATA\'


List of All Functions

To get a list of all Extended Stored Procedures

exec master.dbo.sp_helpextendedproc 


Help For specific Extended Stored Procedure


EXEC sp_helpextendedproc [function]


EXEC sp_helpextendedproc sp_server_diagnostics




The output above basically means that the sp_server_diagnostic is provided natively by Microsoft.

If by 3rd party, it would say so.



Other Functions

Here are other functions arranged by management class.

Management Class Functions
Active X Object Management sp_OACreate, sp_OADestroy, sp_OAGetErrorInfo, sp_OAGetProperty, sp_OAMethod, sp_OASetProperty, sp_OAStop
Prepared Statements sp_prepare, sp_prepexec, sp_prepexecrpc, sp_unprepare
Server Diagnostics sp_server_diagnostics
Server side Tracing sp_trace_create, sp_trace_generateevent, sp_trace_setevent, sp_trace_setfilter, sp_trace_setstatus
Local Computer System Management xp_enumgroups
SQL Server Agent Job Management xp_sqlagent_enum_jobs, xp_sqlagent_is_starting, xp_sqlagent_monitor, xp_sqlagent_notify, xp_sqlagent_param
SQL Mail xp_sysmail_activate, xp_sysmail_attachment_load, xp_sysmail_format_query
Registry xp_regaddmultistring, xp_regdeletekey, xp_regdeletevalue, xp_regenumkeys, xp_regenumvalues, xp_regread, xp_regremovemultistring, xp_regwrite



Server Diagnostics



exec sp_server_diagnostics



One thought on “Transact SQL – Extended Stored Procedures

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s