The undocumented SQL Server extended procedures

Views: 1.139
Reading Time: 6 minutes

Hello everybody!
Good night!

In this post, I will comment on some SQL Server extended procedures that are very useful. Until this post, I didn't find any official Microsoft documentation about it so I decided to comment on it, besides demonstrating some examples.

An extended procedure (XP) is a link to a dynamic library (DLL) programmed using the SQL Server Open Data Services API and is used to extend SQL Server functionality. You can use either of the various procedures that come with SQL Server or create your own extended procedures using the C or C ++ programming languages.

Currently, there is not much incentive to use these procedures as they have been deprecated and should be removed in some future version of SQL Server. The most viable alternative to replacing XP Procedures is to use procedures SQL CLR.

However, I could not miss the opportunity to comment on these SP's, which have always helped me on several occasions and I still see many routines using them.

To view the list of extended procedures present in your installation, simply execute the command below:

XP Procedures - sp_helpextendedproc

sp_MSgetversion

View information

This procedure can be used to return the current version of SQL Server.

Example of use:

XP Procedures - sp_MSgetversion

Alternative solution:

xp_dirtree

View information
This procedure can be used to list all folders and subfolders (recursively) from a given directory.

Example of use:

XP Procedures - xp_dirtree

xp_subdirs

View information
This procedure can be used to return (nonrecursive) directories from an informed directory. Compared to the xp_dirtree procedure, it returns only lines where node depth = 1.

Example of use:

XP Procedures - xp_subdirs

xp_enum_oledb_providers

View information
This procedure can be used to return all available OLE DB providers. It returns the provider name, parse name, and provider description.

Example of use:

XP Procedures - xp_enum_oledb_providers

xp_enumerrorlogs

View information
This procedure can be used to return all error log files and change dates.

Example of use:

Procedures XP - xp_enumerrorlogs

xp_readerrorlog

View information
This procedure can be used to read the SQL Server error log file. As an optional parameter, you can enter the file #. If not informed, will display the current log.

Parameter List:
1th Parameter: Number of log file you want to view (0 = Current, 1 = Archive #1, 2 = Archive #2)
2th Parameter: Log File Type (1 or NULL for Error Log, 2 for SQL Agent Log)
3th parameter: The string you would like to fetch
4th parameter: Another string to refine the already filtered result
5th Parameter: Log Start Date
6th Parameter: Log End Date
7th parameter: Sorting the results ('asc' = ascending, 'desc' = descending)

Example of use:

XP Procedures - xp_readerrorlog

xp_logevent

View information
This procedure can be used to write event messages to the operating system log.

Example of use:

XP Procedures - xp_logevent

xp_enumgroups

View information
This procedure can be used to return all machine groups and their descriptions.

Example of use:

XP Procedures - xp_enumgroups

xp_fileexist

View information
This procedure can be used to return all machine groups and their descriptions.

Example of use:

XP Procedures - xp_fileexist

xp_create_subdir

View information
This procedure can be used to create directories on your SQL Server server.

Example of use:

XP Procedures - xp_create_subdir

xp_delete_file

View information
This procedure can be used to delete backup or report files. Although the name looks like it can erase any file, it is restricted to backup (BAK) and reporting (RPT) files only.

Example of use:

Procedures XP - sp_delete_file

xp_fixeddrives

View information
This procedure can be used to list all physical disk drives or mapped drives, informing the free space.

Example of use:

XP Procedures - xp_fixeddrives

xp_availablemedia

View information
This procedure can be used to list all backup drives attached to the server. If you do not have any drives, SP will return all disk drives.

Example of use:

Procedures XP - xp_availablemedia

xp_getnetname

View information
This procedure can be used to return the WINS name of the machine where your session is connected.

Example of use:

XP Procedures - xp_getnetname

xp_regdeletekey

View information
This procedure can be used to remove a key in the Windows registry from SQL Server Setup. Be very careful when using this procedure as it may damage your Windows installation on this server.

Example of use:

Procedures XP - xp_regdeletekey_1

Procedures XP - xp_regdeletekey_2

Procedures XP - xp_regdeletekey_3

xp_regdeletevalue

View information
This procedure can be used to remove a specific value from a key in the Windows registry from SQL Server Setup. Be very careful when using this procedure as it may damage your Windows installation on this server.

Example of use:

Procedures XP - xp_regdeletevalue

Procedures XP - xp_regdeletevalue_2

Procedures XP - xp_regdeletevalue_3

xp_regread

View information
This procedure can be used to look up a specific value of a key in the Windows Registry of SQL Server Setup.

Example of use:

XP Procedures - xp_regread_1

XP Procedures - xp_regread_2

xp_regwrite

View information
This procedure can be used to write a specific value of a key to the Windows Registry of the SQL Server installation. Be very careful when using this procedure as it may damage your Windows installation on this server.

Example of use:

XP Procedures - xp_regwrite_1

XP Procedures - xp_regwrite_2

xp_cmdshell

View information
Allows you to run any Command Prompt (DOS) command on the server. Be very careful when using this procedure as it may damage your Windows installation on this server.

To use this command, you must enable this feature, as misuse can seriously damage your installation:

Example of use:

XP Procedures - xp_cmdshell

XP Procedures - xp_cmdshell_2

sql server clr xp extended procedures native dll

sql server clr xp extended procedures native dll