Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Working with the Registry

By Dinesh Asanka,

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

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

Total article views: 8638 | Views in the last 30 days: 2
 
Related Articles
BLOG

Microsoft Master Data Services in SQL Server 2012

Microsoft SQL Server Master Data Services (MDS) is a Master Data Management (MDM) product from Micro...

FORUM

Read Registry

Could you please share a script to read remote server's registry?

BLOG

Rangers to Masters

The new Certified Master program sounds suspiciously like the Microsoft Certified Architect program,...

BLOG

Understanding SQL Server related Windows Registries

SQL Server related information from Windows registries The Windows Registry is a hierarchical d...

BLOG

Learning About Microsoft Certified Master: SQL Server

Among the Microsoft certifications, the Microsoft Certified Master is the apex of all of them except...

Tags
miscellaneous    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones