SQLServerCentral Article

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

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'

Out put will be

ValueData
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

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

Rate

3.67 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

3.67 (3)

You rated this post out of 5. Change rating