working on it ...

Filters

Explore Public Snippets

Sort by

Found 7,190 snippets matching: database

    public by sjschmalfeld  298710  1  4  1

    Kill Connections to a Database SQL Server

    sql
    // KILL CONNECTIONS TO A DATABASE
    
    USE [master]
    GO  
    
    DECLARE @kill varchar(8000) = '';  
    SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
    FROM sys.dm_exec_sessions
    WHERE database_id  = db_id('MyDB')
    
    EXEC(@kill);

    public by cghersi  327996  0  6  1

    Retrieve useful information regarding indexes in MS SQL Server

    This script helps the DBA to retrieve valuable information regarding indexes. Here the complete explanation of the output fields: object_type: type of object (will either be "U" for User-Defined Table or "V" for View) database_name: name of the database schema_name: name of the schema object_name: name of the object create_date: date the obje
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SET NOCOUNT ON
    SET ANSI_WARNINGS OFF
    SET ARITHABORT OFF
    SET ARITHIGNORE ON
    SET TEXTSIZE 2147483647
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Declarations / Sets: Declare And Set Variables
    -----------------------------------------------------------------------------------------------------------------------------
    
    DECLARE @Database_ID AS SMALLINT
    
    
    SET @Database_ID = DB_ID ()
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Error Trapping: Check If Temp Table(s) Already Exist(s) And Drop If Applicable
    -----------------------------------------------------------------------------------------------------------------------------
    
    IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_keys_filters', N'U') IS NOT NULL
    BEGIN
    
    	DROP TABLE dbo.#temp_index_breakdown_keys_filters
    
    END
    
    
    IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_size_info', N'U') IS NOT NULL
    BEGIN
    
    	DROP TABLE dbo.#temp_index_breakdown_size_info
    
    END
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Table Insert: Insert Index Key, Include Key, And Filter Definition Values Into Temp Table
    -----------------------------------------------------------------------------------------------------------------------------
    
    SELECT
    	 sqI.[object_id]
    	,sqI.index_id
    	,STUFF (CONVERT (NVARCHAR (MAX), sqI.index_key), 1, 2, N'') AS index_key
    	,STUFF (CONVERT (NVARCHAR (MAX), sqI.include_key), 1, 2, N'') AS include_key
    	,sqI.filter_definition
    	,RANK () OVER
    				(
    					ORDER BY
    						 sqI.[object_id]
    						,CONVERT (NVARCHAR (MAX), sqI.index_key)
    						,sqI.filter_definition
    				) AS dupe_rank
    INTO
    	dbo.#temp_index_breakdown_keys_filters
    FROM
    
    	(
    		SELECT
    			 I.[object_id]
    			,I.index_id
    			,(
    				SELECT
    					  N', '
    					+ C.name
    					+ N' • '
    					+ TYPE_NAME (C.user_type_id)
    					+ ISNULL ((N': [ ' + (CASE
    											WHEN C.system_type_id <> C.user_type_id THEN LOWER (TYPE_NAME (C.system_type_id))
    											END) + N' ]'), N'')
    					+ N' '
    					+ (CASE
    							WHEN TY.name NOT IN (N'bigint', N'bit', N'date', N'datetime', N'datetime2', N'datetimeoffset', N'decimal', N'float', N'int', N'money', N'numeric', N'real', N'smalldatetime', N'smallint', N'smallmoney', N'time', N'tinyint') THEN CONVERT (NVARCHAR (30), C.max_length)
    							ELSE CONVERT (NVARCHAR (30), C.max_length) + N' (' + CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Precision')) + N',' + ISNULL (CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Scale')), 0) + N')'
    							END)
    					+ N' '
    					+ (CASE
    							WHEN IC.is_descending_key = 0 THEN N'[A]'
    							WHEN IC.is_descending_key = 1 THEN N'[D]'
    							ELSE N'[N/A]'
    							END) AS [text()]
    				FROM
    					sys.index_columns IC
    					INNER JOIN sys.columns C ON C.[object_id] = IC.[object_id]
    						AND C.column_id = IC.column_id
    					INNER JOIN sys.types TY ON TY.user_type_id = C.user_type_id
    				WHERE
    					IC.is_included_column = 0
    					AND IC.[object_id] = I.[object_id]
    					AND IC.index_id = I.index_id
    				ORDER BY
    					IC.key_ordinal
    				FOR
    					 XML PATH ('')
    					,TYPE
    			 ) AS index_key
    			,(
    				SELECT
    					  N', '
    					+ C.name
    					+ N' • '
    					+ TYPE_NAME (C.user_type_id)
    					+ ISNULL ((N': [ ' + (CASE
    											WHEN C.system_type_id <> C.user_type_id THEN LOWER (TYPE_NAME (C.system_type_id))
    											END) + N' ]'), N'')
    					+ N' '
    					+ (CASE
    							WHEN TY.name NOT IN (N'bigint', N'bit', N'date', N'datetime', N'datetime2', N'datetimeoffset', N'decimal', N'float', N'int', N'money', N'numeric', N'real', N'smalldatetime', N'smallint', N'smallmoney', N'time', N'tinyint') THEN CONVERT (NVARCHAR (30), C.max_length)
    							ELSE CONVERT (NVARCHAR (30), C.max_length) + N' (' + CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Precision')) + N',' + ISNULL (CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Scale')), 0) + N')'
    							END) AS [text()]
    				FROM
    					sys.index_columns IC
    					INNER JOIN sys.columns C ON C.[object_id] = IC.[object_id]
    						AND C.column_id = IC.column_id
    					INNER JOIN sys.types TY ON TY.user_type_id = C.user_type_id
    				WHERE
    					IC.is_included_column = 1
    					AND IC.[object_id] = I.[object_id]
    					AND IC.index_id = I.index_id
    				ORDER BY
    					IC.key_ordinal
    				FOR
    					 XML PATH ('')
    					,TYPE
    			 ) AS include_key
    			,I.filter_definition
    		FROM
    			sys.indexes I
    	) sqI
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Table Insert: Insert Size Values Into Temp Table
    -----------------------------------------------------------------------------------------------------------------------------
    
    SELECT
    	 DDPS.[object_id]
    	,DDPS.index_id
    	,SUM (CASE
    			WHEN DDPS.index_id < 2 THEN DDPS.row_count
    			END) AS [rows]
    	,SUM (DDPS.reserved_page_count) AS total_pages
    	,SUM (DDPS.used_page_count) AS used_pages
    	,SUM (CASE
    			WHEN DDPS.index_id < 2 THEN DDPS.in_row_data_page_count + DDPS.lob_used_page_count + DDPS.row_overflow_used_page_count
    			ELSE DDPS.lob_used_page_count + DDPS.row_overflow_used_page_count
    			END) AS data_pages
    INTO
    	dbo.#temp_index_breakdown_size_info
    FROM
    	sys.dm_db_partition_stats DDPS
    GROUP BY
    	 DDPS.[object_id]
    	,DDPS.index_id
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Main Query: Final Display / Output
    -----------------------------------------------------------------------------------------------------------------------------
    
    SELECT
    	 (CASE
    		WHEN sqBAQ.row_filter = 1 THEN sqBAQ.[type]
    		ELSE ''
    		END) AS object_type
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN DB_NAME ()
    		ELSE ''
    		END) AS [database_name]
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN SCHEMA_NAME (sqBAQ.[schema_id])
    		ELSE ''
    		END) AS [schema_name]
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN OBJECT_NAME (sqBAQ.[object_id])
    		ELSE ''
    		END) AS [object_name]
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN sqBAQ.create_date
    		ELSE ''
    		END) AS create_date
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN caMDKL.modify_date
    		ELSE ''
    		END) AS modify_date
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (25), CONVERT (MONEY, sqBAQ.[rows]), 1)), 4, 22))
    		ELSE ''
    		END) AS [rows]
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.total_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS total_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.used_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS used_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.unused_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS unused_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.data_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS data_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.index_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS index_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (6), CONVERT (DECIMAL (5, 2), ISNULL (((sqBAQ.data_pages + .0) / sqBAQ.used_pages) * 100, 0)))
    		ELSE ''
    		END) AS pct_data
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (6), CONVERT (DECIMAL (5, 2), ISNULL (((sqBAQ.index_pages + .0) / sqBAQ.used_pages) * 100, 0)))
    		ELSE ''
    		END) AS pct_index
    	,sqBAQ.type_desc AS index_type
    	,ISNULL (sqBAQ.index_name, '') AS index_name
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		WHEN sqBAQ.is_primary_key = 0 AND sqBAQ.is_unique = 0 THEN REPLICATE ('.', 6)
    		WHEN sqBAQ.is_system_named = 0 THEN 'No'
    		WHEN sqBAQ.is_system_named = 1 THEN 'Yes'
    		ELSE ''
    		END) AS system_named
    	,(CASE
    		WHEN sqBAQ.is_primary_key = 1 THEN 'Yes'
    		ELSE ''
    		END) AS is_pk
    	,(CASE
    		WHEN sqBAQ.is_unique_constraint = 1 THEN 'C'
    		WHEN sqBAQ.is_unique = 1 THEN 'I'
    		ELSE ''
    		END) AS [unique]
    	,(CASE
    		WHEN sqBAQ.is_disabled = 1 THEN 'Yes'
    		ELSE ''
    		END) AS [disabled]
    	,(CASE
    		WHEN sqBAQ.is_hypothetical = 1 THEN 'Yes'
    		ELSE ''
    		END) AS hypothetical
    	,REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, sqCC.total_columns), 1)), 4, 23)) AS total_columns
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, caMDKL.[index_columns]), 1)), 4, 23))
    		END) AS [index_columns]
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, caMDKL.include_columns), 1)), 4, 23))
    		END) AS include_columns
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, (caMDKL.[index_columns] / sqCC.total_columns) * 100), 1)), 1, 23))
    		END) AS index_pct_of_columns
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, (caMDKL.include_columns / sqCC.total_columns) * 100), 1)), 1, 23))
    		END) AS include_pct_of_columns
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, ((caMDKL.[index_columns] + caMDKL.include_columns) / sqCC.total_columns) * 100), 1)), 1, 23))
    		END) AS total_pct_of_columns
    	,CONVERT (VARCHAR (25), CONVERT (MONEY, (ISNULL (sqBAQ.individual_index_pages, 0) * 8) / 1024.0), 1) AS key_mb
    	,CONVERT (VARCHAR (6), CONVERT (DECIMAL (5, 2), ISNULL (((sqBAQ.individual_index_pages + .0) / sqBAQ.index_pages) * 100, 0))) AS key_mb_pct
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (25), CONVERT (MONEY, sqKL.total_max_length), 1)), 4, 22))
    		END) AS max_key_size_bytes
    	,(CASE
    		WHEN sqKL.total_max_length > 900 THEN 'Yes'
    		ELSE ''
    		END) AS over_key_size_limit
    	,ISNULL (ttIBKF.index_key, N'') AS index_key
    	,ISNULL (ttIBKF.include_key, N'') AS include_key
    	,ISNULL (ttIBKF.filter_definition, N'') AS filter_definition
    	,(CASE
    		WHEN sqED02.dupe_id IS NOT NULL THEN CONVERT (VARCHAR (20), sqED02.dupe_id) + ' - [' + CONVERT (VARCHAR (11), sqED02.total_dupes) + ']'
    		ELSE ''
    		END) AS dupe_id
    	,sqBAQ.is_unused AS unused
    	,ISNULL (CONVERT (VARCHAR (10), STATS_DATE (sqBAQ.[object_id], sqBAQ.index_id), 23), '') AS statistics_date
    	,(CASE
    		WHEN sqBAQ.[allow_row_locks] = 0 THEN 'No'
    		WHEN sqBAQ.[allow_row_locks] = 1 THEN 'Yes'
    		ELSE ''
    		END) AS row_locks
    	,(CASE
    		WHEN sqBAQ.[allow_page_locks] = 0 THEN 'No'
    		WHEN sqBAQ.[allow_page_locks] = 1 THEN 'Yes'
    		ELSE ''
    		END) AS page_locks
    	,(CASE
    		WHEN sqBAQ.[ignore_dup_key] = 0 THEN 'No'
    		WHEN sqBAQ.[ignore_dup_key] = 1 THEN 'Yes'
    		ELSE ''
    		END) AS ignore_dupes
    	,(CASE
    		WHEN sqBAQ.no_recompute = 0 THEN 'Yes'
    		WHEN sqBAQ.no_recompute = 1 THEN 'No'
    		ELSE ''
    		END) AS auto_stats
    	,(CASE
    		WHEN sqBAQ.is_padded = 0 THEN 'No'
    		WHEN sqBAQ.is_padded = 1 THEN 'Yes'
    		ELSE ''
    		END) AS padded
    	,(CASE
    		WHEN sqBAQ.fill_factor = 0 THEN 100
    		ELSE sqBAQ.fill_factor
    		END) AS fill_factor
    	,(CASE
    		WHEN sqBAQ.user_seeks > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_seeks)
    		ELSE ''
    		END) AS user_seeks
    	,(CASE
    		WHEN sqBAQ.user_scans > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_scans)
    		ELSE ''
    		END) AS user_scans
    	,(CASE
    		WHEN sqBAQ.user_lookups > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_lookups)
    		ELSE ''
    		END) AS user_lookups
    	,(CASE
    		WHEN sqBAQ.user_updates > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_updates)
    		ELSE ''
    		END) AS user_updates
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_seek, 23), '') AS last_user_seek
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_scan, 23), '') AS last_user_scan
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_lookup, 23), '') AS last_user_lookup
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_update, 23), '') AS last_user_update
    	,(CASE
    		WHEN sqBAQ.system_seeks > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_seeks)
    		ELSE ''
    		END) AS system_seeks
    	,(CASE
    		WHEN sqBAQ.system_scans > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_scans)
    		ELSE ''
    		END) AS system_scans
    	,(CASE
    		WHEN sqBAQ.system_lookups > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_lookups)
    		ELSE ''
    		END) AS system_lookups
    	,(CASE
    		WHEN sqBAQ.system_updates > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_updates)
    		ELSE ''
    		END) AS system_updates
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_seek, 23), '') AS last_system_seek
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_scan, 23), '') AS last_system_scan
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_lookup, 23), '') AS last_system_lookup
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_update, 23), '') AS last_system_update
    FROM
    
    	(
    		SELECT
    			 O.[type]
    			,O.[schema_id]
    			,O.[object_id]
    			,CONVERT (VARCHAR (10), O.create_date, 23) AS create_date
    			,CONVERT (VARCHAR (10), O.modify_date, 23) AS modify_date
    			,sqDDPS.[rows]
    			,sqDDPS.total_pages
    			,sqDDPS.used_pages
    			,(CASE
    				WHEN sqDDPS.total_pages > sqDDPS.used_pages THEN sqDDPS.total_pages - sqDDPS.used_pages
    				ELSE 0
    				END) AS unused_pages
    			,sqDDPS.data_pages
    			,(CASE
    				WHEN sqDDPS.used_pages > sqDDPS.data_pages THEN sqDDPS.used_pages - sqDDPS.data_pages
    				ELSE 0
    				END) AS index_pages
    			,sqI.type_desc
    			,sqI.name AS index_name
    			,sqI.is_system_named
    			,sqI.is_primary_key
    			,sqI.is_unique
    			,sqI.is_disabled
    			,sqI.is_hypothetical
    			,sqI.individual_index_pages
    			,sqI.is_unused
    			,sqI.[allow_row_locks]
    			,sqI.[allow_page_locks]
    			,sqI.[ignore_dup_key]
    			,sqI.no_recompute
    			,sqI.is_padded
    			,sqI.fill_factor
    			,sqI.user_seeks
    			,sqI.user_scans
    			,sqI.user_lookups
    			,sqI.user_updates
    			,sqI.last_user_seek
    			,sqI.last_user_scan
    			,sqI.last_user_lookup
    			,sqI.last_user_update
    			,sqI.system_seeks
    			,sqI.system_scans
    			,sqI.system_lookups
    			,sqI.system_updates
    			,sqI.last_system_seek
    			,sqI.last_system_scan
    			,sqI.last_system_lookup
    			,sqI.last_system_update
    			,sqI.is_unique_constraint
    			,sqI.index_id
    			,sqI.row_filter
    		FROM
    			sys.objects O
    			INNER JOIN
    
    				(
    					SELECT
    						 ttIBSI.[object_id]
    						,SUM (ttIBSI.[rows]) AS [rows]
    						,SUM (ttIBSI.total_pages) AS total_pages
    						,SUM (ttIBSI.used_pages) AS used_pages
    						,SUM (ttIBSI.data_pages) AS data_pages
    					FROM
    						dbo.#temp_index_breakdown_size_info ttIBSI
    					GROUP BY
    						ttIBSI.[object_id]
    				) sqDDPS ON sqDDPS.[object_id] = O.[object_id]
    
    			INNER JOIN
    
    				(
    					SELECT
    						 I.[object_id]
    						,I.type_desc
    						,I.name
    						,KC.is_system_named
    						,I.is_primary_key
    						,I.is_unique
    						,I.is_disabled
    						,I.is_hypothetical
    						,(CASE
    							WHEN ttIBSI.used_pages > ttIBSI.data_pages THEN ttIBSI.used_pages - ttIBSI.data_pages
    							END) AS individual_index_pages
    						,(CASE
    							WHEN I.[type] = 0 THEN ''
    							WHEN I.[type] = 1 THEN REPLICATE ('.', 6)
    							WHEN I.is_primary_key = 1 THEN REPLICATE ('.', 6)
    							WHEN I.is_unique = 1 THEN REPLICATE ('.', 6)
    							WHEN EXISTS
    
    								(
    									SELECT
    										*
    									FROM
    										sys.index_columns IC
    										INNER JOIN sys.foreign_key_columns FKC ON FKC.parent_object_id = IC.[object_id]
    											AND FKC.parent_column_id = IC.column_id
    									WHERE
    										IC.[object_id] = I.[object_id]
    										AND IC.index_id = I.index_id
    								) THEN REPLICATE ('.', 6)
    
    							WHEN DDIUS.[object_id] IS NOT NULL THEN (CASE
    																		WHEN DDIUS.user_seeks + DDIUS.user_scans + DDIUS.user_lookups + DDIUS.user_updates = 0 THEN 'Y/N'
    																		ELSE 'No'
    																		END)
    							ELSE 'Yes'
    							END) AS is_unused
    						,I.[allow_row_locks]
    						,I.[allow_page_locks]
    						,I.[ignore_dup_key]
    						,S.no_recompute
    						,I.is_padded
    						,I.fill_factor
    						,DDIUS.user_seeks
    						,DDIUS.user_scans
    						,DDIUS.user_lookups
    						,DDIUS.user_updates
    						,DDIUS.last_user_seek
    						,DDIUS.last_user_scan
    						,DDIUS.last_user_lookup
    						,DDIUS.last_user_update
    						,DDIUS.system_seeks
    						,DDIUS.system_scans
    						,DDIUS.system_lookups
    						,DDIUS.system_updates
    						,DDIUS.last_system_seek
    						,DDIUS.last_system_scan
    						,DDIUS.last_system_lookup
    						,DDIUS.last_system_update
    						,I.is_unique_constraint
    						,I.index_id
    						,ROW_NUMBER () OVER
    											(
    												PARTITION BY
    													I.[object_id]
    												ORDER BY
    													 I.is_primary_key DESC
    													,(CASE
    														WHEN I.[type] = 0 THEN 'Z'
    														ELSE 'A'
    														END)
    													,I.[type]
    													,I.name
    											) AS row_filter
    					FROM
    						sys.indexes I
    						INNER JOIN dbo.#temp_index_breakdown_size_info ttIBSI ON ttIBSI.[object_id] = I.[object_id]
    							AND ttIBSI.index_id = I.index_id
    						LEFT JOIN sys.key_constraints KC ON KC.parent_object_id = I.[object_id]
    							AND KC.unique_index_id = I.index_id
    						LEFT JOIN sys.stats S ON S.[object_id] = I.[object_id]
    							AND S.stats_id = I.index_id
    						LEFT JOIN master.sys.dm_db_index_usage_stats DDIUS ON DDIUS.[object_id] = I.[object_id]
    							AND DDIUS.index_id = I.index_id
    							AND DDIUS.database_id = @Database_ID
    				) sqI ON sqI.[object_id] = O.[object_id]
    
    		WHERE
    			O.[type] IN ('U', 'V')
    			AND O.is_ms_shipped = 0
    			AND NOT
    
    				(
    					SCHEMA_NAME (O.[schema_id]) = N'dbo'
    					AND O.name = N'sysdiagrams'
    					AND O.[type] = 'U'
    				)
    
    	) sqBAQ
    
    	INNER JOIN
    
    		(
    			SELECT
    				 C.[object_id]
    				,COUNT (*) + .0 AS total_columns
    			FROM
    				sys.columns C
    			GROUP BY
    				C.[object_id]
    		) sqCC ON sqCC.[object_id] = sqBAQ.[object_id]
    
    	LEFT JOIN dbo.#temp_index_breakdown_keys_filters ttIBKF ON ttIBKF.[object_id] = sqBAQ.[object_id]
    		AND ttIBKF.index_id = sqBAQ.index_id
    	LEFT JOIN
    
    		(
    			SELECT
    				 IC.[object_id]
    				,IC.index_id
    				,SUM (C.max_length) AS total_max_length
    			FROM
    				sys.index_columns IC
    				INNER JOIN sys.columns C ON C.[object_id] = IC.[object_id]
    					AND C.column_id = IC.column_id
    			WHERE
    				IC.is_included_column = 0
    			GROUP BY
    				 IC.[object_id]
    				,IC.index_id
    		) sqKL ON sqKL.[object_id] = sqBAQ.[object_id] AND sqKL.index_id = sqBAQ.index_id
    
    	LEFT JOIN
    
    		(
    			SELECT
    				 sqED01.dupe_rank
    				,sqED01.total_dupes
    				,ROW_NUMBER () OVER
    									(
    										ORDER BY
    											(SELECT NULL)
    									) AS dupe_id
    			FROM
    
    				(
    					SELECT
    						 ttIBKF.dupe_rank
    						,COUNT (*) AS total_dupes
    					FROM
    						dbo.#temp_index_breakdown_keys_filters ttIBKF
    					GROUP BY
    						ttIBKF.dupe_rank
    					HAVING
    						COUNT (*) > 1
    				) sqED01
    
    		) sqED02 ON sqED02.dupe_rank = ttIBKF.dupe_rank
    
    	CROSS APPLY
    
    		(
    			SELECT
    				 (CASE
    					WHEN sqBAQ.modify_date = sqBAQ.create_date THEN REPLICATE ('.', 18)
    					ELSE sqBAQ.modify_date
    					END) AS modify_date
    				,LEN (ttIBKF.index_key) - LEN (REPLACE (ttIBKF.index_key, '•', '')) AS [index_columns]
    				,ISNULL (LEN (ttIBKF.include_key) - LEN (REPLACE (ttIBKF.include_key, '•', '')), 0) AS include_columns
    		) caMDKL
    
    ORDER BY
    	 sqBAQ.[type]
    	,SCHEMA_NAME (sqBAQ.[schema_id])
    	,OBJECT_NAME (sqBAQ.[object_id])
    	,sqBAQ.row_filter
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Cleanup: Drop Any Remaining Temp Tables
    -----------------------------------------------------------------------------------------------------------------------------
    
    IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_keys_filters', N'U') IS NOT NULL
    BEGIN
    
    	DROP TABLE dbo.#temp_index_breakdown_keys_filters
    
    END
    
    
    IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_size_info', N'U') IS NOT NULL
    BEGIN
    
    	DROP TABLE dbo.#temp_index_breakdown_size_info
    
    END

    public by pan  245512  2  5  0

    Replace all occurrences of a string in DB

    Searches and replaces all of occurrences of a string in all tables and all fields of the given Database
    <?php
    /**
    * @kudos http://stackoverflow.com/a/10182498
    */
     
    header("Content-Type: text/plain");
    
    $host = "localhost";
    $username = "root";
    $password = "";
    $database = "mydatabase";
    $string_to_replace  = 'old.example.com';
    $new_string = 'new.example.com';
    
    // Connect to database server
    mysql_connect($host, $username, $password);
    
    // Select database
    mysql_select_db($database);
    
    // List all tables in database
    $sql = "SHOW TABLES FROM ".$database;
    $tables_result = mysql_query($sql);
    
    if (!$tables_result) {
        echo "Database error, could not list tables\nMySQL error: " . mysql_error();
        exit;
    }
    
    echo "In these fields '$string_to_replace' have been replaced with '$new_string'\n\n";
    while ($table = mysql_fetch_row($tables_result)) {
        echo "Table: {$table[0]}\n";
        $fields_result = mysql_query("SHOW COLUMNS FROM ".$table[0]);
        if (!$fields_result) {
            echo 'Could not run query: ' . mysql_error();
            exit;
        }
        if (mysql_num_rows($fields_result) > 0) {
            while ($field = mysql_fetch_assoc($fields_result)) {
                if (stripos($field['Type'], "VARCHAR") !== false || stripos($field['Type'], "TEXT") !== false) {
                    echo "  ".$field['Field']."\n";
                    $sql = "UPDATE ".$table[0]." SET ".$field['Field']." = replace(".$field['Field'].", '$string_to_replace', '$new_string')";
                    mysql_query($sql);
                }
            }
            echo "\n";
        }
    }
    
    mysql_free_result($tables_result);

    public by Bobby Kozora  38178  1  3  0

    Retrieve Magento Database Configuration

    Retrieve Magento Database Configuration: magento-database-config.php
    <?php
    
    require_once ("../app/Mage.php");
    $app = Mage::app('default');
    
    $config  = Mage::getConfig()->getResourceConnectionConfig("default_setup");
    
    $dbinfo = array(
      "host"    => $config->host,
      "user"    => $config->username,
      "pass"    => $config->password,
      "dbname"  => $config->dbname
    );
    
    
    

    public by marksimon232  4130  7  6  4

    MySql Database: US & CANADA Cities Database

    SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
    SET time_zone = "+00:00";
     
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
     
    CREATE TABLE IF NOT EXISTS `cities` (
      `id` SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
      `city` VARCHAR(128) NOT NULL,
      `state` ENUM('AB','AK','AL','AR','AZ','BC','CA','CO','CT','DE','FL','GA','HI','IA','ID','IL','IN','KS','KY','LA','MA','MB','MD','ME','MI','MN','MO','MS','MT','NB','NC','ND','NE','NH','NJ','NL','NM','NS','NT','NU','NV','NY','OH','OK','ON','OR','PA','PE','QC','RI','SC','SD','SK','TN','TX','UT','VA','VT','WA','WI','WV','WY','YT') NOT NULL,
      `country` ENUM('US','CA') NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8017 ;
     
    INSERT INTO `cities` (`id`, `city`, `state`, `country`) VALUES
    (1, 'Acme', 'AB', 'CA'),
    (2, 'Airdrie', 'AB', 'CA'),
    (3, 'Alix', 'AB', 'CA'),
    (4, 'Amber Valley', 'AB', 'CA'),
    (5, 'Ardrossan', 'AB', 'CA'),
    (6, 'Athabasca', 'AB', 'CA'),
    (7, 'Balzac', 'AB', 'CA'),
    (8, 'Banff', 'AB', 'CA'),
    (9, 'Barrhead', 'AB', 'CA'),
    (10, 'Bashaw', 'AB', 'CA'),
    (11, 'Beaumont', 'AB', 'CA'),
    (12, 'Beiseker', 'AB', 'CA'),
    (13, 'Bellevue', 'AB', 'CA'),
    (14, 'Benalto', 'AB', 'CA'),
    (15, 'Bilby', 'AB', 'CA'),
    (16, 'Black Diamond', 'AB', 'CA'),
    (17, 'Blackfalds', 'AB', 'CA'),
    (18, 'Blackie', 'AB', 'CA'),
    (19, 'Blairmore', 'AB', 'CA'),
    (20, 'Bluesky', 'AB', 'CA'),
    (21, 'Bluffton', 'AB', 'CA'),
    (22, 'Bon Accord', 'AB', 'CA'),
    (23, 'Bonnyville', 'AB', 'CA'),
    (24, 'Bow Island', 'AB', 'CA'),
    (25, 'Bowness', 'AB', 'CA'),
    (26, 'Boyle', 'AB', 'CA'),
    (27, 'Breton', 'AB', 'CA'),
    (28, 'Brooks', 'AB', 'CA'),
    (29, 'Busby', 'AB', 'CA'),
    (30, 'Calgary', 'AB', 'CA'),
    (31, 'Calmar', 'AB', 'CA'),
    (32, 'Camrose', 'AB', 'CA'),
    (33, 'Canmore', 'AB', 'CA'),
    (34, 'Cardston', 'AB', 'CA'),
    (35, 'Caroline', 'AB', 'CA'),
    (36, 'Carseland', 'AB', 'CA'),
    (37, 'Carstairs', 'AB', 'CA'),
    (38, 'Champion', 'AB', 'CA'),
    (39, 'Cherhill', 'AB', 'CA'),
    (40, 'Chip Lake', 'AB', 'CA'),
    (41, 'Chipman', 'AB', 'CA'),
    (42, 'Clairmont', 'AB', 'CA'),
    (43, 'Claresholm', 'AB', 'CA'),
    (44, 'Clive', 'AB', 'CA'),
    (45, 'Coaldale', 'AB', 'CA'),
    (46, 'Cold Lake', 'AB', 'CA'),
    (47, 'Coleman', 'AB', 'CA'),
    (48, 'Coronation', 'AB', 'CA'),
    (49, 'Crossfield', 'AB', 'CA'),
    (50, 'Daysland', 'AB', 'CA'),
    (51, 'Delburne', 'AB', 'CA'),
    (52, 'Devon', 'AB', 'CA'),
    (53, 'Didsbury', 'AB', 'CA'),
    (54, 'Dowling', 'AB', 'CA'),
    (55, 'Drayton Valley', 'AB', 'CA'),
    (56, 'Drumheller', 'AB', 'CA'),
    (57, 'Duchess', 'AB', 'CA'),
    (58, 'Duffield', 'AB', 'CA'),
    (59, 'Eaglesham', 'AB', 'CA'),
    (60, 'Edmonton', 'AB', 'CA'),
    (61, 'Edson', 'AB', 'CA'),
    (62, 'Elk Point', 'AB', 'CA'),
    (63, 'Endiang', 'AB', 'CA'),
    (64, 'Entwistle', 'AB', 'CA'),
    (65, 'Fairview', 'AB', 'CA'),
    (66, 'Faust', 'AB', 'CA'),
    (67, 'Flatbush', 'AB', 'CA'),
    (68, 'Foremost', 'AB', 'CA'),
    (69, 'Forestburg', 'AB', 'CA'),
    (70, 'Fort Macleod', 'AB', 'CA'),
    (71, 'Fort McMurray', 'AB', 'CA'),
    (72, 'Fort Saskatchewan', 'AB', 'CA'),
    (73, 'Fort Vermilion', 'AB', 'CA'),
    (75, 'Girouxville', 'AB', 'CA'),
    (76, 'Gleichen', 'AB', 'CA'),
    (77, 'Glendon', 'AB', 'CA'),
    (78, 'Glenwood', 'AB', 'CA'),
    (79, 'Goodfish Lake', 'AB', 'CA'),
    (80, 'Grand Centre', 'AB', 'CA'),
    (81, 'Grande Prairie', 'AB', 'CA'),
    (82, 'Grimshaw', 'AB', 'CA'),
    (83, 'Halkirk', 'AB', 'CA'),
    (84, 'Hanna', 'AB', 'CA'),
    (85, 'Hardisty', 'AB', 'CA'),
    (86, 'Heisler', 'AB', 'CA'),
    (87, 'Helina', 'AB', 'CA'),
    (88, 'High Level', 'AB', 'CA'),
    (89, 'High Prairie', 'AB', 'CA'),
    (90, 'High River', 'AB', 'CA'),
    (91, 'Hinton', 'AB', 'CA'),
    (92, 'Hobbema', 'AB', 'CA'),
    (93, 'Horburg', 'AB', 'CA'),
    (94, 'Innisfail', 'AB', 'CA'),
    (95, 'Irricana', 'AB', 'CA'),
    (96, 'Irvine', 'AB', 'CA'),
    (97, 'Jarvie', 'AB', 'CA'),
    (98, 'Jasper', 'AB', 'CA'),
    (99, 'Joussard', 'AB', 'CA'),
    (100, 'Keoma', 'AB', 'CA'),
    (101, 'Killam', 'AB', 'CA'),
    (102, 'Kinuso', 'AB', 'CA'),
    (103, 'Kitscoty', 'AB', 'CA'),
    (104, 'Lac La Biche', 'AB', 'CA'),
    (105, 'Lacombe', 'AB', 'CA'),
    (106, 'Lake Louise', 'AB', 'CA'),
    (107, 'Lamont', 'AB', 'CA'),
    (108, 'Lancaster Park', 'AB', 'CA'),
    (109, 'Leduc', 'AB', 'CA'),
    (110, 'Legal', 'AB', 'CA'),
    (111, 'Lethbridge', 'AB', 'CA'),
    (112, 'Lloydminster', 'AB', 'CA'),
    (113, 'Looma', 'AB', 'CA'),
    (114, 'Mackay', 'AB', 'CA'),
    (115, 'Magrath', 'AB', 'CA'),
    (116, 'Mannville', 'AB', 'CA'),
    (117, 'Marwayne', 'AB', 'CA'),
    (118, 'McLennan', 'AB', 'CA'),
    (119, 'McMurray', 'AB', 'CA'),
    (120, 'Medicine Hat', 'AB', 'CA'),
    (121, 'Midnapore', 'AB', 'CA'),
    (122, 'Milk River', 'AB', 'CA'),
    (123, 'Millet', 'AB', 'CA'),
    (124, 'Monarch', 'AB', 'CA'),
    (125, 'Morinville', 'AB', 'CA'),
    (126, 'Munson', 'AB', 'CA'),
    (127, 'Nampa', 'AB', 'CA'),
    (128, 'Nanton', 'AB', 'CA'),
    (129, 'Nisku', 'AB', 'CA'),
    (130, 'Nobleford', 'AB', 'CA'),
    (131, 'North Vermilion', 'AB', 'CA'),
    (132, 'Obed', 'AB', 'CA'),
    (133, 'Okotoks', 'AB', 'CA'),
    (134, 'Olds', 'AB', 'CA'),
    (135, 'Onoway', 'AB', 'CA'),
    (136, 'Paradise Valley', 'AB', 'CA'),
    (137, 'Patricia', 'AB', 'CA'),
    (138, 'Peace River', 'AB', 'CA'),
    (139, 'Peers', 'AB', 'CA'),
    (140, 'Penhold', 'AB', 'CA'),
    (141, 'Pincher Creek', 'AB', 'CA'),
    (142, 'Plamondon', 'AB', 'CA'),
    (143, 'Ponoka', 'AB', 'CA'),
    (144, 'Provost', 'AB', 'CA'),
    (145, 'Radway', 'AB', 'CA'),
    (146, 'Ralston', 'AB', 'CA'),
    (147, 'Raymond', 'AB', 'CA'),
    (148, 'Red Deer', 'AB', 'CA'),
    (149, 'Redcliff', 'AB', 'CA'),
    (150, 'Rimbey', 'AB', 'CA'),
    (151, 'Rocky Mountain House', 'AB', 'CA'),
    (152, 'Rosalind', 'AB', 'CA'),
    (153, 'Rosedale', 'AB', 'CA'),
    (154, 'Rosemary', 'AB', 'CA'),
    (155, 'Rycroft', 'AB', 'CA'),
    (156, 'Saint Albert', 'AB', 'CA'),
    (157, 'Saint Paul', 'AB', 'CA'),
    (158, 'Saint Vincent', 'AB', 'CA'),
    (159, 'Sangudo', 'AB', 'CA'),
    (160, 'Scandia', 'AB', 'CA'),
    (161, 'Sexsmith', 'AB', 'CA'),
    (162, 'Sherwood Park', 'AB', 'CA'),
    (163, 'Slave Lake', 'AB', 'CA'),
    (164, 'Smith', 'AB', 'CA'),
    (165, 'Smoky Lake', 'AB', 'CA'),
    (166, 'Spedden', 'AB', 'CA'),
    (167, 'Spirit River', 'AB', 'CA'),
    (168, 'Spruce Grove', 'AB', 'CA'),
    (169, 'Standard', 'AB', 'CA'),
    (170, 'Stettler', 'AB', 'CA'),
    (171, 'Stony Plain', 'AB', 'CA'),
    (172, 'Suffield', 'AB', 'CA'),
    (173, 'Sylvan Lake', 'AB', 'CA'),
    (174, 'Taber', 'AB', 'CA'),
    (175, 'Thorhild', 'AB', 'CA'),
    (176, 'Three Hills', 'AB', 'CA'),
    (177, 'Tofield', 'AB', 'CA'),
    (178, 'Trochu', 'AB', 'CA'),
    (179, 'Two Hills', 'AB', 'CA'),
    (180, 'Valleyview', 'AB', 'CA'),
    (181, 'Vauxhall', 'AB', 'CA'),
    (182, 'Vegreville', 'AB', 'CA'),
    (183, 'Vermilion', 'AB', 'CA'),
    (184, 'Veteran', 'AB', 'CA'),
    (185, 'Viking', 'AB', 'CA'),
    (186, 'Vulcan', 'AB', 'CA'),
    (187, 'Wainwright', 'AB', 'CA'),
    (188, 'Watino', 'AB', 'CA'),
    (189, 'Webster', 'AB', 'CA'),
    (190, 'Wembley', 'AB', 'CA'),
    (191, 'Westlock', 'AB', 'CA'),
    (192, 'Wetaskiwin', 'AB', 'CA'),
    (193, 'Whitecourt', 'AB', 'CA'),
    (194, 'Widewater', 'AB', 'CA'),
    (195, 'Wildwood', 'AB', 'CA'),
    (196, 'Wrentham', 'AB', 'CA'),
    (197, 'Abbotsford', 'BC', 'CA'),
    (198, 'Abbottsford', 'BC', 'CA'),
    (199, 'Agassiz', 'BC', 'CA'),
    (200, 'Aiyansh', 'BC', 'CA'),
    (201, 'Albert Canyon', 'BC', 'CA'),
    (202, 'Alert Bay', 'BC', 'CA'),
    (203, 'Armstrong', 'BC', 'CA'),
    (204, 'Ashcroft', 'BC', 'CA'),
    (205, 'Atlin', 'BC', 'CA'),
    (206, 'Bamfield', 'BC', 'CA'),
    (207, 'Barriere', 'BC', 'CA'),
    (208, 'Bella Coola', 'BC', 'CA'),
    (209, 'Black Pines', 'BC', 'CA'),
    (210, 'Blucher Hall', 'BC', 'CA'),
    (211, 'Boswell', 'BC', 'CA'),
    (212, 'Brackendale', 'BC', 'CA'),
    (213, 'Brentwood Bay', 'BC', 'CA'),
    (214, 'Bridge Lake', 'BC', 'CA'),
    (215, 'Burnaby', 'BC', 'CA'),
    (216, 'Burns Lake', 'BC', 'CA'),
    (217, 'Campbell River', 'BC', 'CA'),
    (218, 'Canal Flats', 'BC', 'CA'),
    (219, 'Castlegar', 'BC', 'CA'),
    (220, 'Cecil Lake', 'BC', 'CA'),
    (221, 'Chase', 'BC', 'CA'),
    (222, 'Chemainus', 'BC', 'CA'),
    (223, 'Cherryville', 'BC', 'CA'),
    (224, 'Chief Lake', 'BC', 'CA'),
    (225, 'Chilliwack', 'BC', 'CA'),
    (226, 'Cinema', 'BC', 'CA'),
    (227, 'Clearwater', 'BC', 'CA'),
    (228, 'Clo-oose', 'BC', 'CA'),
    (229, 'Cloverdale', 'BC', 'CA'),
    (230, 'Coal Creek', 'BC', 'CA'),
    (231, 'Cobble Hill', 'BC', 'CA'),
    (232, 'Colebrook', 'BC', 'CA'),
    (233, 'Comox', 'BC', 'CA'),
    (234, 'Coquitlam', 'BC', 'CA'),
    (235, 'Cortes Bay', 'BC', 'CA'),
    (236, 'Courtenay', 'BC', 'CA'),
    (237, 'Cowichan Bay', 'BC', 'CA'),
    (238, 'Cranbrook', 'BC', 'CA'),
    (239, 'Creston', 'BC', 'CA'),
    (240, 'Crofton', 'BC', 'CA'),
    (241, 'Dawson Creek', 'BC', 'CA'),
    (242, 'Dease Lake', 'BC', 'CA'),
    (243, 'Delta', 'BC', 'CA'),
    (244, 'Deroche', 'BC', 'CA'),
    (245, 'Dot', 'BC', 'CA'),
    (246, 'Duncan', 'BC', 'CA'),
    (247, 'Edgewood', 'BC', 'CA'),
    (248, 'Elko', 'BC', 'CA'),
    (249, 'Enderby', 'BC', 'CA'),
    (250, 'Extension', 'BC', 'CA'),
    (251, 'Fairmont Hot Springs', 'BC', 'CA'),
    (252, 'Fernie', 'BC', 'CA'),
    (253, 'Finmoore', 'BC', 'CA'),
    (254, 'Fort Nelson', 'BC', 'CA'),
    (255, 'Fort Saint James', 'BC', 'CA'),
    (256, 'Fort Saint John', 'BC', 'CA'),
    (257, 'Fraser Lake', 'BC', 'CA'),
    (258, 'Fruitvale', 'BC', 'CA'),
    (259, 'Gabriola', 'BC', 'CA'),
    (260, 'Ganges', 'BC', 'CA'),
    (261, 'Garibaldi', 'BC', 'CA'),
    (262, 'Gibsons', 'BC', 'CA'),
    (263, 'Glacier', 'BC', 'CA'),
    (264, 'Gold Bridge', 'BC', 'CA'),
    (265, 'Golden', 'BC', 'CA'),
    (266, 'Grand Forks', 'BC', 'CA'),
    (267, 'Hagensborg', 'BC', 'CA'),
    (268, 'Hartley Bay', 'BC', 'CA'),
    (269, 'Hazelton', 'BC', 'CA'),
    (270, 'Hedley', 'BC', 'CA'),
    (271, 'Hixon', 'BC', 'CA'),
    (272, 'Houston', 'BC', 'CA'),
    (273, 'Invermere', 'BC', 'CA'),
    (274, 'Isle Pierre', 'BC', 'CA'),
    (275, 'Kamloops', 'BC', 'CA'),
    (276, 'Kelowna', 'BC', 'CA'),
    (277, 'Keremeos', 'BC', 'CA'),
    (278, 'Kimberley', 'BC', 'CA'),
    (279, 'Kimberly', 'BC', 'CA'),
    (280, 'Kincolith', 'BC', 'CA'),
    (281, 'Kitimat', 'BC', 'CA'),
    (282, 'Kitwanga', 'BC', 'CA'),
    (283, 'Ladner', 'BC', 'CA'),
    (284, 'Ladysmith', 'BC', 'CA'),
    (285, 'Lake Cowichan', 'BC', 'CA'),
    (286, 'Langley', 'BC', 'CA'),
    (287, 'Leon', 'BC', 'CA'),
    (288, 'Lillooet', 'BC', 'CA'),
    (289, 'Lumby', 'BC', 'CA'),
    (290, 'Lytton', 'BC', 'CA'),
    (291, 'Mackenzie', 'BC', 'CA'),
    (292, 'Maple Ridge', 'BC', 'CA'),
    (293, 'Marysville', 'BC', 'CA'),
    (294, 'Masset', 'BC', 'CA'),
    (295, 'Mayne', 'BC', 'CA'),
    (296, 'McBride', 'BC', 'CA'),
    (297, 'McMurphy', 'BC', 'CA'),
    (298, 'Merritt', 'BC', 'CA'),
    (299, 'Merville', 'BC', 'CA'),
    (300, 'Midway', 'BC', 'CA'),
    (301, 'Mission', 'BC', 'CA'),
    (302, 'Moberly Lake', 'BC', 'CA'),
    (303, 'Mud River', 'BC', 'CA'),
    (304, 'Nadu', 'BC', 'CA'),
    (305, 'Nakusp', 'BC', 'CA'),
    (306, 'Nanaimo', 'BC', 'CA'),
    (307, 'Nanoose Bay', 'BC', 'CA'),
    (308, 'Nelson', 'BC', 'CA'),
    (309, 'New Denver', 'BC', 'CA'),
    (310, 'New Westminster', 'BC', 'CA'),
    (311, 'Newgate', 'BC', 'CA'),
    (312, 'North Surrey', 'BC', 'CA'),
    (313, 'North Vancouver', 'BC', 'CA'),
    (314, 'Ocean Falls', 'BC', 'CA'),
    (315, 'Okanagan', 'BC', 'CA'),
    (316, 'Oliver', 'BC', 'CA'),
    (317, 'One Hundred Mile House', 'BC', 'CA'),
    (318, 'Osoyoos', 'BC', 'CA'),
    (319, 'Oyama', 'BC', 'CA'),
    (320, 'Pacific', 'BC', 'CA'),
    (321, 'Parksville', 'BC', 'CA'),
    (322, 'Peachland', 'BC', 'CA'),
    (323, 'Pemberton', 'BC', 'CA'),
    (324, 'Penticton', 'BC', 'CA'),
    (325, 'Pitt Meadows', 'BC', 'CA'),
    (326, 'Port Alberni', 'BC', 'CA'),
    (327, 'Port Alice', 'BC', 'CA'),
    (328, 'Port Clements', 'BC', 'CA'),
    (329, 'Port Coquitlam', 'BC', 'CA'),
    (330, 'Port Edward', 'BC', 'CA'),
    (331, 'Port Hardy', 'BC', 'CA'),
    (332, 'Port Mann', 'BC', 'CA'),
    (333, 'Port Mellon', 'BC', 'CA'),
    (334, 'Port Moody', 'BC', 'CA'),
    (335, 'Port Neville', 'BC', 'CA'),
    (336, 'Pouce Coupe', 'BC', 'CA'),
    (337, 'Powell River', 'BC', 'CA'),
    (338, 'Prince George', 'BC', 'CA'),
    (339, 'Prince Rupert', 'BC', 'CA'),
    (340, 'Princeton', 'BC', 'CA'),
    (341, 'Pritchard', 'BC', 'CA'),
    (342, 'Qualicum Beach', 'BC', 'CA'),
    (343, 'Quathiaski Cove', 'BC', 'CA'),
    (344, 'Queen Charlotte', 'BC', 'CA'),
    (345, 'Quesnel', 'BC', 'CA'),
    (346, 'Revelstoke', 'BC', 'CA'),
    (347, 'Richmond', 'BC', 'CA'),
    (348, 'Riondel', 'BC', 'CA'),
    (349, 'Roberts Creek', 'BC', 'CA'),
    (350, 'Rose Lake', 'BC', 'CA'),
    (351, 'Rossland', 'BC', 'CA'),
    (352, 'Salmon Arm', 'BC', 'CA'),
    (353, 'Salmon Valley', 'BC', 'CA'),
    (354, 'Savona', 'BC', 'CA'),
    (355, 'Sayward', 'BC', 'CA'),
    (356, 'Sechelt', 'BC', 'CA'),
    (357, 'Shere', 'BC', 'CA'),
    (358, 'Sicamous', 'BC', 'CA'),
    (359, 'Sidney', 'BC', 'CA'),
    (360, 'Skidegate', 'BC', 'CA'),
    (361, 'Slocan', 'BC', 'CA'),
    (362, 'Smithers', 'BC', 'CA'),
    (363, 'Sointula', 'BC', 'CA'),
    (364, 'Sooke', 'BC', 'CA'),
    (365, 'Sorrento', 'BC', 'CA'),
    (366, 'Squamish', 'BC', 'CA'),
    (367, 'Stephen', 'BC', 'CA'),
    (368, 'Stewart', 'BC', 'CA'),
    (369, 'Sturdies Bay', 'BC', 'CA'),
    (370, 'Summerland', 'BC', 'CA'),
    (371, 'Surrey', 'BC', 'CA'),
    (372, 'Tahsis', 'BC', 'CA'),
    (373, 'Tappen', 'BC', 'CA'),
    (374, 'Taylor', 'BC', 'CA'),
    (375, 'Telegraph Creek', 'BC', 'CA'),
    (376, 'Terrace', 'BC', 'CA'),
    (377, 'Tlell', 'BC', 'CA'),
    (378, 'Tofino', 'BC', 'CA'),
    (379, 'Trail', 'BC', 'CA'),
    (380, 'Tsawwassen', 'BC', 'CA'),
    (381, 'Ucluelet', 'BC', 'CA'),
    (382, 'Union Bay', 'BC', 'CA'),
    (383, 'Valemount', 'BC', 'CA'),
    (384, 'Vancouver', 'BC', 'CA'),
    (385, 'Vanderhoof', 'BC', 'CA'),
    (386, 'Vernon', 'BC', 'CA'),
    (387, 'Victoria', 'BC', 'CA'),
    (388, 'Whaletown', 'BC', 'CA'),
    (389, 'White Rock', 'BC', 'CA'),
    (390, 'Williams Lake', 'BC', 'CA'),
    (391, 'Windermere', 'BC', 'CA'),
    (392, 'Winfield', 'BC', 'CA'),
    (393, 'Wright', 'BC', 'CA'),
    (394, 'Youbou', 'BC', 'CA'),
    (395, 'Zeballos', 'BC', 'CA'),
    (396, 'Alexander', 'MB', 'CA'),
    (397, 'Altona', 'MB', 'CA'),
    (398, 'Arborg', 'MB', 'CA'),
    (399, 'Ashern', 'MB', 'CA'),
    (400, 'Austin', 'MB', 'CA'),
    (401, 'Beausejour', 'MB', 'CA'),
    (402, 'Brandon', 'MB', 'CA'),
    (403, 'Brochet', 'MB', 'CA'),
    (404, 'Carberry', 'MB', 'CA'),
    (405, 'Carman', 'MB', 'CA'),
    (406, 'Cranberry Portage', 'MB', 'CA'),
    (407, 'Cross Lake', 'MB', 'CA'),
    (408, 'Dauphin', 'MB', 'CA'),
    (409, 'Deer', 'MB', 'CA'),
    (410, 'Dufresne', 'MB', 'CA'),
    (411, 'Eriksdale', 'MB', 'CA'),
    (412, 'Flin Flon', 'MB', 'CA'),
    (413, 'Gillam', 'MB', 'CA'),
    (414, 'Gimli', 'MB', 'CA'),
    (415, 'Gods Lake', 'MB', 'CA'),
    (416, 'Griswold', 'MB', 'CA'),
    (417, 'Gypsumville', 'MB', 'CA'),
    (418, 'Hamiota', 'MB', 'CA'),
    (419, 'Inwood', 'MB', 'CA'),
    (420, 'Kemnay', 'MB', 'CA'),
    (421, 'Killarney', 'MB', 'CA'),
    (422, 'Langruth', 'MB', 'CA'),
    (423, 'Lazare', 'MB', 'CA'),
    (424, 'Macgregor', 'MB', 'CA'),
    (425, 'Malonton', 'MB', 'CA'),
    (426, 'Manitou', 'MB', 'CA'),
    (427, 'McCreary', 'MB', 'CA'),
    (428, 'Melita', 'MB', 'CA'),
    (429, 'Miami', 'MB', 'CA'),
    (430, 'Minitonas', 'MB', 'CA'),
    (431, 'Minnedosa', 'MB', 'CA'),
    (432, 'Morden', 'MB', 'CA'),
    (433, 'Morris', 'MB', 'CA'),
    (434, 'Neepawa', 'MB', 'CA'),
    (435, 'Nelson House', 'MB', 'CA'),
    (436, 'Norway House', 'MB', 'CA'),
    (437, 'Notre-Dame-de-Lourdes', 'MB', 'CA'),
    (438, 'Ochre River', 'MB', 'CA'),
    (439, 'Otterburne', 'MB', 'CA'),
    (440, 'Oxford House', 'MB', 'CA'),
    (441, 'Pilot Mound', 'MB', 'CA'),
    (442, 'Pinawa', 'MB', 'CA'),
    (443, 'Pine Falls', 'MB', 'CA'),
    (444, 'Portage La Prairie', 'MB', 'CA'),
    (445, 'Richer', 'MB', 'CA'),
    (446, 'Rivers', 'MB', 'CA'),
    (447, 'Rosenfeld', 'MB', 'CA'),
    (448, 'Saint Lazare', 'MB', 'CA'),
    (449, 'Sainte-Anne', 'MB', 'CA'),
    (450, 'Selkirk', 'MB', 'CA'),
    (451, 'Shoal Lake', 'MB', 'CA'),
    (452, 'Southport', 'MB', 'CA'),
    (453, 'Steinbach', 'MB', 'CA'),
    (454, 'Stonewall', 'MB', 'CA'),
    (455, 'Stony Mountain', 'MB', 'CA'),
    (456, 'Swan River', 'MB', 'CA'),
    (457, 'Teulon', 'MB', 'CA'),
    (458, 'The Pas', 'MB', 'CA'),
    (459, 'Thicket Portage', 'MB', 'CA'),
    (460, 'Thompson', 'MB', 'CA'),
    (461, 'Treherne', 'MB', 'CA'),
    (462, 'Virden', 'MB', 'CA'),
    (463, 'Wawanesa', 'MB', 'CA'),
    (464, 'Winkler', 'MB', 'CA'),
    (465, 'Winnipeg', 'MB', 'CA'),
    (466, 'Winnipegosis', 'MB', 'CA'),
    (467, 'Aldouane', 'NB', 'CA'),
    (468, 'Bathurst', 'NB', 'CA'),
    (469, 'Blackville', 'NB', 'CA'),
    (470, 'Buctouche', 'NB', 'CA'),
    (471, 'Burnsville', 'NB', 'CA'),
    (472, 'Burnt Church', 'NB', 'CA'),
    (473, 'Campbellton', 'NB', 'CA'),
    (474, 'Canterbury Station', 'NB', 'CA'),
    (475, 'Caraquet', 'NB', 'CA'),
    (476, 'Chatham', 'NB', 'CA'),
    (477, 'Dalhousie', 'NB', 'CA'),
    (478, 'Dieppe', 'NB', 'CA'),
    (479, 'Doaktown', 'NB', 'CA'),
    (480, 'Durham Bridge', 'NB', 'CA'),
    (481, 'Edmundston', 'NB', 'CA'),
    (482, 'Frederickton', 'NB', 'CA'),
    (483, 'Fredericton', 'NB', 'CA'),
    (484, 'Glassville', 'NB', 'CA'),
    (485, 'Grand Bay', 'NB', 'CA'),
    (486, 'Grand Falls', 'NB', 'CA'),
    (487, 'Hampton', 'NB', 'CA'),
    (488, 'Harvey', 'NB', 'CA'),
    (489, 'Hillsborough', 'NB', 'CA'),
    (490, 'Kedgwick', 'NB', 'CA'),
    (491, 'Kent Junction', 'NB', 'CA'),
    (492, 'L''Etete', 'NB', 'CA'),
    (493, 'McAdam', 'NB', 'CA'),
    (494, 'Memramcook', 'NB', 'CA'),
    (495, 'Miramichi', 'NB', 'CA'),
    (496, 'Moncton', 'NB', 'CA'),
    (497, 'Moores Mills', 'NB', 'CA'),
    (498, 'Nauwigewauk', 'NB', 'CA'),
    (499, 'Neguac', 'NB', 'CA'),
    (500, 'Newcastle Bridge', 'NB', 'CA'),
    (501, 'Nigadoo', 'NB', 'CA'),
    (502, 'Notre Dame', 'NB', 'CA'),
    (503, 'Oromocto', 'NB', 'CA'),
    (504, 'Pennfield', 'NB', 'CA'),
    (505, 'Plaster Rock', 'NB', 'CA'),
    (506, 'Pointe-du-Chene', 'NB', 'CA'),
    (507, 'Prince William', 'NB', 'CA'),
    (508, 'Renous', 'NB', 'CA'),
    (509, 'Rexton', 'NB', 'CA'),
    (510, 'Richibucto', 'NB', 'CA'),
    (511, 'River Charlo', 'NB', 'CA'),
    (512, 'Rogersville', 'NB', 'CA'),
    (513, 'Rothesay', 'NB', 'CA'),
    (514, 'Sackville', 'NB', 'CA'),
    (515, 'Saint Andrews', 'NB', 'CA'),
    (516, 'Saint John', 'NB', 'CA'),
    (517, 'Saint Louis De Kent', 'NB', 'CA'),
    (518, 'Saint Margaret Bay', 'NB', 'CA'),
    (519, 'Salisbury', 'NB', 'CA'),
    (520, 'Shediac', 'NB', 'CA'),
    (521, 'South Nelson', 'NB', 'CA'),
    (522, 'Stanley', 'NB', 'CA'),
    (523, 'Sussex', 'NB', 'CA'),
    (524, 'Welsford', 'NB', 'CA'),
    (525, 'Woodstock', 'NB', 'CA'),
    (526, 'Baie Verte', 'NL', 'CA'),
    (527, 'Bauline', 'NL', 'CA'),
    (528, 'Bay Bulls', 'NL', 'CA'),
    (529, 'Bay De Verde', 'NL', 'CA'),
    (530, 'Bay Roberts', 'NL', 'CA'),
    (531, 'Belleoram', 'NL', 'CA'),
    (532, 'Birchy Bay', 'NL', 'CA'),
    (533, 'Bishops Falls', 'NL', 'CA'),
    (534, 'Bonavista', 'NL', 'CA'),
    (535, 'Botwood', 'NL', 'CA'),
    (536, 'Brigus', 'NL', 'CA'),
    (537, 'Burgeo', 'NL', 'CA'),
    (538, 'Burin', 'NL', 'CA'),
    (539, 'Burnside', 'NL', 'CA'),
    (540, 'Cape Ray', 'NL', 'CA'),
    (541, 'Carbonear', 'NL', 'CA'),
    (542, 'Catalina', 'NL', 'CA'),
    (543, 'Change Islands', 'NL', 'CA'),
    (544, 'Channel-Port Aux Basques', 'NL', 'CA'),
    (545, 'Chapel Arm', 'NL', 'CA'),
    (546, 'Clarenville', 'NL', 'CA'),
    (547, 'Colliers', 'NL', 'CA'),
    (548, 'Come By Chance', 'NL', 'CA'),
    (549, 'Corner Brook', 'NL', 'CA'),
    (550, 'Daniels Cove', 'NL', 'CA'),
    (551, 'Dildo', 'NL', 'CA'),
    (552, 'Doyles', 'NL', 'CA'),
    (553, 'Dunville', 'NL', 'CA'),
    (554, 'Fortune', 'NL', 'CA'),
    (555, 'Foxtrap', 'NL', 'CA'),
    (556, 'Francois', 'NL', 'CA'),
    (557, 'Gambo', 'NL', 'CA'),
    (558, 'Gander', 'NL', 'CA'),
    (559, 'Glenburnie', 'NL', 'CA'),
    (560, 'Glovertown', 'NL', 'CA'),
    (561, 'Goose Bay', 'NL', 'CA'),
    (562, 'Grand Bank', 'NL', 'CA'),
    (563, 'Grates Cove', 'NL', 'CA'),
    (564, 'Happy Valley', 'NL', 'CA'),
    (565, 'Harbour Grace', 'NL', 'CA'),
    (566, 'Holyrood', 'NL', 'CA'),
    (567, 'Kelligrews', 'NL', 'CA'),
    (568, 'La Scie', 'NL', 'CA'),
    (569, 'Lamaline', 'NL', 'CA'),
    (570, 'Lawn', 'NL', 'CA'),
    (571, 'Lewisporte', 'NL', 'CA'),
    (572, 'Little Bay Island', 'NL', 'CA'),
    (573, 'Little Harbour', 'NL', 'CA'),
    (574, 'Logy Bay', 'NL', 'CA'),
    (575, 'Loon Bay', 'NL', 'CA'),
    (576, 'Lourdes', 'NL', 'CA'),
    (577, 'Marystown', 'NL', 'CA'),
    (578, 'Middle Arm', 'NL', 'CA'),
    (579, 'Mount Pearl', 'NL', 'CA'),
    (580, 'Mount Pearl Park', 'NL', 'CA'),
    (581, 'Nippers Harbour', 'NL', 'CA'),
    (582, 'Norris Arm', 'NL', 'CA'),
    (583, 'Paradise', 'NL', 'CA'),
    (584, 'Parsons Pond', 'NL', 'CA'),
    (585, 'Petty Harbour', 'NL', 'CA'),
    (586, 'Placentia', 'NL', 'CA'),
    (587, 'Port Albert', 'NL', 'CA'),
    (588, 'Port Blandford', 'NL', 'CA'),
    (589, 'Port Saunders', 'NL', 'CA'),
    (590, 'Portugal Cove', 'NL', 'CA'),
    (591, 'Ramea', 'NL', 'CA'),
    (592, 'Rigolet', 'NL', 'CA'),
    (593, 'Saint Anthony', 'NL', 'CA'),
    (594, 'Saint George', 'NL', 'CA'),
    (595, 'Saint John''s', 'NL', 'CA'),
    (596, 'Saint Jones Within', 'NL', 'CA'),
    (597, 'Silverdale', 'NL', 'CA'),
    (598, 'South Branch', 'NL', 'CA'),
    (599, 'Southern Bay', 'NL', 'CA'),
    (600, 'Spaniards Bay', 'NL', 'CA'),
    (601, 'Springdale', 'NL', 'CA'),
    (602, 'Stephenville', 'NL', 'CA'),
    (603, 'Topsail', 'NL', 'CA'),
    (604, 'Torbay', 'NL', 'CA'),
    (605, 'Twillingate', 'NL', 'CA'),
    (606, 'Wabana', 'NL', 'CA'),
    (607, 'Wabush', 'NL', 'CA'),
    (608, 'Whitbourne', 'NL', 'CA'),
    (609, 'Winterton', 'NL', 'CA'),
    (610, 'Witless Bay', 'NL', 'CA'),
    (611, 'York Harbour', 'NL', 'CA'),
    (612, 'Albany', 'NS', 'CA'),
    (613, 'Amherst', 'NS', 'CA'),
    (614, 'Annapolis', 'NS', 'CA'),
    (615, 'Annapolis Royal', 'NS', 'CA'),
    (616, 'Antigonish', 'NS', 'CA'),
    (617, 'Arichat', 'NS', 'CA'),
    (618, 'Barrington', 'NS', 'CA'),
    (619, 'Bear River', 'NS', 'CA'),
    (620, 'Beaver Harbour', 'NS', 'CA'),
    (621, 'Bedford', 'NS', 'CA'),
    (622, 'Berwick', 'NS', 'CA'),
    (623, 'Bridgetown', 'NS', 'CA'),
    (624, 'Bridgewater', 'NS', 'CA'),
    (625, 'Centreville', 'NS', 'CA'),
    (626, 'Chester', 'NS', 'CA'),
    (627, 'Church Point', 'NS', 'CA'),
    (628, 'Cornwallis', 'NS', 'CA'),
    (629, 'Cow Bay', 'NS', 'CA'),
    (630, 'Dartmouth', 'NS', 'CA'),
    (631, 'Debert', 'NS', 'CA'),
    (632, 'Digby', 'NS', 'CA'),
    (633, 'Dingwall', 'NS', 'CA'),
    (634, 'East Chezzetcook', 'NS', 'CA'),
    (635, 'Eastern Passage', 'NS', 'CA'),
    (636, 'Economy', 'NS', 'CA'),
    (637, 'Elmsdale', 'NS', 'CA'),
    (638, 'Glace Bay', 'NS', 'CA'),
    (639, 'Grand Narrows', 'NS', 'CA'),
    (640, 'Granville Ferry', 'NS', 'CA'),
    (641, 'Greenwood', 'NS', 'CA'),
    (642, 'Halifax', 'NS', 'CA'),
    (643, 'Hantsport', 'NS', 'CA'),
    (644, 'Hopewell', 'NS', 'CA'),
    (645, 'Hubbards', 'NS', 'CA'),
    (646, 'Inverness', 'NS', 'CA'),
    (647, 'Judique', 'NS', 'CA'),
    (648, 'Kennetcook', 'NS', 'CA'),
    (649, 'Kentville', 'NS', 'CA'),
    (650, 'Kingsport', 'NS', 'CA'),
    (651, 'Lawrencetown', 'NS', 'CA'),
    (652, 'Liverpool', 'NS', 'CA'),
    (653, 'Londonderry', 'NS', 'CA'),
    (654, 'Long Point', 'NS', 'CA'),
    (655, 'Lunenburg', 'NS', 'CA'),
    (656, 'Mabou', 'NS', 'CA'),
    (657, 'Mahone Bay', 'NS', 'CA'),
    (658, 'Marshy Hope', 'NS', 'CA'),
    (659, 'McKinnon Harbour', 'NS', 'CA'),
    (660, 'Merigomishe', 'NS', 'CA'),
    (661, 'Middleton', 'NS', 'CA'),
    (662, 'Mosherville', 'NS', 'CA'),
    (663, 'Mount Uniacke', 'NS', 'CA'),
    (664, 'Musquodoboit', 'NS', 'CA'),
    (665, 'Musquodoboit Harbour', 'NS', 'CA'),
    (666, 'New Germany', 'NS', 'CA'),
    (667, 'New Glasgow', 'NS', 'CA'),
    (668, 'New Waterford', 'NS', 'CA'),
    (669, 'Oxford', 'NS', 'CA'),
    (670, 'Oxford Junction', 'NS', 'CA'),
    (671, 'Parrsboro', 'NS', 'CA'),
    (672, 'Petit-Etang', 'NS', 'CA'),
    (673, 'Pictou', 'NS', 'CA'),
    (674, 'Port Hastings', 'NS', 'CA'),
    (675, 'Port Hawkesbury', 'NS', 'CA'),
    (676, 'Port Mouton', 'NS', 'CA'),
    (677, 'Port Williams', 'NS', 'CA'),
    (678, 'Pubnico', 'NS', 'CA'),
    (679, 'Sable River', 'NS', 'CA'),
    (680, 'Saint Peters', 'NS', 'CA'),
    (681, 'Scotsburn', 'NS', 'CA'),
    (682, 'Shubenacadie', 'NS', 'CA'),
    (683, 'Springhill', 'NS', 'CA'),
    (684, 'Stellarton', 'NS', 'CA'),
    (685, 'Sydney', 'NS', 'CA'),
    (686, 'Sydney Mines', 'NS', 'CA'),
    (687, 'Tatamagouche', 'NS', 'CA'),
    (688, 'Tenecape', 'NS', 'CA'),
    (689, 'Tracadie', 'NS', 'CA'),
    (690, 'Truro', 'NS', 'CA'),
    (691, 'Upper Musquodoboit', 'NS', 'CA'),
    (692, 'Wedgeport', 'NS', 'CA'),
    (693, 'Westville', 'NS', 'CA'),
    (694, 'Weymouth', 'NS', 'CA'),
    (695, 'Wolfville', 'NS', 'CA'),
    (696, 'Woods Harbour', 'NS', 'CA'),
    (697, 'Yarmouth', 'NS', 'CA'),
    (698, 'Aklavik', 'NT', 'CA'),
    (699, 'Apex', 'NT', 'CA'),
    (700, 'Arctic Bay', 'NT', 'CA'),
    (701, 'Arviat', 'NT', 'CA'),
    (702, 'Baker Lake', 'NT', 'CA'),
    (703, 'Broughton Island', 'NT', 'CA'),
    (704, 'Cambridge Bay', 'NT', 'CA'),
    (705, 'Cape Dorset', 'NT', 'CA'),
    (706, 'Chesterfield Inlet', 'NT', 'CA'),
    (707, 'Clyde River', 'NT', 'CA'),
    (708, 'Colville Lake', 'NT', 'CA'),
    (709, 'Coral Harbour', 'NT', 'CA'),
    (710, 'Deline', 'NT', 'CA'),
    (711, 'Fort Good Hope', 'NT', 'CA'),
    (712, 'Fort Liard', 'NT', 'CA'),
    (713, 'Fort McPherson', 'NT', 'CA'),
    (714, 'Fort Providence', 'NT', 'CA'),
    (715, 'Fort Resolution', 'NT', 'CA'),
    (716, 'Fort Simpson', 'NT', 'CA'),
    (717, 'Fort Smith', 'NT', 'CA'),
    (718, 'Gjoa Haven', 'NT', 'CA'),
    (719, 'Hall Beach', 'NT', 'CA'),
    (720, 'Hay River', 'NT', 'CA'),
    (721, 'Holman Island', 'NT', 'CA'),
    (722, 'Igloolik', 'NT', 'CA'),
    (723, 'Inuvik', 'NT', 'CA'),
    (724, 'Iqaluit', 'NT', 'CA'),
    (725, 'Jean Marie River', 'NT', 'CA'),
    (726, 'Kimmirut', 'NT', 'CA'),
    (727, 'Kugluktuk', 'NT', 'CA'),
    (728, 'Nahanni Butte', 'NT', 'CA'),
    (729, 'Norman Wells', 'NT', 'CA'),
    (730, 'Pangnirtung', 'NT', 'CA'),
    (731, 'Paulatuk', 'NT', 'CA'),
    (732, 'Pelly Bay', 'NT', 'CA'),
    (733, 'Pond Inlet', 'NT', 'CA'),
    (734, 'Providence', 'NT', 'CA'),
    (735, 'Rankin Inlet', 'NT', 'CA'),
    (736, 'Repulse Bay', 'NT', 'CA'),
    (737, 'Resolution', 'NT', 'CA'),
    (738, 'Sachs Harbour', 'NT', 'CA'),
    (739, 'Sanikiluaq', 'NT', 'CA'),
    (740, 'Snare Lake', 'NT', 'CA'),
    (741, 'Taloyoak', 'NT', 'CA'),
    (742, 'Trout Lake', 'NT', 'CA'),
    (743, 'Tsiigehtchic', 'NT', 'CA'),
    (744, 'Tuktoyaktuk', 'NT', 'CA'),
    (745, 'Tulita', 'NT', 'CA'),
    (746, 'Wha Ti', 'NT', 'CA'),
    (747, 'Wrigley', 'NT', 'CA'),
    (748, 'Yellowknife', 'NT', 'CA'),
    (749, 'Kugaaruk', 'NU', 'CA'),
    (750, 'Resolute', 'NU', 'CA'),
    (751, 'Acton', 'ON', 'CA'),
    (752, 'Agincourt', 'ON', 'CA'),
    (753, 'Ajax', 'ON', 'CA'),
    (754, 'Alexandria', 'ON', 'CA'),
    (755, 'Alfred', 'ON', 'CA'),
    (756, 'Algonquin Park', 'ON', 'CA'),
    (757, 'Allenford', 'ON', 'CA'),
    (758, 'Alliston', 'ON', 'CA'),
    (759, 'Almonte', 'ON', 'CA'),
    (760, 'Amherstburg', 'ON', 'CA'),
    (761, 'Angus', 'ON', 'CA'),
    (762, 'Apsley', 'ON', 'CA'),
    (763, 'Arnprior', 'ON', 'CA'),
    (764, 'Arthur', 'ON', 'CA'),
    (765, 'Atikokan', 'ON', 'CA'),
    (766, 'Attawapiskat', 'ON', 'CA'),
    (767, 'Aurora', 'ON', 'CA'),
    (768, 'Ayr', 'ON', 'CA'),
    (769, 'Ayton', 'ON', 'CA'),
    (770, 'Baden', 'ON', 'CA'),
    (771, 'Bala', 'ON', 'CA'),
    (772, 'Bancroft', 'ON', 'CA'),
    (773, 'Barrie', 'ON', 'CA'),
    (774, 'Bath', 'ON', 'CA'),
    (775, 'Bayfield', 'ON', 'CA'),
    (776, 'Beachburg', 'ON', 'CA'),
    (777, 'Beamsville', 'ON', 'CA'),
    (778, 'Bearskin Lake', 'ON', 'CA'),
    (779, 'Beaverton', 'ON', 'CA'),
    (780, 'Belle River', 'ON', 'CA'),
    (781, 'Belleville', 'ON', 'CA'),
    (782, 'Belmont', 'ON', 'CA'),
    (783, 'Bethany', 'ON', 'CA'),
    (784, 'Blenheim', 'ON', 'CA'),
    (785, 'Blind River', 'ON', 'CA'),
    (786, 'Bobcaygeon', 'ON', 'CA'),
    (787, 'Bolton', 'ON', 'CA'),
    (788, 'Borden', 'ON', 'CA'),
    (789, 'Bowmanville', 'ON', 'CA'),
    (790, 'Bracebridge', 'ON', 'CA'),
    (791, 'Bradford', 'ON', 'CA'),
    (792, 'Brampton', 'ON', 'CA'),
    (793, 'Brantford', 'ON', 'CA'),
    (794, 'Brechin', 'ON', 'CA'),
    (795, 'Brighton', 'ON', 'CA'),
    (796, 'Brimley', 'ON', 'CA'),
    (797, 'Britt', 'ON', 'CA'),
    (798, 'Brockville', 'ON', 'CA'),
    (799, 'Bruce Mines', 'ON', 'CA'),
    (800, 'Buckhorn', 'ON', 'CA'),
    (801, 'Burks Falls', 'ON', 'CA'),
    (802, 'Burlington', 'ON', 'CA'),
    (803, 'Calabogie', 'ON', 'CA'),
    (804, 'Caledon', 'ON', 'CA'),
    (805, 'Caledon East', 'ON', 'CA'),
    (806, 'Caledonia', 'ON', 'CA'),
    (807, 'Callander', 'ON', 'CA'),
    (808, 'Cambridge', 'ON', 'CA'),
    (809, 'Campbellford', 'ON', 'CA'),
    (810, 'Canfield', 'ON', 'CA'),
    (811, 'Cannington', 'ON', 'CA'),
    (812, 'Capreol', 'ON', 'CA'),
    (813, 'Cardinal', 'ON', 'CA'),
    (814, 'Carleton Place', 'ON', 'CA'),
    (815, 'Carp', 'ON', 'CA'),
    (816, 'Casselman', 'ON', 'CA'),
    (817, 'Chalk River', 'ON', 'CA'),
    (818, 'Chapleau', 'ON', 'CA'),
    (819, 'Chatsworth', 'ON', 'CA'),
    (820, 'Chelmsford', 'ON', 'CA'),
    (821, 'Cheminis', 'ON', 'CA'),
    (822, 'Chesley', 'ON', 'CA'),
    (823, 'Chesterville', 'ON', 'CA'),
    (824, 'Churchill', 'ON', 'CA'),
    (825, 'Clarkson', 'ON', 'CA'),
    (826, 'Clinton', 'ON', 'CA'),
    (827, 'Cobalt', 'ON', 'CA'),
    (828, 'Cobden', 'ON', 'CA'),
    (829, 'Cobourg', 'ON', 'CA'),
    (830, 'Cochrane', 'ON', 'CA'),
    (831, 'Colborne', 'ON', 'CA'),
    (832, 'Coldwater', 'ON', 'CA'),
    (833, 'Collingwood', 'ON', 'CA'),
    (834, 'Comber', 'ON', 'CA'),
    (835, 'Cornwall', 'ON', 'CA'),
    (836, 'Cottam', 'ON', 'CA'),
    (837, 'Courtright', 'ON', 'CA'),
    (838, 'Creemore', 'ON', 'CA'),
    (839, 'Creighton', 'ON', 'CA'),
    (840, 'Crystal Beach', 'ON', 'CA'),
    (841, 'Cumberland', 'ON', 'CA'),
    (842, 'Deep River', 'ON', 'CA'),
    (843, 'Deer Lake', 'ON', 'CA'),
    (844, 'Delhi', 'ON', 'CA'),
    (845, 'Denbigh', 'ON', 'CA'),
    (846, 'Denfield', 'ON', 'CA'),
    (847, 'Desbarats', 'ON', 'CA'),
    (848, 'Deseronto', 'ON', 'CA'),
    (849, 'Don Mills', 'ON', 'CA'),
    (850, 'Dorset', 'ON', 'CA'),
    (851, 'Downsview', 'ON', 'CA'),
    (852, 'Dresden', 'ON', 'CA'),
    (853, 'Drinkwater', 'ON', 'CA'),
    (854, 'Drumbo', 'ON', 'CA'),
    (855, 'Dryden', 'ON', 'CA'),
    (856, 'Dundalk', 'ON', 'CA'),
    (857, 'Dundas', 'ON', 'CA'),
    (858, 'Dunnville', 'ON', 'CA'),
    (859, 'Dunsford', 'ON', 'CA'),
    (860, 'Durham', 'ON', 'CA'),
    (861, 'Earlton', 'ON', 'CA'),
    (862, 'Echo Bay', 'ON', 'CA'),
    (863, 'Eganville', 'ON', 'CA'),
    (864, 'Elgin', 'ON', 'CA'),
    (865, 'Elk Lake', 'ON', 'CA'),
    (866, 'Elliot Lake', 'ON', 'CA'),
    (867, 'Elmira', 'ON', 'CA'),
    (868, 'Elmvale', 'ON', 'CA'),
    (869, 'Elora', 'ON', 'CA'),
    (870, 'Emerald', 'ON', 'CA'),
    (871, 'Emo', 'ON', 'CA'),
    (872, 'Englehart', 'ON', 'CA'),
    (873, 'Enterprise', 'ON', 'CA'),
    (874, 'Erin', 'ON', 'CA'),
    (875, 'Erindale', 'ON', 'CA'),
    (876, 'Espanola', 'ON', 'CA'),
    (877, 'Essex', 'ON', 'CA'),
    (878, 'Etobicoke', 'ON', 'CA'),
    (879, 'Exeter', 'ON', 'CA'),
    (880, 'Falconbridge', 'ON', 'CA'),
    (881, 'Fenelon Falls', 'ON', 'CA'),
    (882, 'Fergus', 'ON', 'CA'),
    (883, 'Flesherton', 'ON', 'CA'),
    (884, 'Fonthill', 'ON', 'CA'),
    (885, 'Forest', 'ON', 'CA'),
    (886, 'Fort Albany', 'ON', 'CA'),
    (887, 'Fort Erie', 'ON', 'CA'),
    (888, 'Fort Frances', 'ON', 'CA'),
    (889, 'Fort Hope', 'ON', 'CA'),
    (890, 'Fort Severn', 'ON', 'CA'),
    (891, 'Foymount', 'ON', 'CA'),
    (892, 'Frankford', 'ON', 'CA'),
    (893, 'Galt', 'ON', 'CA'),
    (894, 'Gananoque', 'ON', 'CA'),
    (895, 'Garden River', 'ON', 'CA'),
    (896, 'Garson', 'ON', 'CA'),
    (897, 'Georgetown', 'ON', 'CA'),
    (898, 'Geraldton', 'ON', 'CA'),
    (899, 'Glencoe', 'ON', 'CA'),
    (900, 'Gloucester', 'ON', 'CA'),
    (901, 'Goderich', 'ON', 'CA'),
    (902, 'Gogama', 'ON', 'CA'),
    (903, 'Golden Lake', 'ON', 'CA'),
    (904, 'Goldpines', 'ON', 'CA'),
    (905, 'Gooderham', 'ON', 'CA'),
    (906, 'Gore Bay', 'ON', 'CA'),
    (907, 'Grand Bend', 'ON', 'CA'),
    (908, 'Grand Valley', 'ON', 'CA'),
    (909, 'Gravenhurst', 'ON', 'CA'),
    (910, 'Grimsby', 'ON', 'CA'),
    (911, 'Guelph', 'ON', 'CA'),
    (912, 'Hagersville', 'ON', 'CA'),
    (913, 'Haileybury', 'ON', 'CA'),
    (914, 'Haley Station', 'ON', 'CA'),
    (915, 'Haliburton', 'ON', 'CA'),
    (916, 'Hamilton', 'ON', 'CA'),
    (917, 'Hanover', 'ON', 'CA'),
    (918, 'Harriston', 'ON', 'CA'),
    (919, 'Harrow', 'ON', 'CA'),
    (920, 'Hastings', 'ON', 'CA'),
    (921, 'Havelock', 'ON', 'CA'),
    (922, 'Hawk Junction', 'ON', 'CA'),
    (923, 'Hawkesbury', 'ON', 'CA'),
    (924, 'Hearst', 'ON', 'CA'),
    (925, 'Hensall', 'ON', 'CA'),
    (926, 'Heron Bay', 'ON', 'CA'),
    (927, 'Hespeler', 'ON', 'CA'),
    (928, 'Hindon', 'ON', 'CA'),
    (929, 'Holland', 'ON', 'CA'),
    (930, 'Hope', 'ON', 'CA'),
    (931, 'Hornepayne', 'ON', 'CA'),
    (932, 'Huntsville', 'ON', 'CA'),
    (933, 'Ignace', 'ON', 'CA'),
    (934, 'Ingersoll', 'ON', 'CA'),
    (935, 'Ingleside', 'ON', 'CA'),
    (936, 'Inglewood', 'ON', 'CA'),
    (937, 'Iroquois', 'ON', 'CA'),
    (938, 'Iroquois Falls', 'ON', 'CA'),
    (939, 'Kagawong', 'ON', 'CA'),
    (940, 'Kakabeka Falls', 'ON', 'CA'),
    (941, 'Kanata', 'ON', 'CA'),
    (942, 'Kapuskasing', 'ON', 'CA'),
    (943, 'Keene', 'ON', 'CA'),
    (944, 'Keewatin', 'ON', 'CA'),
    (945, 'Kemptville', 'ON', 'CA'),
    (946, 'Kenora', 'ON', 'CA'),
    (947, 'Keswick', 'ON', 'CA'),
    (948, 'Kincardine', 'ON', 'CA'),
    (949, 'King City', 'ON', 'CA'),
    (950, 'Kingston', 'ON', 'CA'),
    (951, 'Kingsville', 'ON', 'CA'),
    (952, 'Kirkland Lake', 'ON', 'CA'),
    (953, 'Kitchener', 'ON', 'CA'),
    (954, 'L''Original', 'ON', 'CA'),
    (955, 'Lakefield', 'ON', 'CA'),
    (956, 'Lancaster', 'ON', 'CA'),
    (957, 'Lansdowne', 'ON', 'CA'),
    (958, 'Leamington', 'ON', 'CA'),
    (959, 'Leaside', 'ON', 'CA'),
    (960, 'Lefroy', 'ON', 'CA'),
    (961, 'Levack', 'ON', 'CA'),
    (962, 'Lindsay', 'ON', 'CA'),
    (963, 'Listowel', 'ON', 'CA'),
    (964, 'Little Current', 'ON', 'CA'),
    (965, 'Lively', 'ON', 'CA'),
    (966, 'London', 'ON', 'CA'),
    (967, 'Long Sault', 'ON', 'CA'),
    (968, 'Longlac', 'ON', 'CA'),
    (969, 'Lucan', 'ON', 'CA'),
    (970, 'Lucknow', 'ON', 'CA'),
    (971, 'Macdiarmid', 'ON', 'CA'),
    (972, 'Madoc', 'ON', 'CA'),
    (973, 'Magnetawan', 'ON', 'CA'),
    (974, 'Maitland', 'ON', 'CA'),
    (975, 'Malton', 'ON', 'CA'),
    (976, 'Manitouwadge', 'ON', 'CA'),
    (977, 'Manitowaning', 'ON', 'CA'),
    (978, 'Maple', 'ON', 'CA'),
    (979, 'Marathon', 'ON', 'CA'),
    (980, 'Markdale', 'ON', 'CA'),
    (981, 'Markham', 'ON', 'CA'),
    (982, 'Markstay', 'ON', 'CA'),
    (983, 'Marmora', 'ON', 'CA'),
    (984, 'Massey', 'ON', 'CA'),
    (985, 'Mattawa', 'ON', 'CA'),
    (986, 'Maxville', 'ON', 'CA'),
    (987, 'Maynooth', 'ON', 'CA'),
    (988, 'McGregor', 'ON', 'CA'),
    (989, 'Meaford', 'ON', 'CA'),
    (990, 'Merrickville', 'ON', 'CA'),
    (991, 'Michipicoten', 'ON', 'CA'),
    (992, 'Midhurst', 'ON', 'CA'),
    (993, 'Midland', 'ON', 'CA'),
    (994, 'Mildmay', 'ON', 'CA'),
    (995, 'Millbrook', 'ON', 'CA'),
    (996, 'Millhaven', 'ON', 'CA'),
    (997, 'Milton', 'ON', 'CA'),
    (998, 'Milverton', 'ON', 'CA'),
    (999, 'Mimico', 'ON', 'CA'),
    (1000, 'Minden', 'ON', 'CA'),
    (1001, 'Mississauga', 'ON', 'CA'),
    (1002, 'Mitchell', 'ON', 'CA'),
    (1003, 'Moose Factory', 'ON', 'CA'),
    (1004, 'Morrisburg', 'ON', 'CA'),
    (1005, 'Mount Forest', 'ON', 'CA'),
    (1006, 'Mountain Grove', 'ON', 'CA'),
    (1007, 'Nakina', 'ON', 'CA'),
    (1008, 'Napanee', 'ON', 'CA'),
    (1009, 'Nepean', 'ON', 'CA'),
    (1010, 'New Hamburg', 'ON', 'CA'),
    (1011, 'New Liskeard', 'ON', 'CA'),
    (1012, 'Newburgh', 'ON', 'CA'),
    (1013, 'Newcastle', 'ON', 'CA'),
    (1014, 'Newmarket', 'ON', 'CA'),
    (1015, 'Niagara', 'ON', 'CA'),
    (1016, 'Niagara Falls', 'ON', 'CA'),
    (1017, 'Niagara-on-the-Lake', 'ON', 'CA'),
    (1018, 'Nipigon', 'ON', 'CA'),
    (1019, 'Noelville', 'ON', 'CA'),
    (1020, 'North Bay', 'ON', 'CA'),
    (1021, 'Norwich', 'ON', 'CA'),
    (1022, 'Norwood', 'ON', 'CA'),
    (1023, 'Oakville', 'ON', 'CA'),
    (1024, 'Omemee', 'ON', 'CA'),
    (1025, 'Orangeville', 'ON', 'CA'),
    (1026, 'Orillia', 'ON', 'CA'),
    (1027, 'Orleans', 'ON', 'CA'),
    (1028, 'Oshawa', 'ON', 'CA'),
    (1029, 'Ottawa', 'ON', 'CA'),
    (1030, 'Otter Lake', 'ON', 'CA'),
    (1031, 'Owen Sound', 'ON', 'CA'),
    (1032, 'Paisley', 'ON', 'CA'),
    (1033, 'Pakenham', 'ON', 'CA'),
    (1034, 'Palmerston', 'ON', 'CA'),
    (1035, 'Paris', 'ON', 'CA'),
    (1036, 'Park Head', 'ON', 'CA'),
    (1037, 'Parkhill', 'ON', 'CA'),
    (1038, 'Parry Sound', 'ON', 'CA'),
    (1039, 'Pass Lake', 'ON', 'CA'),
    (1040, 'Pefferlaw', 'ON', 'CA'),
    (1041, 'Pembroke', 'ON', 'CA'),
    (1042, 'Penetanguishene', 'ON', 'CA'),
    (1043, 'Perth', 'ON', 'CA'),
    (1044, 'Petawawa', 'ON', 'CA'),
    (1045, 'Peterborough', 'ON', 'CA'),
    (1046, 'Petrolia', 'ON', 'CA'),
    (1047, 'Pickering', 'ON', 'CA'),
    (1048, 'Picton', 'ON', 'CA'),
    (1049, 'Pikangikum', 'ON', 'CA'),
    (1050, 'Pinewood', 'ON', 'CA'),
    (1051, 'Point Edward', 'ON', 'CA'),
    (1052, 'Pontypool', 'ON', 'CA'),
    (1053, 'Poplar Hill', 'ON', 'CA'),
    (1054, 'Port Colborne', 'ON', 'CA'),
    (1055, 'Port Credit', 'ON', 'CA'),
    (1056, 'Port Dover', 'ON', 'CA'),
    (1057, 'Port Elgin', 'ON', 'CA'),
    (1058, 'Port Hope', 'ON', 'CA'),
    (1059, 'Port McNicoll', 'ON', 'CA'),
    (1060, 'Port Perry', 'ON', 'CA'),
    (1061, 'Port Stanley', 'ON', 'CA'),
    (1062, 'Port Weller', 'ON', 'CA'),
    (1063, 'Powassan', 'ON', 'CA'),
    (1064, 'Prescott', 'ON', 'CA'),
    (1065, 'Preston', 'ON', 'CA'),
    (1066, 'Queenston', 'ON', 'CA'),
    (1067, 'Red Lake', 'ON', 'CA'),
    (1068, 'Red Rock', 'ON', 'CA'),
    (1069, 'Redwater', 'ON', 'CA'),
    (1070, 'Renfrew', 'ON', 'CA'),
    (1071, 'Rexdale', 'ON', 'CA'),
    (1072, 'Richan', 'ON', 'CA'),
    (1073, 'Richmond Hill', 'ON', 'CA'),
    (1074, 'Ridgetown', 'ON', 'CA'),
    (1075, 'River Valley', 'ON', 'CA'),
    (1076, 'Roblin', 'ON', 'CA'),
    (1077, 'Rockland', 'ON', 'CA'),
    (1078, 'Rodney', 'ON', 'CA'),
    (1079, 'Rossport', 'ON', 'CA'),
    (1080, 'Russell', 'ON', 'CA'),
    (1081, 'Saint Catharines', 'ON', 'CA'),
    (1082, 'Saint Catherines', 'ON', 'CA'),
    (1083, 'Saint Clair Beach', 'ON', 'CA'),
    (1084, 'Saint Marys', 'ON', 'CA'),
    (1085, 'Saint Thomas', 'ON', 'CA'),
    (1086, 'Saint-Charles', 'ON', 'CA'),
    (1087, 'Sarnia', 'ON', 'CA'),
    (1088, 'Sault Sainte Marie', 'ON', 'CA'),
    (1089, 'Scarborough', 'ON', 'CA'),
    (1090, 'Scarborough Junction', 'ON', 'CA'),
    (1091, 'Scarborough Township', 'ON', 'CA'),
    (1092, 'Schreiber', 'ON', 'CA'),
    (1093, 'Schumacher', 'ON', 'CA'),
    (1094, 'Scotia', 'ON', 'CA'),
    (1095, 'Seaforth', 'ON', 'CA'),
    (1096, 'Severn Bridge', 'ON', 'CA'),
    (1097, 'Sharbot Lake', 'ON', 'CA'),
    (1098, 'Shelburne', 'ON', 'CA'),
    (1099, 'Simcoe', 'ON', 'CA'),
    (1100, 'Sioux Lookout', 'ON', 'CA'),
    (1101, 'Smiths Falls', 'ON', 'CA'),
    (1102, 'Smithville', 'ON', 'CA'),
    (1103, 'Sombra', 'ON', 'CA'),
    (1104, 'South Porcupine', 'ON', 'CA'),
    (1105, 'South River', 'ON', 'CA'),
    (1106, 'Southampton', 'ON', 'CA'),
    (1107, 'Spanish', 'ON', 'CA'),
    (1108, 'Sparta', 'ON', 'CA'),
    (1109, 'Stamford', 'ON', 'CA'),
    (1110, 'Stayner', 'ON', 'CA'),
    (1111, 'Stirling', 'ON', 'CA'),
    (1112, 'Stittsville', 'ON', 'CA'),
    (1113, 'Stoney Creek', 'ON', 'CA'),
    (1114, 'Stouffville', 'ON', 'CA'),
    (1115, 'Stratford', 'ON', 'CA'),
    (1116, 'Strathroy', 'ON', 'CA'),
    (1117, 'Streetsville', 'ON', 'CA'),
    (1118, 'Sturgeon Falls', 'ON', 'CA'),
    (1119, 'Sudbury', 'ON', 'CA'),
    (1120, 'Sundridge', 'ON', 'CA'),
    (1121, 'Swansea', 'ON', 'CA'),
    (1122, 'Sydenham', 'ON', 'CA'),
    (1123, 'Tavistock', 'ON', 'CA'),
    (1124, 'Tecumseh', 'ON', 'CA'),
    (1125, 'Teeswater', 'ON', 'CA'),
    (1126, 'Terrace Bay', 'ON', 'CA'),
    (1127, 'Thedford', 'ON', 'CA'),
    (1128, 'Thessalon', 'ON', 'CA'),
    (1129, 'Thornbury', 'ON', 'CA'),
    (1130, 'Thornhill', 'ON', 'CA'),
    (1131, 'Thorold', 'ON', 'CA'),
    (1132, 'Thunder Bay', 'ON', 'CA'),
    (1133, 'Tichborne', 'ON', 'CA'),
    (1134, 'Tilbury', 'ON', 'CA'),
    (1135, 'Tillsonburg', 'ON', 'CA'),
    (1136, 'Tilsonburg', 'ON', 'CA'),
    (1137, 'Timmins', 'ON', 'CA'),
    (1138, 'Tiverton', 'ON', 'CA'),
    (1139, 'Tobermory', 'ON', 'CA'),
    (1140, 'Toronto', 'ON', 'CA'),
    (1141, 'Tottenham', 'ON', 'CA'),
    (1142, 'Trenton', 'ON', 'CA'),
    (1143, 'Tudhope', 'ON', 'CA'),
    (1144, 'Tweed', 'ON', 'CA'),
    (1145, 'Tyrone', 'ON', 'CA'),
    (1146, 'Uxbridge', 'ON', 'CA'),
    (1147, 'Vankleek Hill', 'ON', 'CA'),
    (1148, 'Verner', 'ON', 'CA'),
    (1149, 'Verona', 'ON', 'CA'),
    (1150, 'Victoria Harbour', 'ON', 'CA'),
    (1151, 'Walkerton', 'ON', 'CA'),
    (1152, 'Wallace', 'ON', 'CA'),
    (1153, 'Wallaceburg', 'ON', 'CA'),
    (1154, 'Warren', 'ON', 'CA'),
    (1155, 'Waterdown', 'ON', 'CA'),
    (1156, 'Waterford', 'ON', 'CA'),
    (1157, 'Waterloo', 'ON', 'CA'),
    (1158, 'Watford', 'ON', 'CA'),
    (1159, 'Wawa', 'ON', 'CA'),
    (1160, 'Webbwood', 'ON', 'CA'),
    (1161, 'Welland', 'ON', 'CA'),
    (1162, 'Wellington', 'ON', 'CA'),
    (1163, 'West Lorne', 'ON', 'CA'),
    (1164, 'Weston', 'ON', 'CA'),
    (1165, 'Westport', 'ON', 'CA'),
    (1166, 'Wheatley', 'ON', 'CA'),
    (1167, 'Whitby', 'ON', 'CA'),
    (1168, 'Whitchurch-Stouffville', 'ON', 'CA'),
    (1169, 'White River', 'ON', 'CA'),
    (1170, 'Whitefish', 'ON', 'CA'),
    (1171, 'Whitney', 'ON', 'CA'),
    (1172, 'Wiarton', 'ON', 'CA'),
    (1173, 'Wilberforce', 'ON', 'CA'),
    (1174, 'Willowdale', 'ON', 'CA'),
    (1175, 'Winchester', 'ON', 'CA'),
    (1176, 'Windsor', 'ON', 'CA'),
    (1177, 'Wingham', 'ON', 'CA'),
    (1178, 'Woodbridge', 'ON', 'CA'),
    (1179, 'Wyebridge', 'ON', 'CA'),
    (1180, 'Wyoming', 'ON', 'CA'),
    (1181, 'Yarker', 'ON', 'CA'),
    (1182, 'York', 'ON', 'CA'),
    (1183, 'Borden-Carleton', 'PE', 'CA'),
    (1184, 'Carleton', 'PE', 'CA'),
    (1185, 'Charlottetown', 'PE', 'CA'),
    (1186, 'Hunter River', 'PE', 'CA'),
    (1187, 'Kensington', 'PE', 'CA'),
    (1188, 'Montague', 'PE', 'CA'),
    (1189, 'Portage', 'PE', 'CA'),
    (1190, 'Rustico', 'PE', 'CA'),
    (1191, 'Souris', 'PE', 'CA'),
    (1192, 'Summerside', 'PE', 'CA'),
    (1193, 'Vernon River', 'PE', 'CA'),
    (1194, 'Abord a Plouffe', 'QC', 'CA'),
    (1195, 'Acton Vale', 'QC', 'CA'),
    (1196, 'Albanel', 'QC', 'CA'),
    (1197, 'Alma', 'QC', 'CA'),
    (1198, 'Amos', 'QC', 'CA'),
    (1199, 'Amqui', 'QC', 'CA'),
    (1200, 'Ancienne Lorette', 'QC', 'CA'),
    (1201, 'Anjou', 'QC', 'CA'),
    (1202, 'Anteuil', 'QC', 'CA'),
    (1203, 'Arthabaska', 'QC', 'CA'),
    (1204, 'Arvida', 'QC', 'CA'),
    (1205, 'Asbestos', 'QC', 'CA'),
    (1206, 'Aston Junction', 'QC', 'CA'),
    (1207, 'Auteuil', 'QC', 'CA'),
    (1208, 'Authier', 'QC', 'CA'),
    (1209, 'Ayers Cliff', 'QC', 'CA'),
    (1210, 'Aylmer', 'QC', 'CA'),
    (1211, 'Bagotville', 'QC', 'CA'),
    (1212, 'Baie-Comeau', 'QC', 'CA'),
    (1213, 'Baie-d''Urfe', 'QC', 'CA'),
    (1214, 'Baie-Saint-Paul', 'QC', 'CA'),
    (1215, 'Barachois', 'QC', 'CA'),
    (1216, 'Barraute', 'QC', 'CA'),
    (1217, 'Batiscan', 'QC', 'CA'),
    (1218, 'Beaconsfield', 'QC', 'CA'),
    (1219, 'Beauceville', 'QC', 'CA'),
    (1220, 'Beauharnois', 'QC', 'CA'),
    (1221, 'Beauport', 'QC', 'CA'),
    (1222, 'Beaupre', 'QC', 'CA'),
    (1223, 'Beaver', 'QC', 'CA'),
    (1224, 'Becancour', 'QC', 'CA'),
    (1225, 'Beebe', 'QC', 'CA'),
    (1226, 'Beloeil', 'QC', 'CA'),
    (1227, 'Berthier', 'QC', 'CA'),
    (1228, 'Berthier-sur-Mer', 'QC', 'CA'),
    (1229, 'Berthierville', 'QC', 'CA'),
    (1230, 'Betsiamites', 'QC', 'CA'),
    (1231, 'Bois-des-Filion', 'QC', 'CA'),
    (1232, 'Boischatel', 'QC', 'CA'),
    (1233, 'Bonaventure', 'QC', 'CA'),
    (1234, 'Bromptonville', 'QC', 'CA'),
    (1235, 'Brossard', 'QC', 'CA'),
    (1236, 'Bryson', 'QC', 'CA'),
    (1237, 'Buckingham', 'QC', 'CA'),
    (1238, 'Cabano', 'QC', 'CA'),
    (1239, 'Cacouna', 'QC', 'CA'),
    (1240, 'Candiac', 'QC', 'CA'),
    (1241, 'Cap-aux-Meules', 'QC', 'CA'),
    (1242, 'Cap-Chat', 'QC', 'CA'),
    (1243, 'Cap-de-la-Madeleine', 'QC', 'CA'),
    (1244, 'Cap-Rouge', 'QC', 'CA'),
    (1245, 'Caughnawaga', 'QC', 'CA'),
    (1246, 'Chambly', 'QC', 'CA'),
    (1247, 'Chambord', 'QC', 'CA'),
    (1248, 'Champlain', 'QC', 'CA'),
    (1249, 'Chandler', 'QC', 'CA'),
    (1250, 'Chapais', 'QC', 'CA'),
    (1251, 'Charlemagne', 'QC', 'CA'),
    (1252, 'Charlesbourg', 'QC', 'CA'),
    (1253, 'Charny', 'QC', 'CA'),
    (1254, 'Chateauguay', 'QC', 'CA'),
    (1255, 'Chibougamau', 'QC', 'CA'),
    (1256, 'Chicoutimi', 'QC', 'CA'),
    (1257, 'Chicoutimi-Nord', 'QC', 'CA'),
    (1258, 'Chomedey', 'QC', 'CA'),
    (1259, 'Chute-aux-Outardes', 'QC', 'CA'),
    (1260, 'Chute-Shipshaw', 'QC', 'CA'),
    (1261, 'Clericy', 'QC', 'CA'),
    (1262, 'Clermont', 'QC', 'CA'),
    (1263, 'Coaticook', 'QC', 'CA'),
    (1264, 'Contrecoeur', 'QC', 'CA'),
    (1265, 'Cookshire', 'QC', 'CA'),
    (1266, 'Cote-Saint-Luc', 'QC', 'CA'),
    (1267, 'Cowansville', 'QC', 'CA'),
    (1268, 'Crabtree', 'QC', 'CA'),
    (1269, 'Danville', 'QC', 'CA'),
    (1270, 'De Lery', 'QC', 'CA'),
    (1271, 'Delson', 'QC', 'CA'),
    (1272, 'Derval', 'QC', 'CA'),
    (1273, 'Desbiens', 'QC', 'CA'),
    (1274, 'Deschambault', 'QC', 'CA'),
    (1275, 'Deux-Montagnes', 'QC', 'CA'),
    (1276, 'Disraeli', 'QC', 'CA'),
    (1277, 'Dolbeau', 'QC', 'CA'),
    (1278, 'Dollard-des-Ormeaux', 'QC', 'CA'),
    (1279, 'Donnacona', 'QC', 'CA'),
    (1280, 'Dorion', 'QC', 'CA'),
    (1281, 'Dorion-Vaudreuil', 'QC', 'CA'),
    (1282, 'Dorval', 'QC', 'CA'),
    (1283, 'Drummondville', 'QC', 'CA'),
    (1284, 'Dubuisson', 'QC', 'CA'),
    (1285, 'Duvernay', 'QC', 'CA'),
    (1286, 'East Angus', 'QC', 'CA'),
    (1287, 'Eastmain', 'QC', 'CA'),
    (1288, 'Eastman', 'QC', 'CA'),
    (1289, 'Etang-du-Nord', 'QC', 'CA'),
    (1290, 'Fabreville', 'QC', 'CA'),
    (1291, 'Farnham', 'QC', 'CA'),
    (1292, 'Ferme-Neuve', 'QC', 'CA'),
    (1293, 'Forestville', 'QC', 'CA'),
    (1294, 'Gaspe', 'QC', 'CA'),
    (1295, 'Gatineau', 'QC', 'CA'),
    (1296, 'Godbout', 'QC', 'CA'),
    (1297, 'Granby', 'QC', 'CA'),
    (1298, 'Grande-Riviere', 'QC', 'CA'),
    (1299, 'Greenfield Park', 'QC', 'CA'),
    (1300, 'Grosses-Roches', 'QC', 'CA'),
    (1301, 'Guigues', 'QC', 'CA'),
    (1302, 'Hebertville', 'QC', 'CA'),
    (1303, 'Hemmingford', 'QC', 'CA'),
    (1304, 'Hudson', 'QC', 'CA'),
    (1305, 'Hudson Heights', 'QC', 'CA'),
    (1306, 'Hull', 'QC', 'CA'),
    (1307, 'Huntingdon', 'QC', 'CA'),
    (1308, 'Iberville', 'QC', 'CA'),
    (1309, 'Ile-Perrot', 'QC', 'CA'),
    (1310, 'Jacques-Cartier', 'QC', 'CA'),
    (1311, 'Joliette', 'QC', 'CA'),
    (1312, 'Jonquiere', 'QC', 'CA'),
    (1313, 'Knowlton', 'QC', 'CA'),
    (1314, 'Kuujjuaq', 'QC', 'CA'),
    (1315, 'L''Assomption', 'QC', 'CA'),
    (1316, 'L''Epiphanie', 'QC', 'CA'),
    (1317, 'La Baie', 'QC', 'CA'),
    (1318, 'La Guadeloupe', 'QC', 'CA'),
    (1319, 'La Macaza', 'QC', 'CA'),
    (1320, 'La Malbaie', 'QC', 'CA'),
    (1321, 'La Salle', 'QC', 'CA'),
    (1322, 'La Sarre', 'QC', 'CA'),
    (1323, 'La Tuque', 'QC', 'CA'),
    (1324, 'Labelle', 'QC', 'CA'),
    (1325, 'Lac-Megantic', 'QC', 'CA'),
    (1326, 'Lachine', 'QC', 'CA'),
    (1327, 'Lachute', 'QC', 'CA'),
    (1328, 'Lacolle', 'QC', 'CA'),
    (1329, 'Lafontaine', 'QC', 'CA'),
    (1330, 'Lanoraie', 'QC', 'CA'),
    (1331, 'Laprairie', 'QC', 'CA'),
    (1332, 'Laurentides', 'QC', 'CA'),
    (1333, 'Lauzon', 'QC', 'CA'),
    (1334, 'Laval', 'QC', 'CA'),
    (1335, 'Laval-des-Rapides', 'QC', 'CA'),
    (1336, 'Laval-Ouest', 'QC', 'CA'),
    (1337, 'Lavaltrie', 'QC', 'CA'),
    (1338, 'Lennoxville', 'QC', 'CA'),
    (1339, 'Lery', 'QC', 'CA'),
    (1340, 'Les Escoumins', 'QC', 'CA'),
    (1341, 'Levis', 'QC', 'CA'),
    (1342, 'Liniere', 'QC', 'CA'),
    (1343, 'Longueuil', 'QC', 'CA'),
    (1344, 'Loretteville', 'QC', 'CA'),
    (1345, 'Louiseville', 'QC', 'CA'),
    (1346, 'Luceville', 'QC', 'CA'),
    (1347, 'Luskville', 'QC', 'CA'),
    (1348, 'Lyster', 'QC', 'CA'),
    (1349, 'Macamic', 'QC', 'CA'),
    (1350, 'Magog', 'QC', 'CA'),
    (1351, 'Magpie', 'QC', 'CA'),
    (1352, 'Malartic', 'QC', 'CA'),
    (1353, 'Maniwaki', 'QC', 'CA'),
    (1354, 'Manseau', 'QC', 'CA'),
    (1355, 'Maple Grove', 'QC', 'CA'),
    (1356, 'Marieville', 'QC', 'CA'),
    (1357, 'Masson', 'QC', 'CA'),
    (1358, 'Matagami', 'QC', 'CA'),
    (1359, 'Matane', 'QC', 'CA'),
    (1360, 'Mauricie', 'QC', 'CA'),
    (1361, 'McMasterville', 'QC', 'CA'),
    (1362, 'McWatters', 'QC', 'CA'),
    (1363, 'Melocheville', 'QC', 'CA'),
    (1364, 'Metabetchouan', 'QC', 'CA'),
    (1365, 'Millstream', 'QC', 'CA'),
    (1366, 'Mirabel', 'QC', 'CA'),
    (1367, 'Mont-Apica', 'QC', 'CA'),
    (1368, 'Mont-Joli', 'QC', 'CA'),
    (1369, 'Mont-Laurier', 'QC', 'CA'),
    (1370, 'Mont-Royal', 'QC', 'CA'),
    (1371, 'Mont-Saint-Hilaire', 'QC', 'CA'),
    (1372, 'Mont-Tremblant', 'QC', 'CA'),
    (1373, 'Montebello', 'QC', 'CA'),
    (1374, 'Montmagny', 'QC', 'CA'),
    (1375, 'Montreal', 'QC', 'CA'),
    (1376, 'Montreal-Est', 'QC', 'CA'),
    (1377, 'Montreal-Nord', 'QC', 'CA'),
    (1378, 'Mount-Royal', 'QC', 'CA'),
    (1379, 'Murdochville', 'QC', 'CA'),
    (1380, 'Napierville', 'QC', 'CA'),
    (1381, 'Natashquan', 'QC', 'CA'),
    (1382, 'Neuville', 'QC', 'CA'),
    (1383, 'New Richmond', 'QC', 'CA'),
    (1384, 'Nicolet', 'QC', 'CA'),
    (1385, 'Noranda', 'QC', 'CA'),
    (1386, 'Normandin', 'QC', 'CA'),
    (1387, 'Notre-Dame-des-Monts', 'QC', 'CA'),
    (1388, 'Notre-Dame-du-Lac', 'QC', 'CA'),
    (1389, 'Notre-Dame-du-Laus', 'QC', 'CA'),
    (1390, 'Notre-Dame-du-Nord', 'QC', 'CA'),
    (1391, 'Omerville', 'QC', 'CA'),
    (1392, 'Ormstown', 'QC', 'CA'),
    (1393, 'Otterburn Park', 'QC', 'CA'),
    (1394, 'Outremont', 'QC', 'CA'),
    (1395, 'Papineauville', 'QC', 'CA'),
    (1396, 'Parent', 'QC', 'CA'),
    (1397, 'Pierrefonds', 'QC', 'CA'),
    (1398, 'Pierreville', 'QC', 'CA'),
    (1399, 'Pincourt', 'QC', 'CA'),
    (1400, 'Plessisville', 'QC', 'CA'),
    (1401, 'Point Claire', 'QC', 'CA'),
    (1402, 'Pointe-aux-Trembles', 'QC', 'CA'),
    (1403, 'Pointe-Calumet', 'QC', 'CA'),
    (1404, 'Pointe-Claire', 'QC', 'CA'),
    (1405, 'Pointe-Lebel', 'QC', 'CA'),
    (1406, 'Pont-Rouge', 'QC', 'CA'),
    (1407, 'Port-Cartier', 'QC', 'CA'),
    (1408, 'Portneuf', 'QC', 'CA'),
    (1409, 'Price', 'QC', 'CA'),
    (1410, 'Princeville', 'QC', 'CA'),
    (1411, 'Puvirnituq', 'QC', 'CA'),
    (1412, 'Rawdon', 'QC', 'CA'),
    (1413, 'Repentigny', 'QC', 'CA'),
    (1414, 'Richelieu', 'QC', 'CA'),
    (1415, 'Rigaud', 'QC', 'CA'),
    (1416, 'Rimouski', 'QC', 'CA'),
    (1417, 'Ripon', 'QC', 'CA'),
    (1418, 'Riviere-des-Prairies', 'QC', 'CA'),
    (1419, 'Riviere-du-Loup', 'QC', 'CA'),
    (1420, 'Riviere-Pigou', 'QC', 'CA'),
    (1421, 'Roberval', 'QC', 'CA'),
    (1422, 'Rosemere', 'QC', 'CA'),
    (1423, 'Rouyn', 'QC', 'CA'),
    (1424, 'Roxboro', 'QC', 'CA'),
    (1425, 'Sacre-Coeur', 'QC', 'CA'),
    (1426, 'Saint Antoine', 'QC', 'CA'),
    (1427, 'Saint Bruno', 'QC', 'CA'),
    (1428, 'Saint Georges De Beauce', 'QC', 'CA'),
    (1429, 'Saint Guillaume', 'QC', 'CA'),
    (1430, 'Saint Isidore', 'QC', 'CA'),
    (1431, 'Saint Leonard', 'QC', 'CA'),
    (1432, 'Saint-Agapit', 'QC', 'CA'),
    (1433, 'Saint-Ambroise', 'QC', 'CA'),
    (1434, 'Saint-Anselme', 'QC', 'CA'),
    (1435, 'Saint-Augustin', 'QC', 'CA'),
    (1436, 'Saint-Basile', 'QC', 'CA'),
    (1437, 'Saint-Bruno-de-Montarville', 'QC', 'CA'),
    (1438, 'Saint-Cesaire', 'QC', 'CA'),
    (1439, 'Saint-Denis', 'QC', 'CA'),
    (1440, 'Saint-Eustache', 'QC', 'CA'),
    (1441, 'Saint-Fabien', 'QC', 'CA'),
    (1442, 'Saint-Felicien', 'QC', 'CA'),
    (1443, 'Saint-Felix-de-Valois', 'QC', 'CA'),
    (1444, 'Saint-Francois', 'QC', 'CA'),
    (1445, 'Saint-Gabriel', 'QC', 'CA'),
    (1446, 'Saint-Gedeon', 'QC', 'CA'),
    (1447, 'Saint-Georges', 'QC', 'CA'),
    (1448, 'Saint-Germain-de-Grantham', 'QC', 'CA'),
    (1449, 'Saint-Henri-de-Levis', 'QC', 'CA'),
    (1450, 'Saint-Henri-de-Taillon', 'QC', 'CA'),
    (1451, 'Saint-Hilaire', 'QC', 'CA'),
    (1452, 'Saint-Honore', 'QC', 'CA'),
    (1453, 'Saint-Hubert', 'QC', 'CA'),
    (1454, 'Saint-Hyacinthe', 'QC', 'CA'),
    (1455, 'Saint-Jacques', 'QC', 'CA'),
    (1456, 'Saint-Jean', 'QC', 'CA'),
    (1457, 'Saint-Jean-Eudes', 'QC', 'CA'),
    (1458, 'Saint-Jerome', 'QC', 'CA'),
    (1459, 'Saint-Joseph-de-Beauce', 'QC', 'CA'),
    (1460, 'Saint-Joseph-de-Kamouraska', 'QC', 'CA'),
    (1461, 'Saint-Jovite', 'QC', 'CA'),
    (1462, 'Saint-Lambert', 'QC', 'CA'),
    (1463, 'Saint-Lambert-Chambly', 'QC', 'CA'),
    (1464, 'Saint-Laurent', 'QC', 'CA'),
    (1465, 'Saint-Lazare', 'QC', 'CA'),
    (1466, 'Saint-Leonard', 'QC', 'CA'),
    (1467, 'Saint-Malo', 'QC', 'CA'),
    (1468, 'Saint-Marc-des-Carrieres', 'QC', 'CA'),
    (1469, 'Saint-Michel-des-Saints', 'QC', 'CA'),
    (1470, 'Saint-Nicolas', 'QC', 'CA'),
    (1471, 'Saint-Pascal', 'QC', 'CA'),
    (1472, 'Saint-Pie', 'QC', 'CA'),
    (1473, 'Saint-Prosper', 'QC', 'CA'),
    (1474, 'Saint-Raphael', 'QC', 'CA'),
    (1475, 'Saint-Raymond', 'QC', 'CA'),
    (1476, 'Saint-Remi', 'QC', 'CA'),
    (1477, 'Saint-Roch-de-Richelieu', 'QC', 'CA'),
    (1478, 'Saint-Romuald', 'QC', 'CA'),
    (1479, 'Saint-Sauveur', 'QC', 'CA'),
    (1480, 'Saint-Sauveur-des-Monts', 'QC', 'CA'),
    (1481, 'Saint-Stanislas-de-Kostka', 'QC', 'CA'),
    (1482, 'Saint-Tite', 'QC', 'CA'),
    (1483, 'Saint-Tite-des-Caps', 'QC', 'CA'),
    (1484, 'Saint-Vallier', 'QC', 'CA'),
    (1485, 'Saint-Vincent-de-Paul', 'QC', 'CA'),
    (1486, 'Saint-Zotique', 'QC', 'CA'),
    (1487, 'Sainte-Adele', 'QC', 'CA'),
    (1488, 'Sainte-Agathe', 'QC', 'CA'),
    (1489, 'Sainte-Agathe-des-Monts', 'QC', 'CA'),
    (1490, 'Sainte-Anne-de-Beaupre', 'QC', 'CA'),
    (1491, 'Sainte-Anne-de-Bellevue', 'QC', 'CA'),
    (1492, 'Sainte-Anne-des-Monts', 'QC', 'CA'),
    (1493, 'Sainte-Catherine', 'QC', 'CA'),
    (1494, 'Sainte-Cecile-de-Masham', 'QC', 'CA'),
    (1495, 'Sainte-Claire', 'QC', 'CA'),
    (1496, 'Sainte-Dorothee', 'QC', 'CA'),
    (1497, 'Sainte-Foy', 'QC', 'CA'),
    (1498, 'Sainte-Genevieve-de-Batiscan', 'QC', 'CA'),
    (1499, 'Sainte-Julienne', 'QC', 'CA'),
    (1500, 'Sainte-Marie', 'QC', 'CA'),
    (1501, 'Sainte-Martine', 'QC', 'CA'),
    (1502, 'Sainte-Rose', 'QC', 'CA'),
    (1503, 'Sainte-Therese', 'QC', 'CA'),
    (1504, 'Sainte-Therese-de-Blainville', 'QC', 'CA'),
    (1505, 'Salaberry-de-Valleyfield', 'QC', 'CA'),
    (1506, 'Sayabec', 'QC', 'CA'),
    (1507, 'Senneterre', 'QC', 'CA'),
    (1508, 'Senneville', 'QC', 'CA'),
    (1509, 'Sept-Iles', 'QC', 'CA'),
    (1510, 'Shawinigan', 'QC', 'CA'),
    (1511, 'Shawinigan-Sud', 'QC', 'CA'),
    (1512, 'Shawville', 'QC', 'CA'),
    (1513, 'Shefford', 'QC', 'CA'),
    (1514, 'Sheldrake', 'QC', 'CA'),
    (1515, 'Sherbrook', 'QC', 'CA'),
    (1516, 'Sherbrooke', 'QC', 'CA'),
    (1517, 'Shipshaw', 'QC', 'CA'),
    (1518, 'Sillery', 'QC', 'CA'),
    (1519, 'Sorel', 'QC', 'CA'),
    (1520, 'Stanstead', 'QC', 'CA'),
    (1521, 'Stoneham', 'QC', 'CA'),
    (1522, 'Sutton', 'QC', 'CA'),
    (1523, 'Tadoussac', 'QC', 'CA'),
    (1524, 'Taschereau', 'QC', 'CA'),
    (1525, 'Temiscaming', 'QC', 'CA'),
    (1526, 'Templeton', 'QC', 'CA'),
    (1527, 'Terrebonne', 'QC', 'CA'),
    (1528, 'Thetford Mines', 'QC', 'CA'),
    (1529, 'Thurso', 'QC', 'CA'),
    (1530, 'Tracy', 'QC', 'CA'),
    (1531, 'Trois-Pistoles', 'QC', 'CA'),
    (1532, 'Trois-Rivieres', 'QC', 'CA'),
    (1533, 'Val-Barrette', 'QC', 'CA'),
    (1534, 'Val-d''Or', 'QC', 'CA'),
    (1535, 'Val-David', 'QC', 'CA'),
    (1536, 'Valcourt', 'QC', 'CA'),
    (1537, 'Vallee-Jonction', 'QC', 'CA'),
    (1538, 'Valleyfield', 'QC', 'CA'),
    (1539, 'Varennes', 'QC', 'CA'),
    (1540, 'Vaudreuil', 'QC', 'CA'),
    (1541, 'Vercheres', 'QC', 'CA'),
    (1542, 'Verdun', 'QC', 'CA'),
    (1543, 'Victoriaville', 'QC', 'CA'),
    (1544, 'Ville-Marie', 'QC', 'CA'),
    (1545, 'Wakefield', 'QC', 'CA'),
    (1546, 'Warwick', 'QC', 'CA'),
    (1547, 'Waswanipi', 'QC', 'CA'),
    (1548, 'Waterville', 'QC', 'CA'),
    (1549, 'Weedon', 'QC', 'CA'),
    (1550, 'Westmount', 'QC', 'CA'),
    (1551, 'Yamachiche', 'QC', 'CA'),
    (1552, 'Yamaska', 'QC', 'CA'),
    (1553, 'Aberdeen', 'SK', 'CA'),
    (1554, 'Allan', 'SK', 'CA'),
    (1555, 'Arborfield', 'SK', 'CA'),
    (1556, 'Arcola', 'SK', 'CA'),
    (1557, 'Asquith', 'SK', 'CA'),
    (1558, 'Assiniboia', 'SK', 'CA'),
    (1559, 'Avonlea', 'SK', 'CA'),
    (1560, 'Big River', 'SK', 'CA'),
    (1561, 'Biggar', 'SK', 'CA'),
    (1562, 'Birsay', 'SK', 'CA'),
    (1563, 'Blaine Lake', 'SK', 'CA'),
    (1564, 'Brancepeth', 'SK', 'CA'),
    (1565, 'Broderick', 'SK', 'CA'),
    (1566, 'Bruno', 'SK', 'CA'),
    (1567, 'Buffalo Narrows', 'SK', 'CA'),
    (1568, 'Cabri', 'SK', 'CA'),
    (1569, 'Canoe Narrows', 'SK', 'CA'),
    (1570, 'Canora', 'SK', 'CA'),
    (1571, 'Carlyle', 'SK', 'CA'),
    (1572, 'Carnduff', 'SK', 'CA'),
    (1573, 'Caronport', 'SK', 'CA'),
    (1574, 'Carrot River', 'SK', 'CA'),
    (1575, 'Chamberlain', 'SK', 'CA'),
    (1576, 'Chaplin', 'SK', 'CA'),
    (1577, 'Clair', 'SK', 'CA'),
    (1578, 'Coderre', 'SK', 'CA'),
    (1579, 'Colonsay', 'SK', 'CA'),
    (1580, 'Cote', 'SK', 'CA'),
    (1581, 'Crooked River', 'SK', 'CA'),
    (1582, 'Cumberland House', 'SK', 'CA'),
    (1583, 'Cut Knife', 'SK', 'CA'),
    (1584, 'Dalmeny', 'SK', 'CA'),
    (1585, 'Denare Beach', 'SK', 'CA'),
    (1586, 'Dillon', 'SK', 'CA'),
    (1587, 'Dinsmore', 'SK', 'CA'),
    (1588, 'Dodsland', 'SK', 'CA'),
    (1589, 'Domremy', 'SK', 'CA'),
    (1590, 'Dorintosh', 'SK', 'CA'),
    (1591, 'Duck Lake', 'SK', 'CA'),
    (1592, 'Dundurn', 'SK', 'CA'),
    (1593, 'Dunfermline', 'SK', 'CA'),
    (1594, 'Edam', 'SK', 'CA'),
    (1595, 'Elbow', 'SK', 'CA'),
    (1596, 'Esterhazy', 'SK', 'CA'),
    (1597, 'Estevan', 'SK', 'CA'),
    (1598, 'Eston', 'SK', 'CA'),
    (1599, 'Fielding', 'SK', 'CA'),
    (1600, 'Foam Lake', 'SK', 'CA'),
    (1601, 'Fort Qu''Appelle', 'SK', 'CA'),
    (1602, 'Fox Valley', 'SK', 'CA'),
    (1603, 'Govan', 'SK', 'CA'),
    (1604, 'Gravelbourg', 'SK', 'CA'),
    (1605, 'Grenfell', 'SK', 'CA'),
    (1606, 'Griffin', 'SK', 'CA'),
    (1607, 'Gull Lake', 'SK', 'CA'),
    (1608, 'Hafford', 'SK', 'CA'),
    (1609, 'Hague', 'SK', 'CA'),
    (1610, 'Hanley', 'SK', 'CA'),
    (1611, 'Herschel', 'SK', 'CA'),
    (1612, 'Hudson Bay', 'SK', 'CA'),
    (1613, 'Humboldt', 'SK', 'CA'),
    (1614, 'Ile-a-la-Crosse', 'SK', 'CA'),
    (1615, 'Indian Head', 'SK', 'CA'),
    (1616, 'Kamsack', 'SK', 'CA'),
    (1617, 'Kelvington', 'SK', 'CA'),
    (1618, 'Kennedy', 'SK', 'CA'),
    (1619, 'Kerrobert', 'SK', 'CA'),
    (1620, 'Kindersley', 'SK', 'CA'),
    (1621, 'Kinistino', 'SK', 'CA'),
    (1622, 'La Ronge', 'SK', 'CA'),
    (1623, 'Lampman', 'SK', 'CA'),
    (1624, 'Lang', 'SK', 'CA');
    INSERT INTO `cities` (`id`, `city`, `state`, `country`) VALUES
    (1625, 'Langbank', 'SK', 'CA'),
    (1626, 'Langham', 'SK', 'CA'),
    (1627, 'Lanigan', 'SK', 'CA'),
    (1628, 'Lashburn', 'SK', 'CA'),
    (1629, 'Leader', 'SK', 'CA'),
    (1630, 'Lipton', 'SK', 'CA'),
    (1631, 'Livelong', 'SK', 'CA'),
    (1632, 'Loon Lake', 'SK', 'CA'),
    (1633, 'Lumsden', 'SK', 'CA'),
    (1634, 'Macklin', 'SK', 'CA'),
    (1635, 'Maidstone', 'SK', 'CA'),
    (1636, 'Maple Creek', 'SK', 'CA'),
    (1637, 'Meadow Lake', 'SK', 'CA'),
    (1638, 'Melfort', 'SK', 'CA'),
    (1639, 'Melville', 'SK', 'CA'),
    (1640, 'Michel', 'SK', 'CA'),
    (1641, 'Midale', 'SK', 'CA'),
    (1642, 'Milden', 'SK', 'CA'),
    (1643, 'Montmartre', 'SK', 'CA'),
    (1644, 'Montreal Lake', 'SK', 'CA'),
    (1645, 'Moose Jaw', 'SK', 'CA'),
    (1646, 'Moosomin', 'SK', 'CA'),
    (1647, 'Mortlach', 'SK', 'CA'),
    (1648, 'Mossbank', 'SK', 'CA'),
    (1649, 'Naicam', 'SK', 'CA'),
    (1650, 'Naisberry', 'SK', 'CA'),
    (1651, 'Neilburg', 'SK', 'CA'),
    (1652, 'Netherhill', 'SK', 'CA'),
    (1653, 'New Osgoode', 'SK', 'CA'),
    (1654, 'Nipawin', 'SK', 'CA'),
    (1655, 'North Battleford', 'SK', 'CA'),
    (1656, 'North Portal', 'SK', 'CA'),
    (1657, 'Onion Lake', 'SK', 'CA'),
    (1658, 'Outlook', 'SK', 'CA'),
    (1659, 'Oxbow', 'SK', 'CA'),
    (1660, 'Pascal', 'SK', 'CA'),
    (1661, 'Pilot Butte', 'SK', 'CA'),
    (1662, 'Plunkett', 'SK', 'CA'),
    (1663, 'Preeceville', 'SK', 'CA'),
    (1664, 'Prince', 'SK', 'CA'),
    (1665, 'Prince Albert', 'SK', 'CA'),
    (1666, 'Quill Lake', 'SK', 'CA'),
    (1667, 'Radisson', 'SK', 'CA'),
    (1668, 'Radville', 'SK', 'CA'),
    (1669, 'Redvers', 'SK', 'CA'),
    (1670, 'Regina', 'SK', 'CA'),
    (1671, 'Revenue', 'SK', 'CA'),
    (1672, 'Rockglen', 'SK', 'CA'),
    (1673, 'Rosetown', 'SK', 'CA'),
    (1674, 'Saint Brieux', 'SK', 'CA'),
    (1675, 'Saint Gregor', 'SK', 'CA'),
    (1676, 'Saint Louis', 'SK', 'CA'),
    (1677, 'Saint Walberg', 'SK', 'CA'),
    (1678, 'Saint Walburg', 'SK', 'CA'),
    (1679, 'Saint-Isidore-de-Bellevue', 'SK', 'CA'),
    (1680, 'Sandy Lake', 'SK', 'CA'),
    (1681, 'Saskatoon', 'SK', 'CA'),
    (1682, 'Shaunavon', 'SK', 'CA'),
    (1683, 'Shellbrook', 'SK', 'CA'),
    (1684, 'Silton', 'SK', 'CA'),
    (1685, 'Simmie', 'SK', 'CA'),
    (1686, 'Spiritwood', 'SK', 'CA'),
    (1687, 'Springwater', 'SK', 'CA'),
    (1688, 'Stranraer', 'SK', 'CA'),
    (1689, 'Swift Current', 'SK', 'CA'),
    (1690, 'Turtleford', 'SK', 'CA'),
    (1691, 'Unity', 'SK', 'CA'),
    (1692, 'Wadena', 'SK', 'CA'),
    (1693, 'Warman', 'SK', 'CA'),
    (1694, 'Watrous', 'SK', 'CA'),
    (1695, 'Watson', 'SK', 'CA'),
    (1696, 'Weyburn', 'SK', 'CA'),
    (1697, 'Whitewood', 'SK', 'CA'),
    (1698, 'Wilkie', 'SK', 'CA'),
    (1699, 'Willowbrook', 'SK', 'CA'),
    (1700, 'Wolseley', 'SK', 'CA'),
    (1701, 'Wymark', 'SK', 'CA'),
    (1702, 'Wynyard', 'SK', 'CA'),
    (1703, 'Yorkton', 'SK', 'CA'),
    (1704, 'Young', 'SK', 'CA'),
    (1705, 'Carmacks', 'YT', 'CA'),
    (1706, 'Dawson City', 'YT', 'CA'),
    (1707, 'Kaslo', 'YT', 'CA'),
    (1708, 'Old Crow', 'YT', 'CA'),
    (1709, 'Pelly Crossing', 'YT', 'CA'),
    (1710, 'Whitehorse', 'YT', 'CA'),
    (1711, 'Abbeville', 'AL', 'US'),
    (1712, 'Alabaster', 'AL', 'US'),
    (1713, 'Albertville', 'AL', 'US'),
    (1714, 'Alexander City', 'AL', 'US'),
    (1715, 'Andalusia', 'AL', 'US'),
    (1716, 'Anniston', 'AL', 'US'),
    (1717, 'Arab', 'AL', 'US'),
    (1718, 'Ardmore', 'AL', 'US'),
    (1719, 'Athens', 'AL', 'US'),
    (1720, 'Atmore', 'AL', 'US'),
    (1721, 'Attalla', 'AL', 'US'),
    (1722, 'Auburn', 'AL', 'US'),
    (1723, 'Bay Minette', 'AL', 'US'),
    (1724, 'Bessemer', 'AL', 'US'),
    (1725, 'Birmingham', 'AL', 'US'),
    (1726, 'Boaz', 'AL', 'US'),
    (1727, 'Brewton', 'AL', 'US'),
    (1728, 'Butler', 'AL', 'US'),
    (1729, 'Calera', 'AL', 'US'),
    (1730, 'Camden', 'AL', 'US'),
    (1731, 'Centre', 'AL', 'US'),
    (1732, 'Clanton', 'AL', 'US'),
    (1733, 'Columbiana', 'AL', 'US'),
    (1734, 'Cullman', 'AL', 'US'),
    (1735, 'Dadeville', 'AL', 'US'),
    (1736, 'Daleville', 'AL', 'US'),
    (1737, 'Daphne', 'AL', 'US'),
    (1738, 'Decatur', 'AL', 'US'),
    (1739, 'Demopolis', 'AL', 'US'),
    (1740, 'Dothan', 'AL', 'US'),
    (1741, 'Enterprise', 'AL', 'US'),
    (1742, 'Eufaula', 'AL', 'US'),
    (1743, 'Evergreen', 'AL', 'US'),
    (1744, 'Fairfield', 'AL', 'US'),
    (1745, 'Fairhope', 'AL', 'US'),
    (1746, 'Fayette', 'AL', 'US'),
    (1747, 'Florence', 'AL', 'US'),
    (1748, 'Foley', 'AL', 'US'),
    (1749, 'Fort Payne', 'AL', 'US'),
    (1750, 'Gadsden', 'AL', 'US'),
    (1751, 'Gardendale', 'AL', 'US'),
    (1752, 'Geneva', 'AL', 'US'),
    (1753, 'Greenville', 'AL', 'US'),
    (1754, 'Gulf Shores', 'AL', 'US'),
    (1755, 'Guntersville', 'AL', 'US'),
    (1756, 'Haleyville', 'AL', 'US'),
    (1757, 'Hamilton', 'AL', 'US'),
    (1758, 'Hanceville', 'AL', 'US'),
    (1759, 'Hartselle', 'AL', 'US'),
    (1760, 'Hazel Green', 'AL', 'US'),
    (1761, 'Huntsville', 'AL', 'US'),
    (1762, 'Jackson', 'AL', 'US'),
    (1763, 'Jacksonville', 'AL', 'US'),
    (1764, 'Jasper', 'AL', 'US'),
    (1765, 'Lanett', 'AL', 'US'),
    (1766, 'Leeds', 'AL', 'US'),
    (1767, 'Loxley', 'AL', 'US'),
    (1768, 'Madison', 'AL', 'US'),
    (1769, 'Millbrook', 'AL', 'US'),
    (1770, 'Mobile', 'AL', 'US'),
    (1771, 'Monroeville', 'AL', 'US'),
    (1772, 'Montevallo', 'AL', 'US'),
    (1773, 'Montgomery', 'AL', 'US'),
    (1774, 'Moulton', 'AL', 'US'),
    (1775, 'Muscle', 'AL', 'US'),
    (1776, 'Shoals', 'AL', 'US'),
    (1777, 'Northport', 'AL', 'US'),
    (1778, 'Oneonta', 'AL', 'US'),
    (1779, 'Opelika', 'AL', 'US'),
    (1780, 'Opp', 'AL', 'US'),
    (1781, 'Orange Beach', 'AL', 'US'),
    (1782, 'Oxford', 'AL', 'US'),
    (1783, 'Ozark', 'AL', 'US'),
    (1784, 'Pelham', 'AL', 'US'),
    (1785, 'Pell City', 'AL', 'US'),
    (1786, 'Phenix City', 'AL', 'US'),
    (1787, 'Piedmont', 'AL', 'US'),
    (1788, 'Pinson', 'AL', 'US'),
    (1789, 'Prattville', 'AL', 'US'),
    (1790, 'Rainbow City', 'AL', 'US'),
    (1791, 'Rainsville', 'AL', 'US'),
    (1792, 'Roanoke', 'AL', 'US'),
    (1793, 'Robertsdale', 'AL', 'US'),
    (1794, 'Russellville', 'AL', 'US'),
    (1795, 'Saraland', 'AL', 'US'),
    (1796, 'Scottsboro', 'AL', 'US'),
    (1797, 'Selma', 'AL', 'US'),
    (1798, 'Sheffield', 'AL', 'US'),
    (1799, 'Spanish Fort', 'AL', 'US'),
    (1800, 'Sylacauga', 'AL', 'US'),
    (1801, 'Talladega', 'AL', 'US'),
    (1802, 'Tallassee', 'AL', 'US'),
    (1803, 'Theodore', 'AL', 'US'),
    (1804, 'Thomasville', 'AL', 'US'),
    (1805, 'Troy', 'AL', 'US'),
    (1806, 'Trussville', 'AL', 'US'),
    (1807, 'Tuscaloosa', 'AL', 'US'),
    (1808, 'Tuscumbia', 'AL', 'US'),
    (1809, 'Tuskegee', 'AL', 'US'),
    (1810, 'Union Springs', 'AL', 'US'),
    (1811, 'Valley', 'AL', 'US'),
    (1812, 'Warrior', 'AL', 'US'),
    (1813, 'Wetumpka', 'AL', 'US'),
    (1814, 'Winfield', 'AL', 'US'),
    (1815, 'Anchorage', 'AK', 'US'),
    (1816, 'Barrow', 'AK', 'US'),
    (1817, 'Bethel', 'AK', 'US'),
    (1818, 'Cordova', 'AK', 'US'),
    (1819, 'Dillingham', 'AK', 'US'),
    (1820, 'Eagle River', 'AK', 'US'),
    (1821, 'Fairbanks', 'AK', 'US'),
    (1822, 'Fort Wainwright', 'AK', 'US'),
    (1823, 'Haines', 'AK', 'US'),
    (1824, 'Homer', 'AK', 'US'),
    (1825, 'Juneau', 'AK', 'US'),
    (1826, 'Kenai', 'AK', 'US'),
    (1827, 'Ketchikan', 'AK', 'US'),
    (1828, 'Kodiak', 'AK', 'US'),
    (1829, 'Kotzebue', 'AK', 'US'),
    (1830, 'Nome', 'AK', 'US'),
    (1831, 'North Pole', 'AK', 'US'),
    (1832, 'Palmer', 'AK', 'US'),
    (1833, 'Petersburg', 'AK', 'US'),
    (1834, 'Seward', 'AK', 'US'),
    (1835, 'Sitka', 'AK', 'US'),
    (1836, 'Skagway', 'AK', 'US'),
    (1837, 'Soldotna', 'AK', 'US'),
    (1838, 'Unalaska', 'AK', 'US'),
    (1839, 'Valdez', 'AK', 'US'),
    (1840, 'Wasilla', 'AK', 'US'),
    (1841, 'Wrangell', 'AK', 'US'),
    (1842, 'Ajo', 'AZ', 'US'),
    (1843, 'Apache Junction', 'AZ', 'US'),
    (1844, 'Avondale', 'AZ', 'US'),
    (1845, 'Benson', 'AZ', 'US'),
    (1846, 'Bisbee', 'AZ', 'US'),
    (1847, 'Buckeye', 'AZ', 'US'),
    (1848, 'Bullhead City', 'AZ', 'US'),
    (1849, 'Camp Verde', 'AZ', 'US'),
    (1850, 'Casa Grande', 'AZ', 'US'),
    (1851, 'Cave Creek', 'AZ', 'US'),
    (1852, 'Chandler', 'AZ', 'US'),
    (1853, 'Chinle', 'AZ', 'US'),
    (1854, 'Chino Valley', 'AZ', 'US'),
    (1855, 'Coolidge', 'AZ', 'US'),
    (1856, 'Cottonwood', 'AZ', 'US'),
    (1857, 'Douglas', 'AZ', 'US'),
    (1858, 'Flagstaff', 'AZ', 'US'),
    (1859, 'Fort Defiance', 'AZ', 'US'),
    (1860, 'Fort Mohave', 'AZ', 'US'),
    (1861, 'Fountain Hills', 'AZ', 'US'),
    (1862, 'Gilbert', 'AZ', 'US'),
    (1863, 'Glendale', 'AZ', 'US'),
    (1864, 'Globe', 'AZ', 'US'),
    (1865, 'Goodyear', 'AZ', 'US'),
    (1866, 'Green Valley', 'AZ', 'US'),
    (1867, 'Holbrook', 'AZ', 'US'),
    (1868, 'Kayenta', 'AZ', 'US'),
    (1869, 'Kingman', 'AZ', 'US'),
    (1870, 'Lake Havasu City', 'AZ', 'US'),
    (1871, 'Lakeside', 'AZ', 'US'),
    (1872, 'Litchfield Park', 'AZ', 'US'),
    (1873, 'Mesa', 'AZ', 'US'),
    (1874, 'Mohave Valley', 'AZ', 'US'),
    (1875, 'Nogales', 'AZ', 'US'),
    (1876, 'Page', 'AZ', 'US'),
    (1877, 'Paradise Valley', 'AZ', 'US'),
    (1878, 'Parker', 'AZ', 'US'),
    (1879, 'Payson', 'AZ', 'US'),
    (1880, 'Peoria', 'AZ', 'US'),
    (1881, 'Phoenix', 'AZ', 'US'),
    (1882, 'Pinetop', 'AZ', 'US'),
    (1883, 'Prescott', 'AZ', 'US'),
    (1884, 'Prescott Valley', 'AZ', 'US'),
    (1885, 'Quartzsite', 'AZ', 'US'),
    (1886, 'Safford', 'AZ', 'US'),
    (1887, 'Saint Johns', 'AZ', 'US'),
    (1888, 'Scottsdale', 'AZ', 'US'),
    (1889, 'Sedona', 'AZ', 'US'),
    (1890, 'Show Low', 'AZ', 'US'),
    (1891, 'Snowflake', 'AZ', 'US'),
    (1892, 'Springerville', 'AZ', 'US'),
    (1893, 'Sun City', 'AZ', 'US'),
    (1894, 'Sun City West', 'AZ', 'US'),
    (1895, 'Surprise', 'AZ', 'US'),
    (1896, 'Tempe', 'AZ', 'US'),
    (1897, 'Tuba City', 'AZ', 'US'),
    (1898, 'Tucson', 'AZ', 'US'),
    (1899, 'Whiteriver', 'AZ', 'US'),
    (1900, 'Wickenburg', 'AZ', 'US'),
    (1901, 'Willcox', 'AZ', 'US'),
    (1902, 'Window Rock', 'AZ', 'US'),
    (1903, 'Winslow', 'AZ', 'US'),
    (1904, 'Yuma', 'AZ', 'US'),
    (1905, 'Alma', 'AR', 'US'),
    (1906, 'Arkadelphia', 'AR', 'US'),
    (1907, 'Ashdown', 'AR', 'US'),
    (1908, 'Batesville', 'AR', 'US'),
    (1909, 'Beebe', 'AR', 'US'),
    (1910, 'Bella Vista', 'AR', 'US'),
    (1911, 'Benton', 'AR', 'US'),
    (1912, 'Bentonville', 'AR', 'US'),
    (1913, 'Berryville', 'AR', 'US'),
    (1914, 'Blytheville', 'AR', 'US'),
    (1915, 'Bonne

    public by cghersi  7188  32  7  0

    Drop all indexes of a MS SQL Server database

    This script removes all the indexes of the currrent database in use
    DECLARE idx CURSOR FOR 
    	SELECT	IDX.name AS idxName, OBJ.name AS tblName 
    	FROM	sys.indexes IDX inner join
    			sys.all_objects OBJ ON IDX.object_id = OBJ.object_id 
    	WHERE	(IDX.type) = 2 AND (OBJ.type = 'U')
    OPEN idx
    
    DECLARE @name varchar(200)
    DECLARE @tablename varchar(200)
    
    FETCH NEXT FROM idx INTO @name, @tablename
    WHILE @@fetch_status = 0
    BEGIN
    
    	EXEC('DROP INDEX ' + @name + ' ON ' + @tablename) 
    
    	FETCH NEXT FROM idx INTO @name, @tablename
    
    END
    
    CLOSE idx
    DEALLOCATE idx
    GO

    public by cghersi  4273  0  6  1

    How to execute an SQL statement in VBA

    This simple method allows to execute the given SQL query, returning true if there was no error, or false otherwise. The SQL statement is not supposed to return resultsets, like an INSERT, UPDATE or DELETE
    Public Function ExecCmd(ByVal sql As String) As Boolean
    On Error GoTo ErrorCmd
    
      Dim conn As ADODB.Connection
      Dim cmd As ADODB.Command
          
      'Init db objects
      'Connection is the current DB one
      Set conn = CurrentProject.Connection
      Set cmd = New ADODB.Command
        
      With cmd
          .ActiveConnection = conn
          .CommandText = sql
          .CommandType = adCmdText
          .Execute
      End With
    
      ExecCmd = True
        
    Exit_go: Exit Function
    ErrorCmd:
      MsgBox Err.Description
      ExecCmd = False
    End Function

    public by cghersi  3359  3  6  4

    Change the location of MS SQL Server database files

    When you start playing with serious DB, one of the most important parts is to place (if possible) the files in different drives (physical, not only logical partitions!!!). But what if you already have your DB in production with tons of data on the same location? This script helps in solving the issue. It should be executed in three steps: 1) p
    -- 1) Execute this first chunk to put the DB offline
    ALTER DATABASE MyDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE MyDBName SET OFFLINE;
    ALTER DATABASE MyDBName MODIFY FILE 
    (
       Name = MyDBName_Data,
       Filename = 'D:\DB\MyDBName.mdf'
    );
    
    ALTER DATABASE MyDBName MODIFY FILE 
    (
       Name = MyDBName_Log, 
       Filename = 'D:\DBLog\MyDBName_log.ldf'
    );
    
    ALTER DATABASE MyDBName MODIFY FILE 
    (
       Name = MyDBName_Idx, 
       Filename = 'E:\DBIndex\MyDBName_log.ndf'
    );
    
    -- 2) Manually move the files in the right location
    
    -- 3) Execute this second chunk to put the DB online
    ALTER DATABASE my SET ONLINE;
    
    ALTER DATABASE my SET MULTI_USER;

    public by cghersi  3834  0  6  3

    How to clean all the data from the tables of a MS SQL Server database

    This procedure allows to clean all the data from all the tables of the given DB. Use @rese_ident = 1 if you want to restart the identity columns from the default value.
    CREATE PROCEDURE [dbo].[CleanDB]
    	@db_name sysname = 'MyDBName',
    	@reset_ident tinyint = 0
    as
    begin
    	set nocount on
    
    	if @db_name is null
    		set @db_name = db_name()
    
    	if @reset_ident is null
    		set @reset_ident = 0
    
    	if not exists(select * from sys.databases where name = @db_name and database_id > 4)
    	begin
    		raiserror('Database does not exist or it can not be cleared', 16, 1)
    		return
    	end
    
    	declare @recovery_mode sysname
    
    	select @recovery_mode = cast(DatabasePropertyEx(@db_name,'Recovery') as sysname)
    
    	if @recovery_mode <> 'Simple'
    	begin
    		declare @ncmd nvarchar(max)
    
    		print '-- Changing database recovery mode to SIMPLE'
    
    		set @ncmd = 'alter database [' + @db_name + '] set recovery simple'
    
    		print @ncmd
    
    		exec sp_executesql @ncmd
    	end
    
    	-------------------------------------------------------------------------------------------
    	-- prepare table with tables list
    
    	create table #temp_tables
    	(
    		rec_id int identity(1, 1) primary key not null,
    		schema_name sysname not null,
    		table_name sysname not null
    	)
    
    	declare @n_cmd nvarchar(max)
    
    	set @n_cmd = 'insert into #temp_tables (schema_name, table_name) select ss.name as schema_name, st.name as table_name from [' + @db_name + '].sys.tables as st inner join [' + @db_name + '].sys.schemas as ss on ss.schema_id = st.schema_id where ss.name <> ''sys'' '
    
    	exec sp_executesql @n_cmd
    
    	-------------------------------------------------------------------------------------------
    	-- disable constraints
    
    	declare @table_name sysname
    	declare @schema_name sysname
    	declare @counter_max int
    	declare @counter int
    
    	select @counter_max = max(rec_id) from #temp_tables
    
    	if @counter_max is null
    		set @counter_max = 0
    
    	declare @object_name nvarchar(max)
    
    	set @counter = @counter_max
    	while @counter > 0
    	begin
    		set @table_name = null
    		set @schema_name = null
    
    		select @table_name = table_name, @schema_name = schema_name from #temp_tables where rec_id = @counter
    
    		if @table_name is null or @schema_name is null
    			break
    
    		set @object_name = N'[' + @db_name + N'].[' + @schema_name + N'].[' + @table_name + N']'
    
    		set @n_cmd = N'alter table ' + @object_name + N' nocheck constraint all'
    
    		print @n_cmd
    
    		begin try
    			exec sp_executesql @n_cmd
    		end try
    		begin catch
    			print '-------------------------------------------------------------------------'
    			print 'ERROR - Could not disable constraints for table ' + @object_name
    			print error_message()
    			print '-------------------------------------------------------------------------'
    		end catch
    
    		set @counter = @counter - 1
    	end
    
    	----------------------------------------------------------------------------------------------
    	-- delete records from tables
    
    	set @counter = @counter_max
    	while @counter > 0
    	begin
    		set @table_name = null
    		set @schema_name = null
    
    		select @table_name = table_name, @schema_name = schema_name from #temp_tables where rec_id = @counter
    
    		if @table_name is null or @schema_name is null
    			break
    
    		set @object_name = N'[' + @db_name + N'].[' + @schema_name + N'].[' + @table_name + N']'
    
    		set @n_cmd = 'delete ' + @object_name
    
    		print @n_cmd
    
    		begin try
    			exec sp_executesql @n_cmd
    
    			if @reset_ident = 1
    			begin
    				set @n_cmd = 'if exists(select * from [' + @db_name + '].sys.columns where object_id = object_id(''' + 
    					@object_name + ''') and is_identity = 1) dbcc checkident(''' + @object_name + ''', reseed)'
    
    				print @n_cmd
    
    				exec sp_executesql @n_cmd
    			end
    		end try
    		begin catch
    			print '-------------------------------------------------------------------------'
    			print 'ERROR - Could not clean table ' + @object_name
    			print error_message()
    			print '-------------------------------------------------------------------------'
    		end catch
    
    		set @counter = @counter - 1
    	end
    
    	-----------------------------------------------------------------------------------------------
    	-- enable constraints
    
    	set @counter = @counter_max
    	while @counter > 0
    	begin
    		set @table_name = null
    		set @schema_name = null
    
    		select @table_name = table_name, @schema_name = schema_name from #temp_tables where rec_id = @counter
    
    		if @table_name is null or @schema_name is null
    			break
    
    		set @n_cmd = 'alter table [' + @db_name + '].[' + @schema_name + '].[' + @table_name + '] with check check constraint all'
    
    		print @n_cmd
    
    		begin try
    			exec sp_executesql @n_cmd
    		end try
    		begin catch
    			print '-------------------------------------------------------------------------'
    			print 'ERROR - Could not enable constraints for table ' + @object_name
    			print error_message()
    			print '-------------------------------------------------------------------------'
    		end catch
    
    		set @counter = @counter - 1
    	end
    
    	drop table #temp_tables
    
    	---------------------------------------------------------------------------------------------------
    	-- restore database recovery mode
    
    	if @recovery_mode <> 'Simple'
    	begin
    		declare @ncmd2 nvarchar(max)
    
    		print '-- Restoring database recovery mode'
    
    		set @ncmd2 = 'alter database [' + @db_name + '] set recovery ' + @recovery_mode
    
    		print @ncmd2
    
    		exec sp_executesql @ncmd2
    	end
    
    end

    public by cghersi @ Apache Cayenne DEV  3646  0  7  0

    Use Apache Cayenne under OSGi environment

    This class presents the minimal behavior to show how to use Apache Cayenne framework in an OSGi container. The class is the activator of the OSGi Bundle and publishes a minimal set of CRUD methods for an example MyDBBean table. It implies the existence of a MyDBBean class, maybe generated with Cayenne Modeler; in the example MyDBBean class is in
    package osgicayennemanager;
    
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.Collection;
    import java.util.List;
    
    import javax.sql.DataSource;
    
    import org.apache.cayenne.Cayenne;
    import org.apache.cayenne.CayenneRuntimeException;
    import org.apache.cayenne.ObjectContext;
    import org.apache.cayenne.ObjectId;
    import org.apache.cayenne.configuration.server.ServerRuntime;
    import org.apache.cayenne.di.spi.ClassRegistry;
    import org.apache.cayenne.exp.Expression;
    import org.apache.cayenne.exp.ExpressionFactory;
    import org.apache.cayenne.query.ObjectIdQuery;
    import org.apache.cayenne.query.Ordering;
    import org.apache.cayenne.query.QueryCacheStrategy;
    import org.apache.cayenne.query.SelectQuery;
    import org.apache.cayenne.query.SortOrder;
    import org.osgi.framework.Bundle;
    import org.osgi.framework.BundleActivator;
    import org.osgi.framework.BundleContext;
    import org.osgi.framework.FrameworkUtil;
    import org.osgi.framework.wiring.BundleWiring;
    
    public class Activator implements BundleActivator {
    
    	private static ServerRuntime dbRuntime;
    	
    	//this is useful if you want to directly get the connection to the DB
    	private static DataSource dbDS;
    	
    	/////////////////////////////////////////////////////////////////////
    	/// OSGI BundleActivator implementation
    	/////////////////////////////////////////////////////////////////////
    	public void start(BundleContext bundleContext) throws Exception {
    		initDBConnection(); 		//initialize internal structures
    		
    		//now you are ready to perform CRUD operations...
    	}
    
    	public void stop(BundleContext bundleContext) throws Exception {
    		//nothing to do here...
    	}
    	/////////////////////////////////////////////////////////////////////
    	/// END OSGI BundleActivator implementation
    	/////////////////////////////////////////////////////////////////////
    	
    	/////////////////////////////////////////////////////////////////////
    	/// CRUD Operations
    	/////////////////////////////////////////////////////////////////////
    	/**
    	 * Create a new record in MyDBBean table
    	 * @param field1
    	 * @param field2
    	 * @return the created record
    	 */
    	public MyDBBean addMyDBBean(String field1, String field2) {	
    		//MyBean is a class generated via CayenneModeler that maps a table in the DB
    		ObjectContext context = getDbContext();
    
    		MyDBBean obj = createNewObj(context, MyDBBean.class);
    		obj.setField1(field1);
    		obj.setField2(field2);
    		commitObjects(context);
    
    		return obj;
    	}
    	
    	/**
    	 * Deletes the given record from MyDBBean table
    	 * @param id PK of the record
    	 */
    	public void deleteMyDBBean(int id) {
    		ObjectContext context = getDbContext();
    		MyDBBean o = getByPK(context, id, MyDBBean.ID_PK_COLUMN, MyDBBean.class);
    		deleteObject(context, o);
    	}
    	
    	/**
    	 * Updates field1 for the given record
    	 * @param id PK of the record to change
    	 * @param newField1 new value for field1
    	 */
    	public void updateMyDBBean(int id, String newField1) {
    		ObjectContext context = getDbContext();
    		MyDBBean o = getByPK(context, id, MyDBBean.ID_PK_COLUMN, MyDBBean.class);
    		if (o == null)
    			return;
    		o.setField1(newField1);
    		commitObjects(context);
    	}
    	
    	/**
    	 * Retrieves the record identified by the given PK
    	 * @param id PK of the record to return
    	 * @return null if not found
    	 */
    	public MyDBBean getMyDBBeanByPK(int id) {
    		ObjectContext context = getDbContext(); 
    		return getByPK(context, id, MyDBBean.ID_PK_COLUMN, MyDBBean.class);
    	}
    	
    	/**
    	 * Retrieves the record(s) identified by the given value of field1
    	 * @param field1 filter of the query
    	 * @return the list of retrieved records, null if not found
    	 */
    	public List<MyDBBean> getMyDBBeanByField1(String field1) {
    		Ordering[] ordering = new Ordering[] {new Ordering(MyDBBean.ID_PK_COLUMN, SortOrder.ASCENDING)};
    		Expression qualifier = ExpressionFactory.matchExp(MyDBBean.FIELD1_PROPERTY, field1);
    		return query(getDbContext(), qualifier, null, 0, ordering, MyDBBean.class, false, true);
    	}
    	/////////////////////////////////////////////////////////////////////
    	/// END CRUD Operations
    	/////////////////////////////////////////////////////////////////////
    	
    	/////////////////////////////////////////////////////////////////////
    	/// Internal Utilities
    	/////////////////////////////////////////////////////////////////////
    	private static void initDBConnection() {
    		//stores the objects that can be useful in the life of the bundle
    		dbRuntime = new ServerRuntime("cayenne-MyDomain.xml");
    		dbDS = dbRuntime.getDataSource("MyNode");
    		
    		//loads the classes that maps the DB tables in a OSGI-friendly manner.
    		//"osgicayennemanager" is the package which these classes belong to 
    		List<Class<?>> classes = getClassesOfPackage(FrameworkUtil.getBundle(Activator.class), "osgicayennemanager");
    		for (Class<?> cl : classes) {
    			ClassRegistry.unRegisterClass(cl);
    		}
    		for (Class<?> cl : classes) {
    			ClassRegistry.registerClass(cl);
    		}
    	}
    	
    	private static List<Class<?>> getClassesOfPackage(Bundle b, String packageName) {
    		BundleWiring wiring = b.adapt(BundleWiring.class);
    		Collection<String> classNames = wiring.listResources(packageName.replace('.', '/'), null, 
    					BundleWiring.LISTRESOURCES_LOCAL);
    		List<Class<?>> result = new ArrayList<Class<?>>();
    		for (String cName : classNames) {
    			Class<?> c = null;
    			try {
    				c = b.loadClass(cName.replace(".class", "").replace('/', '.'));
    			} catch (ClassNotFoundException e) {
    				continue;
    			}
    			if (c != null)
    				result.add(c);
    		}
    		return result;
    	}
    	
    	protected static Connection getConnection() throws SQLException {
    		return dbDS.getConnection();
    	}
    	
    	private static ObjectContext getDbContext() {
    		return dbRuntime.getContext();
    	}
    	
    	private <E> E createNewObj(ObjectContext context, Class<E> clazz) {
    		return context.newObject(clazz);
    	}
    	
    	private boolean commitObjects(ObjectContext context) {
    		try {
    			context.commitChanges();
    			return true;
    		} catch(CayenneRuntimeException e){
    			manageCayenneException(e, context);
    			return false;
    		} catch (Exception e) {
    			manageCayenneException(e, context);
    			return false;
    		}
    	}
    	
    	protected void deleteObject(ObjectContext context, Object o) {
    		if (o == null) 
    			return;
    
    		try {
    			context.deleteObjects(o);
    			context.commitChanges();
    		} catch(CayenneRuntimeException e){
    			manageCayenneException(e, context);
    		} catch (Exception e) {
    			manageCayenneException(e, context);
    		}
    	}
    	
    	@SuppressWarnings("unchecked")
    	private <E> List<E> query(ObjectContext context, Expression qualifier, Integer maxResult, Integer offset, 
    			Ordering[] ordering, Class<E> clazz, boolean setDistinct, boolean enableCache) {
    		SelectQuery select = new SelectQuery(clazz, qualifier);
    		if (enableCache) {
    			select.setCacheStrategy(QueryCacheStrategy.SHARED_CACHE);
    			select.setCacheGroups(clazz.getName());
    		}
    		if (ordering != null) {
    			for (int i = 0; i < ordering.length; i++) {
    				if (ordering[i] != null)
    					select.addOrdering(ordering[i]);
    			}
    		}
    		if (maxResult != null && maxResult > 0)
    			select.setFetchLimit(maxResult);
    		if (offset != null && offset.intValue() > 0)
    			select.setFetchOffset(offset.intValue());
    		if (setDistinct)
    			select.setDistinct(true);
    		return context.performQuery(select);
    	}
    	
    	@SuppressWarnings("unchecked")
    	private <E> E getByPK(ObjectContext context, int pkID, String key, Class<E> clazz) {
    		ObjectId id = new ObjectId(clazz.getSimpleName(), key, pkID);
    		ObjectIdQuery query = new ObjectIdQuery(id, false, ObjectIdQuery.CACHE_REFRESH);
    		return (E)Cayenne.objectForQuery(context, query);
    	}
    	
    	private static void manageCayenneException(Exception e, ObjectContext context) {
    		System.err.println("Error while commmitting changes due to " + e);
    		try{
    			context.rollbackChanges();
    		} catch(Exception e1){
    			System.err.println("Error while rollback changes due to " + e1);
    		}
    	}
    	/////////////////////////////////////////////////////////////////////
    	/// END Internal Utilities
    	/////////////////////////////////////////////////////////////////////
    }
    • Public Snippets
    • Channels Snippets