Working with the Registry

Introduction

I was looking for a function which will return me the country list. I found that list if countries in the registry. List of the countries resides in the  SOFTWARE\Microsoft\Windows\CurrentVersion\Telephony\Country List\. Therefore My task was reduced to read this registry. But unfortunately, I found that there are no functions documented for the registry functions. When I was looking for more information, I found a German Site which gives the syntax of the registry functions (thanks to Frank Kalis). So I decided to produce this short article in order to make my finds useful to others.

Read 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 which you want to read

String Value : String Value which 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

Write 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 which you want to read

String Value : String Value which 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'

Delete 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 which you want to delete

String Value : String Value which you want to delete

Example:

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

Delete 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'

Out put will be

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'

Out put will be

Implementation

Now it's time to use the above knowledge into practice. Let's try to get the country list from the registry. Following script can be used for that.

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