Windows registry is a hierarchical database that store configuration settings and options on Microsoft Windows operating systems. Read more from http://en.wikipedia.org/wiki/Windows_Registry.

How to take a look at the registry?

1. Press Win + R
   
   
2. Type “regedit” and press “Enter”
   
   
3. Now you can play with the registry. As you can see, the structure of the registry is like file system. There are "folders" on the left panel, and "files" on the right panel.
   
   

Terms used in Windows registry

Keys

Keys is like folders, for example, there are some root keys in the registry. In my system, they are

HKEY_CLASSES_ROOT
HKEY_CURRENT_USER
HKEY_LOCAL_MACHINE
HKEY_USERS
HKEY_CURRENT_CONFIG

Under the root keys, there are sub keys. For example, there is a key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft, Microsoft is a sub key of the key HKEY_LOCAL_MACHINE\SOFTWARE, and SOFTWARE is a sub key of the root key HKEY_LOCAL_MACHINE.

Values

Values is like files in a folder. A value is a name/data pair. The name part of the value is called value name, the data part of the value is called value. So there can be multiple values under a key. A value is combined by a value name and a value. This is similar to the concept of a key value pair in other programming languages.

Let’s take the following image as an example.

Under the key HKEY_LOCAL_MACHINE\SOFTWARE\7-Zip, there is a value. The name of the value is Path (value name), the value of the value is C:\Program Files\7-Zip\.

How to access Windows registry from SQL Server?

There are some registry-related extended stored procedure in SQL Server. They are:

As you can see, for every stored procedure on the left of the table, there is a correspond instance version on the right. The stored procedures on the left (without instance in their name) will read, write the registry key exactly as you specified. However, the instance version will read, write under the context of the current instance you are connecting to. The instance version of the stored procedures will only effect when you are dealing with SQL Server related keys or values, otherwise, they have the same effect as the non-instance version. Let’s take some example to demonstrate.

Let’s try to read determine whether the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Telephony\Country List\1 exists. I’ll use xp_regread to to this work.

EXECUTE master.dbo.xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE',
    @key = 'SOFTWARE\Microsoft\Windows\CurrentVersion\Telephony\Country List\1';
GO

/*
KeyExist
-----------
1
*/

We got the result 1, which mean the key does exist. Since this key is not related to SQL Server, we can use xp_instance_regread to get the same result:

EXECUTE master.dbo.xp_instance_regread
    @rootkey = 'HKEY_LOCAL_MACHINE',
    @key = 'SOFTWARE\Microsoft\Windows\CurrentVersion\Telephony\Country List\1';
GO

If we access a non exist key, we will get this result:

EXECUTE master.dbo.xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE',
    @key = 'SOFTWARE\Microsoft\Windows\CurrentVersion\Telephony\Country List\Edde';
GO

/*
KeyExist
-----------
0
*/

We can use xp_regread to get the value of an item under a key as table form. For example, we can get the value of the item Name under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Telephony\Country List\1.

EXECUTE master.dbo.xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE',
    @key = 'SOFTWARE\Microsoft\Windows\CurrentVersion\Telephony\Country List\1',
    @value_name = 'Name';

Note that, the @value_name, @key, and @rootkey must exist, otherwise you will get an exception. For example:

EXECUTE master.dbo.xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE',
    @key = 'SOFTWARE\Microsoft\Windows\CurrentVersion\Telephony\Country List\Edde',
    @value_name = 'Name';
GO

/*

RegOpenKeyEx() returned error 2, 'The system cannot find the file specified.'
Msg 22001, Level 1, State 1

(0 row(s) affected)

*/

Also, you can read an item value to a variable like this:

DECLARE @result NVARCHAR(4000);

EXECUTE master.dbo.xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE',
    @key = 'SOFTWARE\Microsoft\Windows\CurrentVersion\Telephony\Country List\1',
    @value_name = 'Name',
    @value = @result OUTPUT;
PRINT @result;
GO

Let’s write something into the registry. I want to add an item under the key HKEY_LOCAL_MACHINE\SOFTWARE\EDDE, note the sub key EDDE does not exist before I add an item under it. What I want to add is a simple item “name” (value name), “Yang Shuai” (vlaue).

EXEC master.dbo.xp_regwrite
    @rootkey = 'HKEY_LOCAL_MACHINE', 
    @key = 'SOFTWARE\EDDE',
    @value_name = 'name',
    @type = 'REG_SZ',
    @value = 'Yang Shuai';
