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
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
|
| Author: critic 04 Jun 2004 | Member Level: Bronze Points : 0 |
This is one of the good articles that came up these days. We like to see more contributions from you.
Great job Dinesh.
|