Accessing The Registry From SQL Server

Introduction

I was looking for a function that would return a country list. I found a list of countries within the Registry at SOFTWARE\Microsoft\Windows\CurrentVersion\Telephony\Country List\.

By using this list, my task was reduced to reading the Registry. Unfortunately, I could not find any functions documented for accessing this information from the Registry. When I was looking for information, I found a German site that provided the syntax of the Registry functions. I decided to produce this short article in order to share what I have learned.

Reading the Registry

Syntax

EXECUTE master..xp_regread 'hKey','Key Value','String Value',@outvar OUTPUT

Parameters

hKey

Identifies a currently open key or any of the following predefined reserved handle values:

HKEY_CLASSES_ROOT
HKEY_CURRENT_USER
HKEY_LOCAL_MACHINE
HKEY_USERS

The enumerated values are associated with the key identified by hKey.

Key Value: Key value that you want to read

String Value: String value that you want to read

outvar: varchar variable in which output will be stored.

Example:

DECLARE @datapath varchar(255)
EXEC master..xp_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion',
'RegisteredOwner',
@datapath OUTPUT
PRINT @datapath

Writing to the Registry

Syntax

EXECUTE master..xp_regwrite 'hKey','Key Name','String Value','Data type',
                            'Value to write'

Parameters

hKey: Identifies a currently open key or any of the following predefined reserved handle values:

  • Key Name: Key name that you want to read
  • String Value: String Value that you want to read

Data Type:

  • REG_SZ: A null-terminated string. It will be a Unicode or ANSI string, depending on whether you use the Unicode or ANSI functions.
  • REG_BINARY: Binary data in any form.
  • REG_DWORD: A 32-bit number.

Example:

EXEC master..xp_regwrite
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion',
'RegisteredOwner',
'REG_SZ',
'DINESH'

Deleting a Registry String

Syntax

EXECUTE master..xp_regdeletevalue 'hKey','Key Name','String Value'

Parameters

hKey: Identifies a currently open key or any of the following predefined reserved handle values:

  • Key Name: Key name that you want to delete
  • String Value: String Value that you want to delete

Example:

EXEC master..xp_regdeletevalue
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion',
'RegisteredOwner',

Deleting a Registry Key

Syntax

EXECUTE master..xp_regdeletekey 'hKey','Key Name'

Parameters

  • hKey: Identifies a currently open key or any of the following predefined reserved handle values:
  • Key Value: Key value which you want to delete

Example:

EXEC master..xp_regdeletevalue
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion'

Reading All the Registry Keys Under the Given Registry Key

Syntax

EXECUTE master..xp_regenumkeys 'hKey','Key Name'

Parameters

hKey: Identifies a currently open key or any of the following predefined reserved handle values:

  • Key Name: Key name which you want to read

Example:

EXEC master..xp_regenumkeys
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\Providers'

Output will be:

ADSDSOObject
DB2OLEDB
Microsoft.Jet.OLEDB.4.0
MSDAORA
MSDASQL
MSIDXS
MSQLImpProv
MSSEARCHSQL
SQLOLEDB

Reading All the Registry Values Under the Given Registry Key

Syntax

EXECUTE master..xp_regenumvalues 'hKey','Key Name'

Parameters

hKey: Identifies a currently open key or any of the following predefined reserved handle values:

  • Key Name: Key name which you want to read

Example:

EXEC master..xp_regenumvalues
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters'

Output will be

Value Data
SQLArg0 -dC:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
SQLArg1 -eC:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG
SQLArg2 -lC:\Program Files\Microsoft SQL Server\MSSQL\data\mastlog.ldf

Implementation

Now it's time to use the above information. Let's try to get the country list from the Registry. The following script can be used to do this:

DECLARE @trenutniRed int
DECLARE @outputvar nvarchar(255)
DECLARE @countryKey nvarchar(150)
DECLARE @MasterKey nvarchar(150)
Set @MasterKey = 'SOFTWARE\Microsoft\Windows\CurrentVersion\Telephony\Country List\'
Create table #CID ( i int)
Create table #CNAME ( CountryName nvarchar(255))

insert into #CID EXEC master..xp_regenumkeys
'HKEY_LOCAL_MACHINE',
@MasterKey

DECLARE SysKursor INSENSITIVE SCROLL CURSOR
FOR SELECT i FROM #CID
FOR READ ONLY
OPEN SysKursor
FETCH NEXT FROM SysKursor INTO @trenutniRed

WHILE @@Fetch_Status = 0
BEGIN
Set @countryKey = @MasterKey + RTRIM(LTRIM(CAST( @trenutniRed as nvarchar(15))))

EXEC master..xp_regread
'HKEY_LOCAL_MACHINE',
@countryKey,
'Name',
@outputvar OUTPUT

Insert into #CNAME Values ( @outputvar)
FETCH NEXT FROM SysKursor INTO @trenutniRed

END

CLOSE SysKursor
DEALLOCATE SysKursor

Select * from #CNAME

drop table #CID
drop table #CNAME

Drawbacks

It is highly important to note that these functions are not documented by Microsoft — This means that they can change without any notice.



Comments

  • Quality work!

    Posted by Marc on 03/19/2013 12:30pm

    I always knew about these XPs but it's hard to find much documentation (even unofficial) on them. Thank you very much for doing a nice job breaking down each XP and providing examples!

    Reply
  • Good Article

    Posted by rsgovar on 11/17/2006 01:01am

    I studied a new thing from this article...

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • With JRebel, developers get to see their code changes immediately, fine-tune their code with incremental changes, debug, explore and deploy their code with ease (both locally and remotely), and ultimately spend more time coding instead of waiting for the dreaded application redeploy to finish. Every time a developer tests a code change it takes minutes to build and deploy the application. JRebel keeps the app server running at all times, so testing is instantaneous and interactive.

  • Live Event Date: May 6, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT While you likely have very good reasons for remaining on WinXP after end of support -- an estimated 20-30% of worldwide devices still are -- the bottom line is your security risk is now significant. In the absence of security patches, attackers will certainly turn their attention to this new opportunity. Join Lumension Vice President Paul Zimski in this one-hour webcast to discuss risk and, more importantly, 5 pragmatic risk mitigation techniques …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds