Undocumented SQL Server Extended Procedures

Views: 2.057 views

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:

EXEC master.dbo.sp_helpextendedproc

1	EXEC master.dbo.sp_helpextendedproc

sp_MSgetversion

View information

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

Example of use:

EXEC master..sp_MSgetversion

1	EXEC master..sp_MSgetversion

Alternative solution:

SELECT @@version

1	SELECT @@version

xp_dirtree

View information

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

Example of use:

EXEC master..xp_dirtree 'C:\'

1	EXEC master..xp_dirtree 'C:\'

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:

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

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

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:

EXEC master..xp_enum_oledb_providers

1	EXEC master..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:

EXEC master.dbo.xp_enumerrorlogs

1	EXEC master.dbo.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:

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'

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'

xp_logevent

View information

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

Example of use:

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

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

xp_enumgroups

View information

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

Example of use:

EXEC master..xp_enumgroups

1	EXEC master..xp_enumgroups

xp_fileexist

View information

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

Example of use:

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

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

xp_create_subdir

View information

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

Example of use:

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

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

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:

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)

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)

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:

EXEC master.dbo.xp_fixeddrives

1	EXEC master.dbo.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:

EXEC master.dbo.xp_availablemedia

1	EXEC master.dbo.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:

EXEC master..xp_getnetname

1	EXEC master..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:

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

EXEC master.dbo.xp_regdeletekey

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = 'SOFTWARE\Teste_Dirceu'

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:

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

EXEC master.dbo.xp_regdeletevalue

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = 'SOFTWARE\Teste_Dirceu',

@value_name = 'Oracle Database'

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:

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

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

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:

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'

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'

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:

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

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1 -- 0 para desativar

RECONFIGURE

Example of use:

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

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

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

The undocumented SQL Server extended procedures

sp_MSgetversion

xp_dirtree

xp_subdirs

xp_enum_oledb_providers

xp_enumerrorlogs

xp_readerrorlog

xp_logevent

xp_enumgroups

xp_fileexist

xp_create_subdir

xp_delete_file

xp_fixeddrives

xp_availablemedia

xp_getnetname

xp_regdeletekey

xp_regdeletevalue

xp_regread

xp_regwrite

xp_cmdshell

sql server clr xp extended procedures native dll

You may also like ...

Leave a Comment Cancel reply

sp_MSgetversion

xp_dirtree

xp_subdirs

xp_enum_oledb_providers

xp_enumerrorlogs

xp_readerrorlog

xp_logevent

xp_enumgroups

xp_fileexist

xp_create_subdir

xp_delete_file

xp_fixeddrives

xp_availablemedia

xp_getnetname

xp_regdeletekey

xp_regdeletevalue

xp_regread

xp_regwrite

xp_cmdshell

sql server clr xp extended procedures native dll

You may also like ...

Generating Reverse Engineering (Creation DDL) from Oracle Database Users, Tablespaces, Roles, Jobs, and Profiles

Microsoft MCSE 70-776 (Performing Big Data Engineering on Microsoft Cloud Services) certification test for free (beta) through 08 / 09 / 2017

SQL Server - How to create a hidden friend draw using Transact-SQL

Leave a Comment Cancel reply