Undocumented SQL Server 2014 extended stored procedures (Part 1)
In this article, you can find the description of some useful undocumented SQL Server 2014
extended stored procedures.
sp_MSgetversion
This extended stored procedure can be used to get the current version of Microsoft SQL Server.
Syntax:
EXECUTE sp_MSgetversion
For example, to get the current SQL Server version, you can run:
EXEC master..sp_MSgetversion
Note. A more common way to retrieve the current SQL Server version (this way provides more
information) is to use the following SELECT statement:
SELECT @@version
xp_regaddmultistring
This extended stored procedure is used to create a multi-string registry entry or add a string
to an existing multi-string key. A multi-string registry entry contains multiple string values
in a single registry entry.
Syntax:
EXECUTE xp_regaddmultistring [@rootkey=]’rootkey’,
[@key=]’key’,
[@value_name=]’value_name’,
[@value=]’value’
For example, to add the ‘Test’ string to the ‘TestValue’ value, in the
"HKEY_LOCAL_MACHINESoftwareTest" folder, run:
EXECUTE master..xp_regaddmultistring
@rootkey=’HKEY_LOCAL_MACHINE’,
@key=’SOFTWARETest’,
@value_name=’TestValue’,
@value=’Test’
xp_regremovemultistring
This extended stored procedure is used to remove a string from a multi-string registry entry.
Syntax:
EXECUTE xp_regremovemultistring [@rootkey=]’rootkey’,
[@key=]’key’,
[@value_name=]’value_name’,
[@value=]’value’
For example, to remove the ‘Test’ string from the ‘TestValue’ value, in the
"HKEY_LOCAL_MACHINESoftwareTest" folder, run:
EXECUTE master..xp_regremovemultistring
@rootkey=’HKEY_LOCAL_MACHINE’,
@key=’SOFTWARETest’,
@value_name=’TestValue’,
@value=’Test’
xp_regdeletekey
This extended stored procedure can be used to delete an entire key from the registry. You should
use it very carefully.
Syntax:
EXECUTE xp_regdeletekey [@rootkey=]’rootkey’,
[@key=]’key’
For example, to delete the ‘SOFTWARESQL’ key from ‘HKEY_LOCAL_MACHINE’, run:
EXEC master..xp_regdeletekey
@rootkey=’HKEY_LOCAL_MACHINE’,
@key=’SOFTWARESQL’
xp_regdeletevalue
This extended stored procedure can be used to delete a particular value for a key in the registry.
You should use it very carefully.
Syntax:
EXECUTE xp_regdeletevalue [@rootkey=]’rootkey’,
[@key=]’key’,
[@value_name=]’value_name’
For example, to delete the ‘TestValue’ value for the ‘SOFTWARETest’ key from
‘HKEY_LOCAL_MACHINE’, run:
EXEC master..xp_regdeletevalue
@rootkey=’HKEY_LOCAL_MACHINE’,
@key=’SOFTWARETest’,
@value_name=’TestValue’
xp_regread
This extended stored procedure is used to read from the registry.
Syntax:
EXECUTE xp_regread [@rootkey=]’rootkey’,
[@key=]’key’
[, [@value_name=]’value_name’]
[, [@value=]@value OUTPUT]
For example, to read into the @test variable from the ‘TestValue’ value from the
"HKEY_LOCAL_MACHINESoftwareTest" folder, run:
DECLARE @test varchar(20)
EXEC master..xp_regread @rootkey=’HKEY_LOCAL_MACHINE’,
@key=’SOFTWARETest’,
@value_name=’TestValue’,
@value=@test OUTPUT
SELECT @test
xp_regwrite
This extended stored procedure is used to write to the registry.
Syntax:
EXECUTE xp_regwrite [@rootkey=]’rootkey’,
[@key=]’key’,
[@value_name=]’value_name’,
[@type=]’type’,
[@value=]’value’
For example, to write the ‘Test’ variable to the ‘TestValue’ value, in the
"HKEY_LOCAL_MACHINESoftwareTest" folder, run:
EXEC master..xp_regwrite
@rootkey=’HKEY_LOCAL_MACHINE’,
@key=’SOFTWARETest’,
@value_name=’TestValue’,
@type=’REG_SZ’,
@value=’Test’
xp_regenumkeys
You can use this extended stored procedure to determine what keys are available in a given registry folder.
Syntax:
EXECUTE xp_regenumkeys [@rootkey=]’rootkey’,
[@key=]’key’
For example, to enumerate the "HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server"
folder, run:
EXEC master..xp_regenumkeys
@rootkey=’HKEY_LOCAL_MACHINE’,
@key=’SOFTWAREMicrosoftMicrosoft SQL Server’
xp_regenumvalues
You can use this extended stored procedure to display the values associated with a specific key.
Syntax:
EXECUTE xp_regenumvalues [@rootkey=]’rootkey’,
[@key=]’key’
This example displays the key values under "HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server120":
EXEC master..xp_regenumvalues
@rootkey=’HKEY_LOCAL_MACHINE’,
@key=’SOFTWAREMicrosoftMicrosoft SQL Server120′