I was catching up on some blog reading tonight when I saw this post. For retrieval purposes, I have made a copy of the post and will keep it for when in use. Thanks Omar!
Using T-SQL and the Registry to Retrieve a List of Countries
So every time I create a SQL script to populate a countries table I end up losing it. So here is proc I decided to keep forever on my blog that retrieves the list of countries from the registry of the machine it’s sitting on. Remember to run it under elevated permissions.
1:
PRINT
‘Building dbo.ListCountryFromRegistry’
2:
IF
EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N‘dbo.ListCountryFromRegistry’) AND OBJECTPROPERTY(id, N‘IsProcedure’) = 1)
3:
BEGIN
4:
DROP
PROC dbo.ListCountryFromRegistry
5:
END
6:
GO
7:
CREATE
PROC dbo.ListCountryFromRegistry
8:
AS
9: –Root Key
10:
DECLARE @RootKey nvarchar(255)
11:
SET @RootKey = ‘HKEY_LOCAL_MACHINE’
12:
13: –Registry Key
for country list
14:
DECLARE @CountryListKey nvarchar(255)
15:
SET @CountryListKey = ‘SOFTWARE\Microsoft\Windows\CurrentVersion\Telephony\Country List\’
16:
17: —Table
to store registry extracts
18:
CREATE
TABLE #RegistryOutput ( countryKey int)
19:
20: —Table
to store registry outputs with rownumbers.
21:
CREATE
TABLE #NumberedRegistryOutput ( rowNumber int, countryKey int)
22:
23: —Get the the list of country nodes under the cuntry list tree
24: INSERT INTO #RegistryOutput EXEC master..xp_regenumkeys @RootKey, @CountryListKey
25:
26: —Add RowNumbers to
use
as indexers later
27: INSERT INTO #NumberedRegistryOutput ( rowNumber, countryKey )
28: (SELECT ROW_NUMBER() OVER(ORDER
BY countryKey), countryKey
29:
FROM #RegistryOutput)
30:
31: –Grab a counter to
use
in looping through country nodes
32:
DECLARE @Counter int;
33:
SET @Counter = (SELECT
MAX(rowNumber) FROM #NumberedRegistryOutput);
34:
35: —Current
index
to be used to
iterate the countr nodes.
36:
DECLARE @CurrentCountryKey int;
37:
38: —Current Country Registry Key
39:
DECLARE @CountryRegistryKey nvarchar(150)
40:
41: –Country name returned form registry and
to be added to
output
42:
DECLARE @CountryName nvarchar(255)
43:
44: —Output
table
with Country Names
45:
Create
table #CountryNames ( CountryName nvarchar(255))
46:
47: –Loop through country nodes
48:
WHILE (@Counter > 0)
49:
BEGIN
50:
SET @Counter = @Counter – 1
51:
SET @CurrentCountryKey = (SELECT countryKey FROM #NumberedRegistryOutput WHERE rowNumber = @Counter)
52:
SET @CountryRegistryKey = @CountryListKey + RTRIM(LTRIM(CAST( @CurrentCountryKey as nvarchar(20))))
53:
54:
EXEC master..xp_regread
55: @RootKey,
56: @CountryRegistryKey,
57:
‘Name’,
58: @CountryName OUTPUT
59:
60: Insert #CountryNames VALUES (@CountryName)
61:
END
62:
63: —Output
64:
Select * from #CountryNames ORDER
BY CountryName
65:
66: –Cleanup
67:
drop
table #RegistryOutput
68:
drop
table #NumberedRegistryOutput
69:
drop
table #CountryNames
70:
71:
GO
Robert's Dot Net World 