Part 1  |  Part 2

This is the second article in my series discussing how to manipulate the Windows registry using SQL Server Extended Stored Procedures. If you are writing applications that require you to read and write to the Windows registry, this article will give you some insight into how to manipulate the registry using T-SQL. In this Article I will discuss how to enumerate the keys and values of a specific registry structure, how to deleted registry entries, and how to create and manipulate registry entries that contain multiple values.

Browsing the Registry with Extended Stored Procedures

If your application needs to browse the Windows registry to determine what keys and values are available, Microsoft SQL Server has two Extended Stored Procedures you can use: xp_regenumkeys and xp_regenumvalues. You can use xp_regenumkeys to determine what keys are available in a given registry folder; xp_regenumvalues will display the values associated with a specific key.

Here is the calling syntax for the xp_regenumkeys Extended Stored Procedure:

EXECUTE master..xp_regenumkeys
        'HKEY',    
        ’KEY VALUE',

Where:

• ‘HKEY’  is one of the following high level sub-trees:
• HKEY_LOCAL_MACHINE
• HKEY_CURRENT_USER
• HKEY_CURRENT_CONFIG
• HKEY_USERS
• HKEY_CLASSES_ROOT
• ‘KEY VALUE’ is the registry key value for which you are interested in enumerating the different keys.

The following is an example of how to use the xp_regenumkeys Extended Stored Procedure:

     EXECUTE xp_regenumkeys 'HKEY_LOCAL_MACHINE',                    
                            'SOFTWARE\Microsoft\MSSQLServer\'

In the following, I have enumerated the “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\” folder. Running this on your machine should generate the following output:

     SubKeyName
     ---------------------------------------- 
     Client
     MSSQLServer
     Providers
     Replication
     Setup
     SNMP
     SQLServerAgent
     Tracking

As you can see, this procedure displayed the keys available under the folder enumerated. Each one of the keys displayed is also a registry folder.

To display the values under a given registry key folder, you can use the “xp_regenumvalues” Extended Stored Procedure. This is the syntax for “xp_regenumvalues”:

EXECUTE master..xp_regenumvalues
        'HKEY',    
        'KEY VALUE'

Where:

• ‘HKEY’  is one of the following high level sub-trees:
• HKEY_LOCAL_MACHINE
• HKEY_CURRENT_USER
• HKEY_CURRENT_CONFIG
• HKEY_USERS
• HKEY_CLASSES_ROOT
• ‘KEY VALUE’ is the registry key value for which you are interested in reviewing the different values available.

Here is an example that displays the key values under folder, “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup\“:

     set nocount on
     EXECUTE xp_regenumvalues 'HKEY_LOCAL_MACHINE',                     
                              'SOFTWARE\Microsoft\MSSQLServer\Setup\'

These commands should generate the following output:

     Value          Data
     --------------------------------------------------------------
     SQLPath        C:\Program Files\Microsoft SQL Server\MSSQL

     Value          Data
     --------------------------------------------------------------
     SQLDataRoot    C:\Program Files\Microsoft SQL Server\MSSQL

     Value          Data
     --------------------------------------------------------------
     SourcePath     D:

     Value          Data        
     --------------------------------------------------------------
     firststart     0

In this example, you can see the values and data for each key value under the “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup\“ folder.

How to Delete a Registry Value Using xp_regdeletevalue

If your application requires you to remove a single registry value, this can be accomplished by using the xp_regdeletevalue Extended Stored Procedure. Use the following syntax for this procedure:

EXECUTE master..xp_regdeletevalue
        'HKEY',    
        ’KEY VALUE',
        'STING VALUE'

Where:

• ‘HKEY’  is one of the following high level sub-trees:
• HKEY_LOCAL_MACHINE
• HKEY_CURRENT_USER
• HKEY_CURRENT_CONFIG
• HKEY_USERS
• HKEY_CLASSES_ROOT
• ‘KEY VALUE’ is the registry key value for which you are interested in deleting the value
• ‘STRING VALUE’ is the registry string value you want to delete

You can delete the registry value “HKEY_LOCAL_MACHINE\SOFTWARE\Larsen\Info\FirstName” with the following example of code, which uses the “xp_regdeletevalue” Extended Stored Procedure:

     EXECUTE master..xp_regdeletevalue 'HKEY_LOCAL_MACHINE',
                                       'SOFTWARE\Larsen\Info\', 
                                       'FirstName'

This example only deletes the single value “FirstName” in the registry folder “HKEY_LOCAL_MACHINE\SOFTWARE\Larsen\Info\.”

However, anytime you are manipulating the registry, be sure you know what you are doing. This is especially true when you are deleting registry entries. Incorrectly updating your Windows registry could cause such damage that you might find yourself reinstalling the operating system and all software.

How to Delete a Registry Key using xp_regdeletekey