GO

Take a look at the following image, the item has been added.

Now let’s delete the key we just created.

EXEC master.dbo.xp_regdeletekey
    @rootkey = 'HKEY_LOCAL_MACHINE',
    @key = 'SOFTWARE\EDDE';
GO

You can check the registry to confirm it’s already been deleted. You can run the last piece of code multiple times, since it will not give error if the key does not exist. You can give it a try.

Not let’s take some keys related to SQL Server. Let’s take a look at the following image.

I’ve installed 4 instances on my computer. One default instance, and 3 named instance, instance1, instance2, instance3. You can get all the instance names from the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL. However, a lot of the instance specific information are not saved here. For example, from the image we know that for instance1, there is a sub key called MSSQL10_50.INSTANCE1. After you got this sub key, you can explore it under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server, which is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.INSTANCE1. Here you can find a lot of information of instance1. For example, I can confirm the replication feature is installed for instance. Take a look at the following image.

To use TSQL to get this information, I can use:

DECLARE @result INT;

EXECUTE master.dbo.xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE',
    @key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.INSTANCE1\Replication',
    @value_name = 'IsInstalled',
    @value = @result OUTPUT;
PRINT @result;
GO
/*
1
*/

Note I use INT type to get the result. Since the value is of type REG_DWORD. Take the following table as a reference.

As you can see, xp_regread will read exactly as you specified. In this example, it is reading the value “IsInstalled” under the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.INSTANCE1\Replication. We can use xp_instance_regread to simplify the code a little. Let’s replace Microsoft SQL Server\MSSQL10_50.INSTANCE1 with MSSQLServer in the key name. Now we have SOFTWARE\Microsoft\MSSQLServer\Replication as the key name. Let’s take a look at the code.

DECLARE @result INT;

EXECUTE master.dbo.xp_instance_regread
    @rootkey = 'HKEY_LOCAL_MACHINE',
    @key = 'SOFTWARE\Microsoft\MSSQLServer\Replication',
    @value_name = 'IsInstalled',
    @value = @result OUTPUT;
PRINT @result;
GO
/*
1
*/

This time we are using xp_instance_regread instead of xp_regread. Since we are connecting to instance1 to run the code, the MSSQLServer in the key name represents the key of the current instance, which is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.INSTANCE1. So SOFTWARE\Microsoft\MSSQLServer\Replication really means HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.INSTANCE1\Replication. Now we know what the instance version of the stored procedure does, it simply replace MSSQLServer with the sub key of the instance you are currently connecting to. That’s it.

Let’s enum the values under a key.

IF OBJECT_ID('tempdb..#temp_reg_values') IS NOT NULL DROP TABLE #temp_reg_values;
CREATE TABLE #temp_reg_values
(
    value_name NVARCHAR(4000),
    data NVARCHAR(4000)
);

INSERT #temp_reg_values
EXEC master.dbo.xp_instance_regenumvalues
    @rootkey = N'HKEY_LOCAL_MACHINE',
    @key = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL';

SELECT * FROM #temp_reg_values;
GO
/*

value_name                                         data
-------------------------------------- --------------------------------------------------
MSSQLSERVER                                    MSSQL10_50.MSSQLSERVER
INSTANCE1                                          MSSQL10_50.INSTANCE1
INSTANCE2                                          MSSQL10_50.INSTANCE2
INSTANCE3                                          MSSQL10_50.INSTANCE3
*/

Or we can get all sub keys under a key.

IF OBJECT_ID('tempdb..#temp_reg_subkey_names') IS NOT NULL DROP TABLE #temp_reg_subkey_names;
CREATE TABLE #temp_reg_subkey_names
(
    subkey_name NVARCHAR(4000)
);

INSERT #temp_reg_subkey_names
EXECUTE xp_instance_regenumkeys
    @rootkey = 'HKEY_LOCAL_MACHINE',
    @key = 'Software';

SELECT * FROM #temp_reg_subkey_names;
GO
/*

subkey_name
-------------------------------
7-Zip
ActiveState
Adobe
AMD
Apple Computer, Inc.
Apple Inc.
ATI Technologies

...

(32 row(s) affected)
*/

I’ve not take a look of all the stored procedures aforementioned. However, the principal is the same. You can take a look them by yourself.

Thanks for reading. Bye.