Transact SQL – Extended Stored Procedures

Background

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

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

 

Functions

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

 

 

 

Lab

xp_create_subdir


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

Metadata

List of All Functions

To get a list of all Extended Stored Procedures


exec master.dbo.sp_helpextendedproc 

 

Help For specific Extended Stored Procedure

Syntax


EXEC sp_helpextendedproc [function]

Example


EXEC sp_helpextendedproc sp_server_diagnostics

Output

extendedstoredprocedure-serverdiagnostics

Explanation

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

sp_server_diagnostics

Syntax


exec sp_server_diagnostics

Output

extendedstoredprocedure-serverdiagnostics-output-201612140148pm

One thought on “Transact SQL – Extended Stored Procedures

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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