h1

Using T-SQL and the Registry to Retrieve a List of Countries

August 20, 2008

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: