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'