I have had to create a country and province dropdown combination several times on my webforms and have developed this script to create and populate a country table and a province table. The province table is populated assuming canada is countryid = 1, USA is countryid = 2, and UK is countryID = 3. I currently don't have the abbreviations for the UK provinces, but I have not really had to use them yet. Also included are scripts to create the stored procedures to get the countries and get the provinces for countries.
As I use this forum regularly as a resource when I am having problems I wanted to give something back to the community. Hope you can make use of this. Just run the following script in QueryAnalyzer
(original country and province data referenced from Wikipedia donation page)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[country]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[country]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[province]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[province]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_GetCountries]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[p_GetCountries]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_GetCountryProvinces]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[p_GetCountryProvinces]
GO
CREATE TABLE [dbo].[country] (
[countryID] [int] IDENTITY (1, 1) NOT NULL ,
[abbreviation] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[province] (
[provinceID] [int] IDENTITY (1, 1) NOT NULL ,
[abbreviation] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[countryID] [int] NULL
) ON [PRIMARY]
GO
insert into country(abbreviation,name) values('CA','Canada')
insert into country(abbreviation,name) values('US','United States')
insert into country(abbreviation,name) values('GB','United Kingdom')
insert into country(abbreviation,name) values('AD','Andorra')
insert into country(abbreviation,name) values('AI','Anguilla')
insert into country(abbreviation,name) values('AR','Argentina')
insert into country(abbreviation,name) values('AW','Aruba')
insert into country(abbreviation,name) values('AU','Australia')
insert into country(abbreviation,name) values('AT','Austria')
insert into country(abbreviation,name) values('BS','Bahamas')
insert into country(abbreviation,name) values('BH','Bahrain')
insert into country(abbreviation,name) values('BB','Barbados')
insert into country(abbreviation,name) values('BE','Belgium')
insert into country(abbreviation,name) values('BM','Bermuda')
insert into country(abbreviation,name) values('BW','Botswana')
insert into country(abbreviation,name) values('BR','Brazil')
insert into country(abbreviation,name) values('VG','British Virgin Islands')
insert into country(abbreviation,name) values('BN','Brunei')
insert into country(abbreviation,name) values('CV','Cape Verde')
insert into country(abbreviation,name) values('KY','Cayman Islands')
insert into country(abbreviation,name) values('CL','Chile')
insert into country(abbreviation,name) values('C2','China')
insert into country(abbreviation,name) values('CR','Costa Rica')
insert into country(abbreviation,name) values('HR','Croatia')
insert into country(abbreviation,name) values('CY','Cyprus')
insert into country(abbreviation,name) values('CZ','Czech Republic')
insert into country(abbreviation,name) values('DK','Denmark')
insert into country(abbreviation,name) values('DO','Dominican Republic')
insert into country(abbreviation,name) values('EC','Ecuador')
insert into country(abbreviation,name) values('EE','Estonia')
insert into country(abbreviation,name) values('FK','Falkland Islands')
insert into country(abbreviation,name) values('FJ','Fiji')
insert into country(abbreviation,name) values('FI','Finland')
insert into country(abbreviation,name) values('FR','France')
insert into country(abbreviation,name) values('GF','French Guiana')
insert into country(abbreviation,name) values('PF','French Polynesia')
insert into country(abbreviation,name) values('DE','Germany')
insert into country(abbreviation,name) values('GI','Gibraltar')
insert into country(abbreviation,name) values('GR','Greece')
insert into country(abbreviation,name) values('GP','Guadeloupe')
insert into country(abbreviation,name) values('HK','Hong Kong')
insert into country(abbreviation,name) values('HU','Hungary')
insert into country(abbreviation,name) values('IS','Iceland')
insert into country(abbreviation,name) values('IN','India')
insert into country(abbreviation,name) values('ID','Indonesia')
insert into country(abbreviation,name) values('IE','Ireland')
insert into country(abbreviation,name) values('IL','Israel')
insert into country(abbreviation,name) values('IT','Italy')
insert into country(abbreviation,name) values('JM','Jamaica')
insert into country(abbreviation,name) values('JP','Japan')
insert into country(abbreviation,name) values('JO','Jordan')
insert into country(abbreviation,name) values('LV','Latvia')
insert into country(abbreviation,name) values('LI','Liechtenstein')
insert into country(abbreviation,name) values('LT','Lithuania')
insert into country(abbreviation,name) values('LU','Luxembourg')
insert into country(abbreviation,name) values('MY','Malaysia')
insert into country(abbreviation,name) values('MV','Maldives')
insert into country(abbreviation,name) values('MT','Malta')
insert into country(abbreviation,name) values('MQ','Martinique')
insert into country(abbreviation,name) values('YT','Mayotte')
insert into country(abbreviation,name) values('MX','Mexico')
insert into country(abbreviation,name) values('MS','Montserrat')
insert into country(abbreviation,name) values('NA','Namibia')
insert into country(abbreviation,name) values('NL','Netherlands')
insert into country(abbreviation,name) values('AN','Netherlands Antilles')
insert into country(abbreviation,name) values('NC','New Caledonia')
insert into country(abbreviation,name) values('NZ','New Zealand')
insert into country(abbreviation,name) values('NO','Norway')
insert into country(abbreviation,name) values('PH','Philippines')
insert into country(abbreviation,name) values('PN','Pitcairn Islands')
insert into country(abbreviation,name) values('PL','Poland')
insert into country(abbreviation,name) values('PT','Portugal')
insert into country(abbreviation,name) values('QA','Qatar')
insert into country(abbreviation,name) values('RE','Reunion')
insert into country(abbreviation,name) values('RU','Russia')
insert into country(abbreviation,name) values('WS','Samoa')
insert into country(abbreviation,name) values('SG','Singapore')
insert into country(abbreviation,name) values('SK','Slovakia')
insert into country(abbreviation,name) values('SI','Slovenia')
insert into country(abbreviation,name) values('SB','Solomon Islands')
insert into country(abbreviation,name) values('ZA','South Africa')
insert into country(abbreviation,name) values('KR','South Korea')
insert into country(abbreviation,name) values('ES','Spain')
insert into country(abbreviation,name) values('SH','St. Helena')
insert into country(abbreviation,name) values('KN','St. Kitts and Nevis')
insert into country(abbreviation,name) values('LC','St. Lucia')
insert into country(abbreviation,name) values('PM','St. Pierre and Miquelon')
insert into country(abbreviation,name) values('SE','Sweden')
insert into country(abbreviation,name) values('CH','Switzerland')
insert into country(abbreviation,name) values('TW','Taiwan')
insert into country(abbreviation,name) values('TH','Thailand')
insert into country(abbreviation,name) values('TG','Togo')
insert into country(abbreviation,name) values('TO','Tonga')
insert into country(abbreviation,name) values('TT','Trinidad and Tobago')
insert into country(abbreviation,name) values('TN','Tunisia')
insert into country(abbreviation,name) values('TR','Turkey')
insert into country(abbreviation,name) values('TC','Turks and Caicos Islands')
insert into country(abbreviation,name) values('UA','Ukraine')
insert into country(abbreviation,name) values('AE','United Arab Emirates')
insert into country(abbreviation,name) values('UY','Uruguay')
insert into country(abbreviation,name) values('VE','Venezuela')
insert into country(abbreviation,name) values('VN','Vietnam')
insert into country(abbreviation,name) values('WF','Wallis and Futuna Islands')
insert into province(abbreviation,name,countryid) values('AB','Alberta',1)
insert into province(abbreviation,name,countryid) values('BC','British Columbia',1)
insert into province(abbreviation,name,countryid) values('MB','Manitoba',1)
insert into province(abbreviation,name,countryid) values('NB','New Brunswick',1)
insert into province(abbreviation,name,countryid) values('NL','Newfoundland & Labrador',1)
insert into province(abbreviation,name,countryid) values('NS','Nova Scotia',1)
insert into province(abbreviation,name,countryid) values('NT','Northwest Territories',1)
insert into province(abbreviation,name,countryid) values('NU','Nunavut',1)
insert into province(abbreviation,name,countryid) values('ON','Ontario',1)
insert into province(abbreviation,name,countryid) values('PE','Prince Edward Island',1)
insert into province(abbreviation,name,countryid) values('QC','Quebec',1)
insert into province(abbreviation,name,countryid) values('SK','Saskatchewan',1)
insert into province(abbreviation,name,countryid) values('YT','Yukon Territory',1)
insert into province(abbreviation,name,countryid) values('AK','Alaska',2)
insert into province(abbreviation,name,countryid) values('AL','Alabama',2)
insert into province(abbreviation,name,countryid) values('AR','Arkansas',2)
insert into province(abbreviation,name,countryid) values('AZ','Arizona',2)
insert into province(abbreviation,name,countryid) values('CA','California',2)
insert into province(abbreviation,name,countryid) values('CO','Colorado',2)
insert into province(abbreviation,name,countryid) values('CT','Connecticut',2)
insert into province(abbreviation,name,countryid) values('DE','Delaware',2)
insert into province(abbreviation,name,countryid) values('FL','Florida',2)
insert into province(abbreviation,name,countryid) values('GA','Georgia',2)
insert into province(abbreviation,name,countryid) values('HI','Hawaii',2)
insert into province(abbreviation,name,countryid) values('IA','Iowa',2)
insert into province(abbreviation,name,countryid) values('ID','Idaho',2)
insert into province(abbreviation,name,countryid) values('IL','Illinois',2)
insert into province(abbreviation,name,countryid) values('IN','Indiana',2)
insert into province(abbreviation,name,countryid) values('KS','Kansas',2)
insert into province(abbreviation,name,countryid) values('KY','Kentucky',2)
insert into province(abbreviation,name,countryid) values('LA','Louisiana',2)
insert into province(abbreviation,name,countryid) values('MA','Massachusetts',2)
insert into province(abbreviation,name,countryid) values('MD','Maryland',2)
insert into province(abbreviation,name,countryid) values('ME','Maine',2)
insert into province(abbreviation,name,countryid) values('MI','Michigan',2)
insert into province(abbreviation,name,countryid) values('MN','Minnesota',2)
insert into province(abbreviation,name,countryid) values('MO','Missouri',2)
insert into province(abbreviation,name,countryid) values('MS','Mississippi',2)
insert into province(abbreviation,name,countryid) values('MT','Montana',2)
insert into province(abbreviation,name,countryid) values('NC','North Carolina',2)
insert into province(abbreviation,name,countryid) values('ND','North Dakota',2)
insert into province(abbreviation,name,countryid) values('NE','Nebraska',2)
insert into province(abbreviation,name,countryid) values('NH','New Hampshire',2)
insert into province(abbreviation,name,countryid) values('NJ','New Jersey',2)
insert into province(abbreviation,name,countryid) values('NM','New Mexico',2)
insert into province(abbreviation,name,countryid) values('NV','Nevada',2)
insert into province(abbreviation,name,countryid) values('NY','New York',2)
insert into province(abbreviation,name,countryid) values('OH','Ohio',2)
insert into province(abbreviation,name,countryid) values('OK','Oklahoma',2)
insert into province(abbreviation,name,countryid) values('OR','Oregon',2)
insert into province(abbreviation,name,countryid) values('PA','Pennsylvania',2)
insert into province(abbreviation,name,countryid) values('RI','Rhode Island',2)
insert into province(abbreviation,name,countryid) values('SC','South Carolina',2)
insert into province(abbreviation,name,countryid) values('SD','South Dakota',2)
insert into province(abbreviation,name,countryid) values('TN','Tennessee',2)
insert into province(abbreviation,name,countryid) values('TX','Texas',2)
insert into province(abbreviation,name,countryid) values('UT','Utah',2)
insert into province(abbreviation,name,countryid) values('VA','Virginia',2)
insert into province(abbreviation,name,countryid) values('VT','Vermont',2)
insert into province(abbreviation,name,countryid) values('WA','Washington',2)
insert into province(abbreviation,name,countryid) values('WI','Wisconsin',2)
insert into province(abbreviation,name,countryid) values('WV','West Virginia',2)
insert into province(abbreviation,name,countryid) values('WY','Wyoming',2)
insert into province(abbreviation,name,countryid) values(' ','England',3)
insert into province(abbreviation,name,countryid) values(' ','Avon',3)
insert into province(abbreviation,name,countryid) values(' ','Bedfordshire',3)
insert into province(abbreviation,name,countryid) values(' ','Berkshire',3)
insert into province(abbreviation,name,countryid) values(' ','Bristol',3)
insert into province(abbreviation,name,countryid) values(' ','Buckinghamshire',3)
insert into province(abbreviation,name,countryid) values(' ','Cambridgeshire',3)
insert into province(abbreviation,name,countryid) values(' ','Cheshire',3)
insert into province(abbreviation,name,countryid) values(' ','Cleveland',3)
insert into province(abbreviation,name,countryid) values(' ','Cornwall',3)
insert into province(abbreviation,name,countryid) values(' ','Cumbria',3)
insert into province(abbreviation,name,countryid) values(' ','Derbyshire',3)
insert into province(abbreviation,name,countryid) values(' ','Devon',3)
insert into province(abbreviation,name,countryid) values(' ','Dorset',3)
insert into province(abbreviation,name,countryid) values(' ','Durham',3)
insert into province(abbreviation,name,countryid) values(' ','East Riding of Yorkshire',3)
insert into province(abbreviation,name,countryid) values(' ','East Sussex',3)
insert into province(abbreviation,name,countryid) values(' ','Essex',3)
insert into province(abbreviation,name,countryid) values(' ','Gloucestershire',3)
insert into province(abbreviation,name,countryid) values(' ','Greater Manchester',3)
insert into province(abbreviation,name,countryid) values(' ','Hampshire',3)
insert into province(abbreviation,name,countryid) values(' ','Herefordshire',3)
insert into province(abbreviation,name,countryid) values(' ','Hertfordshire',3)
insert into province(abbreviation,name,countryid) values(' ','Humberside',3)
insert into province(abbreviation,name,countryid) values(' ','Isle of Wight',3)
insert into province(abbreviation,name,countryid) values(' ','Isles of Scilly',3)
insert into province(abbreviation,name,countryid) values(' ','Kent',3)
insert into province(abbreviation,name,countryid) values(' ','Lancashire',3)
insert into province(abbreviation,name,countryid) values(' ','Leicestershire',3)
insert into province(abbreviation,name,countryid) values(' ','Lincolnshire',3)
insert into province(abbreviation,name,countryid) values(' ','London',3)
insert into province(abbreviation,name,countryid) values(' ','Merseyside',3)
insert into province(abbreviation,name,countryid) values(' ','Middlesex',3)
insert into province(abbreviation,name,countryid) values(' ','Norfolk',3)
insert into province(abbreviation,name,countryid) values(' ','North Yorkshire',3)
insert into province(abbreviation,name,countryid) values(' ','Northamptonshire',3)
insert into province(abbreviation,name,countryid) values(' ','Northumberland',3)
insert into province(abbreviation,name,countryid) values(' ','Nottinghamshire',3)
insert into province(abbreviation,name,countryid) values(' ','Oxfordshire',3)
insert into province(abbreviation,name,countryid) values(' ','Rutland',3)
insert into province(abbreviation,name,countryid) values(' ','Shropshire',3)
insert into province(abbreviation,name,countryid) values(' ','Somerset',3)
insert into province(abbreviation,name,countryid) values(' ','South Yorkshire',3)
insert into province(abbreviation,name,countryid) values(' ','Staffordshire',3)
insert into province(abbreviation,name,countryid) values(' ','Suffolk',3)
insert into province(abbreviation,name,countryid) values(' ','Surrey',3)
insert into province(abbreviation,name,countryid) values(' ','Tyne and Wear',3)
insert into province(abbreviation,name,countryid) values(' ','Warwickshire',3)
insert into province(abbreviation,name,countryid) values(' ','West Midlands',3)
insert into province(abbreviation,name,countryid) values(' ','West Sussex',3)
insert into province(abbreviation,name,countryid) values(' ','West Yorkshire',3)
insert into province(abbreviation,name,countryid) values(' ','Wiltshire',3)
insert into province(abbreviation,name,countryid) values(' ','Worcestershire',3)
insert into province(abbreviation,name,countryid) values(' ','Northern Ireland',3)
insert into province(abbreviation,name,countryid) values(' ','Antrim',3)
insert into province(abbreviation,name,countryid) values(' ','Armagh',3)
insert into province(abbreviation,name,countryid) values(' ','Down',3)
insert into province(abbreviation,name,countryid) values(' ','Fermanagh',3)
insert into province(abbreviation,name,countryid) values(' ','Londonderry',3)
insert into province(abbreviation,name,countryid) values(' ','Tyrone',3)
insert into province(abbreviation,name,countryid) values(' ','Scotland',3)
insert into province(abbreviation,name,countryid) values(' ','Aberdeen City',3)
insert into province(abbreviation,name,countryid) values(' ','Aberdeenshire',3)
insert into province(abbreviation,name,countryid) values(' ','Angus',3)
insert into province(abbreviation,name,countryid) values(' ','Argyll and Bute',3)
insert into province(abbreviation,name,countryid) values(' ','Borders',3)
insert into province(abbreviation,name,countryid) values(' ','Clackmannan',3)
insert into province(abbreviation,name,countryid) values(' ','Dumfries and Galloway',3)
insert into province(abbreviation,name,countryid) values(' ','East Ayrshire',3)
insert into province(abbreviation,name,countryid) values(' ','East Dunbartonshire',3)
insert into province(abbreviation,name,countryid) values(' ','East Lothian',3)
insert into province(abbreviation,name,countryid) values(' ','East Renfrewshire',3)
insert into province(abbreviation,name,countryid) values(' ','Edinburgh City',3)
insert into province(abbreviation,name,countryid) values(' ','Falkirk',3)
insert into province(abbreviation,name,countryid) values(' ','Fife',3)
insert into province(abbreviation,name,countryid) values(' ','Glasgow',3)
insert into province(abbreviation,name,countryid) values(' ','Highland',3)
insert into province(abbreviation,name,countryid) values(' ','Inverclyde',3)
insert into province(abbreviation,name,countryid) values(' ','Midlothian',3)
insert into province(abbreviation,name,countryid) values(' ','Moray',3)
insert into province(abbreviation,name,countryid) values(' ','North Ayrshire',3)
insert into province(abbreviation,name,countryid) values(' ','North Lanarkshire',3)
insert into province(abbreviation,name,countryid) values(' ','Orkney',3)
insert into province(abbreviation,name,countryid) values(' ','Perthshire and Kinross',3)
insert into province(abbreviation,name,countryid) values(' ','Renfrewshire',3)
insert into province(abbreviation,name,countryid) values(' ','Roxburghshire',3)
insert into province(abbreviation,name,countryid) values(' ','Shetland',3)
insert into province(abbreviation,name,countryid) values(' ','South Ayrshire',3)
insert into province(abbreviation,name,countryid) values(' ','South Lanarkshire',3)
insert into province(abbreviation,name,countryid) values(' ','Stirling',3)
insert into province(abbreviation,name,countryid) values(' ','West Dunbartonshire',3)
insert into province(abbreviation,name,countryid) values(' ','West Lothian',3)
insert into province(abbreviation,name,countryid) values(' ','Western Isles',3)
insert into province(abbreviation,name,countryid) values(' ','Blaenau Gwent',3)
insert into province(abbreviation,name,countryid) values(' ','Bridgend',3)
insert into province(abbreviation,name,countryid) values(' ','Caerphilly',3)
insert into province(abbreviation,name,countryid) values(' ','Cardiff',3)
insert into province(abbreviation,name,countryid) values(' ','Carmarthenshire',3)
insert into province(abbreviation,name,countryid) values(' ','Ceredigion',3)
insert into province(abbreviation,name,countryid) values(' ','Conwy',3)
insert into province(abbreviation,name,countryid) values(' ','Denbighshire',3)
insert into province(abbreviation,name,countryid) values(' ','Flintshire',3)
insert into province(abbreviation,name,countryid) values(' ','Gwynedd',3)
insert into province(abbreviation,name,countryid) values(' ','Isle of Anglesey',3)
insert into province(abbreviation,name,countryid) values(' ','Merthyr Tydfil',3)
insert into province(abbreviation,name,countryid) values(' ','Monmouthshire',3)
insert into province(abbreviation,name,countryid) values(' ','Neath Port Talbot',3)
insert into province(abbreviation,name,countryid) values(' ','Newport',3)
insert into province(abbreviation,name,countryid) values(' ','Pembrokeshire',3)
insert into province(abbreviation,name,countryid) values(' ','Powys',3)
insert into province(abbreviation,name,countryid) values(' ','Rhondda Cynon Taff',3)
insert into province(abbreviation,name,countryid) values(' ','Swansea',3)
insert into province(abbreviation,name,countryid) values(' ','Torfaen',3)
insert into province(abbreviation,name,countryid) values(' ','The Vale of Glamorgan',3)
insert into province(abbreviation,name,countryid) values(' ','Wrexham',3)
insert into province(abbreviation,name,countryid) values(' ','UK Offshore Dependencies',3)
insert into province(abbreviation,name,countryid) values(' ','Channel Islands',3)
insert into province(abbreviation,name,countryid) values(' ','Isle of Man',3)
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE dbo.p_GetCountries
AS
SELECT *
FROM country
Order By name asc
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE dbo.p_GetCountryProvinces
(
@countryID int
)
AS
SELECT *
FROM province
where countryID = @countryID
order by name asc
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO