Manipulating the Registry

Extended stored procedures are C or C++ routines that let you perform actions outside the scope of standard T-SQL database access and management. You can view SQL Server's available extended stored procedures in the master database. Here are seven undocumented extended stored procedures that you can use to manipulate the registry. Remember that modifying the registry can cause severe system problems, so take extra precautions before performing any action that can alter the registry.

7. xp_regenumkeys

Of the extended stored procedures in this list, only this one is exclusively in SQL Server 2000. To produce a resultset that contains all the subkeys in a specified registry location, enter the following statement in Query Analyzer or as a part of a stored procedure:

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

6. xp_regenumvalues

This extended stored procedure returns multiple resultsets that list registry values and data.

                              xp_regenumvalues 'HKEY_LOCAL_MACHINE',                              'SOFTWARE\Microsoft\MSSQLServerMSSQLServer'

5. xp_regread

Xp_regread lets you read the values that subkeys, such as BackupDirectory, contain:

                              DECLARE @backupdir varchar(255)                              EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',                                'SOFTWARE\Microsoft\MSSQLServerMSSQLServer',                                'BackupDirectory',@backupdir OUTPUT

4. xp_regaddmultistring or xp_regremovemultistring

Running xp_regaddmultistring, as in the following example, adds a new value to the multivalued string entry. Likewise, xp_regremove-multistring removes one string item at a time.

                              xp_regaddmultistring 'HKEY_LOCAL_MACHINE', 'SOFTWARE', 'DeleteThisValue','String1'

3. xp_regdeletekey

To delete a specified registry subkey, including all its values and data, enter

                              xp_regdeletekey 'HKEY_LOCAL_MACHINE', 'SOFTWAREDeleteThisKey'

2. xp_regdeletevalue

To delete a registry value and its data, enter

                              xp_regdeletevalue 'HKEY_LOCAL_MACHINE', 'SOFTWARE',                              'DeleteThisValue', ''

1. xp_regwrite

This vital registry extended procedure writes registry values.

                              xp_regwrite 'HKEY_LOCAL_MACHINE',   'SOFTWARE', 'DeleteThisValue',                                'REG_SZ', 'New'