Demo Script
-- Querying the registry from SQL Server
-- xp_regread / xp_instance_regread
-- these procedures query the registry and return the specified values
/*
EXECUTE xp_regread / xp_instance_regread
@rootkey = 'rootkey',
[@key=] 'key'
[,@value_name=] 'value_name'
[,@value=] @value OUTPUT
*/
exec xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer'
, @value_name = 'BackupDirectory';
go
exec xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.DB02\MSSQLServer'
, @value_name = 'BackupDirectory';
go
exec xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.DB03\MSSQLServer'
, @value_name = 'BackupDirectory';
go
:connect sql2017\db01
exec xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'SOFTWARE\Microsoft\MSSQLSERVER\MSSQLServer'
, @value_name = 'BackupDirectory';
go
:connect sql2017\db02
exec xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'SOFTWARE\Microsoft\MSSQLSERVER\MSSQLServer'
, @value_name = 'BackupDirectory';
go
:connect sql2017\db03
exec xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'SOFTWARE\Microsoft\MSSQLSERVER\MSSQLServer'
, @value_name = 'BackupDirectory';
go
declare @backupdir varchar(512) = '';
exec xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer'
, @value_name = 'BackupDirectory'
, @value = @backupdir output;
select @backupdir as backupdir;
go
declare @backupdir varchar(512) = '';
exec xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'SOFTWARE\Microsoft\MSSQLSERVER\MSSQLServer'
, @value_name = 'BackupDirectory'
, @value = @backupdir output;
select @backupdir as backupdir;
go
-- xp_regenumvalues / xp_instance_regenumvalues
-- these procedures enumerate all of the values of the specified key
-- returning a separate result set for each value
/*
EXECUTE xp_regenumvalues / xp_instance_regenumvalues
@rootkey='rootkey',
@key='key'
*/
exec xp_instance_regenumvalues
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'SOFTWARE\Microsoft\MSSQLSERVER\MSSQLServer';
go
declare @results table ( value varchar(255), value_data varchar(255));
insert into @results
exec xp_instance_regenumvalues
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'SOFTWARE\Microsoft\MSSQLSERVER\MSSQLServer';
select * from @results
go
-- xp_regenumkeys / xp_instance_regenumkeys
-- these procedures enumerate through all of the keys in a specified path
-- return all of the keys in that path.
/*
EXECUTE xp_regenumkeys / xp_instance_regenumkeys
@rootkey='rootkey',
@key='key'
*/
exec xp_instance_regenumkeys
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'Software\Microsoft\MSSQLSERVER';
go
exec xp_instance_regenumkeys
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'Software\Microsoft\MSSQLSERVER\Polybase';
go
-- xp_regwrite / xp_instance_regwrite
-- these procedures to create keys and write data into the registry
-- we can create up to 32 sub-keys at a time.
/*
EXECUTE xp_regwrite / xp_instance_regwrite
@rootkey='rootkey',
@key='key',
@value_name='value_name',
@type='type',
@value='value'
*/
exec xp_instance_regwrite
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'Software\Microsoft\MSSQLSERVER\MSSQLServer'
, @value_name = 'Hello'
, @type = 'REG_SZ'
, @value = 'Hello SQL Server';
go
-- xp_regdeletevalue / xp_instance_regdeletevalue
-- these procedures are used to delete a specified value from the registry
/*
EXECUTE xp_regdeletevalue / xp_instance_regdeletevalue
@rootkey='rootkey',
@key='key',
@value_name='value_name'
*/
exec xp_instance_regdeletevalue
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'Software\Microsoft\MSSQLSERVER\MSSQLServer'
, @value_name = 'Hello';
go
-- xp_regdeletekey / xp_instance_regdeletekey
-- these procedures to delete an entire key from the registry
/*
EXECUTE xp_regdeletekey / xp_instance_regdeletekey
@rootkey='rootkey',
@key='key',
*/
exec xp_instance_regwrite
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'Software\Microsoft\MSSQLSERVER\SQLServerAgent\MyNewKey'
, @value_name = 'MyNewValue'
, @type = 'REG_SZ'
, @value = 'Another new value!';
go
exec xp_instance_regdeletekey
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'Software\Microsoft\MSSQLSERVER\SQLServerAgent\MyNewKey';
go
-- xp_regaddmultistring / xp_instance_regaddmultistring
-- these procedures add a string to a multi-string entry in the registry
/*
EXECUTE xp_regaddmultistring / xp_instance_regaddmultistring
@rootkey='rootkey',
@key='key',
@value_name='value_name',
@value='value'
*/
exec xp_instance_regaddmultistring
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'Software\Microsoft\MSSQLSERVER\MSSQLServer\MyNewKey'
, @value_name = 'MyNewValue'
, @value = 'string1';
go
exec xp_instance_regaddmultistring
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'Software\Microsoft\MSSQLSERVER\MSSQLServer\MyNewKey'
, @value_name = 'MyNewValue'
, @value = 'string2';
go
exec xp_instance_regaddmultistring
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'Software\Microsoft\MSSQLSERVER\MSSQLServer\MyNewKey'
, @value_name = 'MyNewValue'
, @value = 'string3';
go
-- xp_regremovemultistring / xp_instance_regremovemultistring
-- these procedures removes a string from a multi-string entry.
/*
EXECUTE xp_regremovemultistring / xp_instance_regremovemultistring
@rootkey= 'rootkey',
@key='key',
@value_name='value_name',
@value='value'
*/
exec xp_instance_regremovemultistring
@rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'Software\Microsoft\MSSQLSERVER\MSSQLServer\MyNewKey'
, @value_name = 'MyNewValue'
, @value = 'string2';
go
DECLARE @netfxversion VARCHAR(1000)
EXECUTE xp_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\Full',
'Version',
@netfxversion OUTPUT
SELECT @netfxversion