Thread: CanadaProvincesCitiesDumpScript

Is any one can have create / Insert scripts for Canandian provinces and their cities?

Create Script for Countries / Provinces / Cities:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Countries]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Countries](
[CountryID] [int] NOT NULL,
[CountryName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED
(
[CountryID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

END
GO

-- Create Script for States table

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[States]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[States](
[StateCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CountryID] [int] NOT NULL,
[StateName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
(
[StateCode] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

-- Create Script for Cities table

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Cities]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Cities](
[CityID] [int] NOT NULL,
[StateCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CityName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
END
GO

-- Binding relationship between States & Cities with StateCode

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Cities_States]') AND parent_object_id = OBJECT_ID(N'[dbo].[Cities]'))
ALTER TABLE [dbo].[Cities] WITH CHECK ADD CONSTRAINT [FK_Cities_States] FOREIGN KEY([StateCode])
REFERENCES [dbo].[States] ([StateCode])

-- Binding relationship between Contries & States CountryID

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_States_Countries]') AND parent_object_id = OBJECT_ID(N'[dbo].[States]'))
ALTER TABLE [dbo].[States] WITH CHECK ADD CONSTRAINT [FK_States_Countries] FOREIGN KEY([CountryID])
REFERENCES [dbo].[Countries] ([CountryID])


Thanks,
Muthu T.