Introduction
I was looking for a function that would return a country list. I found a list of countries within the Registry at SOFTWAREMicrosoftWindowsCurrentVersionTelephonyCountry 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', 'SOFTWAREMicrosoftMSSQLServerMSSQLServerCurrentVersion', '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', 'SOFTWAREMicrosoftMSSQLServerMSSQLServerCurrentVersion', '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', 'SOFTWAREMicrosoftMSSQLServerMSSQLServerCurrentVersion', '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', 'SOFTWAREMicrosoftMSSQLServerMSSQLServerCurrentVersion'
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', 'SOFTWAREMicrosoftMSSQLServerProviders'
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', 'SOFTWAREMicrosoftMSSQLServerMSSQLServerParameters'
Output will be
Value | Data |
---|---|
SQLArg0 | -dC:Program FilesMicrosoft SQL ServerMSSQLdatamaster.mdf |
SQLArg1 | -eC:Program FilesMicrosoft SQL ServerMSSQLlogERRORLOG |
SQLArg2 | -lC:Program FilesMicrosoft SQL ServerMSSQLdatamastlog.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 = 'SOFTWAREMicrosoftWindowsCurrentVersionTelephonyCountry 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.