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