If your application needs to delete an entire registry key folder, this can be accomplished by using the Extended Stored Procedure xp_regdeletekey. You can use the following syntax for this procedure:

EXECUTE master..xp_regdeletekey
        'HKEY',    
        ’KEY VALUE'

• ‘HKEY’  is one of the following high level sub-trees:
• HKEY_LOCAL_MACHINE
• HKEY_CURRENT_USER
• HKEY_CURRENT_CONFIG
• HKEY_USERS
• HKEY_CLASSES_ROOT
• ‘KEY VALUE’ is the registry key value for which you are interested in deleting the key folder

Here is an example that uses the Extended Stored Procedure xp_regdeletekey:

     EXECUTE master..xp_regdeletekey
                     'HKEY_LOCAL_MACHINE',
                     'SOFTWARE\Larsen\Info\'

This example deletes the entire Windows registry key folder   “HKEY_LOCAL_MACHINE\SOFTWARE\Larsen\Info\.” If you are building applications to store and manipulate information in the registry, the ability to delete an entire folder with a single T-SQL command allows you to quickly remove an entire registry key.

Adding and Removing Multi-String Registry Entries

Microsoft SQL Server provides two different Extended Stored Procedures to manipulate multi-string registry entries. A multi-string registry entry is a key that contains multiple values in a single registry entry.

The first Extended Stored Procedure “xp_regaddmultistring” allows you to create a new multi-string registry entry or add a new string to an existing multi-string key:

EXECUTE master..xp_regaddmultistring
        'HKEY',    
        ’KEY VALUE',
        'STRING VALUE',
        ‘String to Write’

Where:

• ‘HKEY’  is one of the following high level sub-trees:
• HKEY_LOCAL_MACHINE
• HKEY_CURRENT_USER
• HKEY_CURRENT_CONFIG
• HKEY_USERS
• HKEY_CLASSES_ROOT
• ‘KEY VALUE’ is the registry key value to be written
• ‘STRING VALUE’ is the registry string value to be written
• ‘String to Write’ – The string to be written to the multi-string entry

In the following example, I have created a new, multi-string registry entry using the Extended Stored Procedure “xp_regaddmultistring”:

     EXECUTE master..xp_regaddmultistring 'HKEY_LOCAL_MACHINE', 
                          'SOFTWARE\Larsen\Info', 
                          'Owner',
                          'Gregory'
     EXECUTE master..xp_regaddmultistring 'HKEY_LOCAL_MACHINE', 
                          'SOFTWARE\Larsen\Info', 
                          'Owner',
                          'A'
     EXECUTE master..xp_regaddmultistring 'HKEY_LOCAL_MACHINE', 
                          'SOFTWARE\Larsen\Info', 
                          'Owner',
                          'Larsen'

With this procedure, I have created a new registry entry in the “HKEY_LOCAL_MACHINE\Software\Larsen\Info” folder with a value_name of “Owner” and three different strings: “Gregory,” “A,” “Larsen.” Now, this registry entry contains three different strings that make up my complete name.

The second multi-string Extended Stored Procedure, “xp_regremovemultistring” can remove a string from a multi-string registry entry. The “xp_regremovemultistring” procedure has the same calling syntax as “xp_regaddmultistring. ”

Note the following example, which demonstrates how to remove the initial for my middle name and my last name from the registry entry I just created above:

     EXECUTE master..xp_regremovemultistring 'HKEY_LOCAL_MACHINE', 
                          'SOFTWARE\Larsen\Info', 
                          'Owner',
                          'A'
     EXECUTE master..xp_regremovemultistring 'HKEY_LOCAL_MACHINE', 
                          'SOFTWARE\Larsen\Info', 
                          'Owner',
                          'Larsen'

In this procedure, the first “EXECUTE” statement removed the string “A,” and the second “EXECUTE” statement removed “Larsen” from the HKEY_LOCAL_MACHINE\SOFTWARE\Larsen\Info\Owner registry multi-string entry. After executing these two commands, this registry entry only contains my first name, “Gregory.”

Considerations When Updating the Windows Registry

When you are updating the Windows registry, take care not to corrupt the registry, which could cause serious problems with your machine. Depending on the extent of the corruption, you might even need to reinstall all the software on your machine to recover from such damage. So, before you update or delete entries in the registry, make sure you understand what you are doing and the consequences of your actions.

Conclusion

As you can see, Microsoft SQL Server provides a number of different Extended Stored Procedures to read and write to the Windows registry. Remember, these procedures are undocumented, so Microsoft may change the functionality of these procedures with the next release of Microsoft SQL Server. I suggest that if you should use one of these Extended Stored Procedures, you should consider thoroughly testing your code against any future release of Microsoft SQL Server. But these undocumented Extended Stored Procedures included in Microsoft SQL Server may provide you with the functionality you need to manipulate the Windows registry using T-SQL.