Hey guys!
Goodnight!

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 this, so I decided to comment on it, in addition to 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 the functionalities of SQL Server. You can use 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 for replacing XP Procedures is the use of procedures SQL CLR.

However, I couldn't 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 run the command below:

EXEC master.dbo.sp_helpextendedproc

XP Procedures - sp_helpextendedproc
XP Procedures - sp_helpextendedproc

sp_MSgetversion

View information

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

Usage example:

EXEC master..sp_MSgetversion

Procedures XP - sp_MSgetversion
Procedures XP - sp_MSgetversion

Workaround:

SELECT @@version

xp_dirtree

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

Usage example:

EXEC master..xp_dirtree 'C:\'

Procedures XP - xp_dirtree
XP Procedures - xp_dirtree

xp_subdirs

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

Usage example:

EXEC master.dbo.xp_subdirs 'C:\Users\'

Procedures XP - xp_subdirs
Procedures XP - 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.

Usage example:

EXEC master..xp_enum_oledb_providers

Procedures XP - xp_enum_oledb_providers
Procedures XP - xp_enum_oledb_providers

xp_enumerrorlogs

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

Usage example:

EXEC master.dbo.xp_enumerrorlogs

Procedures XP - xp_enumerrorlogs
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 # of the file. If not informed, it will show the current log.

Parameter list:
1st parameter: Number of the log file you want to view (0 = Current, 1 = Archive #1, 2 = Archive #2)
2nd parameter: Log file type (1 or NULL for error log, 2 for SQL Agent log)
3rd parameter: The string you would like to search for
4th parameter: Another string to refine the already filtered result
5th parameter: Log start date
6th parameter: Log end date
7th parameter: Ordering of results (‘asc’ = ascending, ‘desc’ = descending)

Usage example:

EXEC master..xp_readerrorlog -- Log atual
EXEC master..xp_readerrorlog 1 -- Log #1
EXEC master..xp_readerrorlog 0, 1, N'Starting up', 'master', NULL, NULL, N'asc'

Procedures XP - xp_readerrorlog
Procedures XP - xp_readerrorlog

xp_logevent

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

Usage example:

EXEC master..xp_logevent 50001, 'Teste de mensagem no Event Viewer', 'INFORMATIONAL' -- INFORMATIONAL, WARNING, ERROR

Procedures XP - xp_logevent
Procedures XP - xp_logevent

xp_enumgroups

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

Usage example:

EXEC master..xp_enumgroups

Procedures XP - xp_enumgroups
XP Procedures - xp_enumgroups

xp_fileexist

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

Usage example:

EXEC master.dbo.xp_fileexist 'C:\Teste.html'

Procedures XP - xp_fileexist
XP Procedures - xp_fileexist

xp_create_subdir

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

Usage example:

EXEC master.dbo.xp_create_subdir 'C:\Dirceu\Teste'

Procedures XP - xp_create_subdir
Procedures XP - xp_create_subdir

xp_delete_file

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

Usage example:

EXEC master.dbo.xp_delete_file
    0, -- (0 = Arquivo de backup, 1 = Arquivo de report)
    'C:\Backups\', -- diretório (terminando com "\")
    'bak', -- extensão do arquivo (sem o ".")
    '2015-08-31', -- data de corte para deletar
    1 -- flag para subdiretórios (1 = incluir sub-diretórios, 0 = não incluir)

Procedures XP - sp_delete_file
Procedures XP - sp_delete_file

xp_fixeddrives

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

Usage example:

EXEC master.dbo.xp_fixeddrives

Procedures XP - xp_fixeddrives
Procedures XP - xp_fixeddrives

xp_availablemedia

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

Usage example:

EXEC master.dbo.xp_availablemedia

Procedures XP - xp_availablemedia
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.

Usage example:

EXEC master..xp_getnetname

Procedures XP - xp_getnetname
Procedures XP - xp_getnetname

xp_regdeletekey

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

Usage example:

EXEC master.dbo.xp_regdeletekey
    @rootkey = 'HKEY_LOCAL_MACHINE',  
    @key = 'SOFTWARE\Teste_Dirceu'

Procedures XP - xp_regdeletekey_1
Procedures XP - xp_regdeletekey_1

Procedures XP - xp_regdeletekey_2
Procedures XP - xp_regdeletekey_2

Procedures XP - xp_regdeletekey_3
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 of the SQL Server installation. Be very careful when using this procedure, as it may damage the Windows installation on that server.

Usage example:

EXEC master.dbo.xp_regdeletevalue
     @rootkey = 'HKEY_LOCAL_MACHINE',
     @key = 'SOFTWARE\Teste_Dirceu',
     @value_name = 'Oracle Database'

Procedures XP - xp_regdeletevalue
Procedures XP - xp_regdeletevalue

Procedures XP - xp_regdeletevalue_2
Procedures XP - xp_regdeletevalue_2

Procedures XP - xp_regdeletevalue_3
Procedures XP - xp_regdeletevalue_3

xp_regread

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

Usage example:

DECLARE @Retorno VARCHAR(4000)

EXEC master..xp_regread 
	@rootkey='HKEY_LOCAL_MACHINE',
	@key='SOFTWARE\Teste_Dirceu',
	@value_name = 'Campeão Carioca 2015',
	@value = @Retorno OUTPUT
	
SELECT @Retorno

Procedures XP - xp_regread_1
Procedures XP - xp_regread_1

Procedures XP - xp_regread_2
Procedures XP - 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 the Windows installation on that server.

Usage example:

EXEC master..xp_regwrite
     @rootkey = 'HKEY_LOCAL_MACHINE',
     @key = 'SOFTWARE\Teste_Dirceu',
     @value_name = 'Flamengo na Copa do Brasil 2015',
     @type = 'REG_SZ',
     @value = 'Eliminado pelo Vasco'

Procedures XP - xp_regwrite_1
Procedures XP - xp_regwrite_1

Procedures XP - xp_regwrite_2
Procedures XP - 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 the Windows installation on that server.

To use this command, it is necessary to enable this feature, as if misused, it can seriously damage your installation:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1 -- 0 para desativar
GO
RECONFIGURE
GO

Usage example:

EXEC master.dbo.xp_cmdshell 'dir C:\'
EXEC master.dbo.xp_cmdshell 'ping localhost'

XP Procedures - xp_cmdshell
XP Procedures - xp_cmdshell

XP Procedures - xp_cmdshell_2
XP Procedures - xp_cmdshell_2

sql server clr xp extended procedures native dll

sql server clr xp extended procedures native dll