USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDB')

DROP DATABASE TestDB
GO
Create database TestDB
go

USE [TestDB] 
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].

[TestLocationTable]') AND type in (N'U')) 

DROP TABLE [dbo].[TestLocationTable] 
GO 
USE [TestDB] 
GO 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
SET ANSI_PADDING ON 
GO 
CREATE TABLE [dbo].[TestLocationTable]( 
[Id] [int] NULL, 
[shortname] [char](3) NULL, 
[name] [varchar](100) NULL 
) ON [PRIMARY] 
GO 
SET ANSI_PADDING OFF 
GO

USE [TestDB] 
GO 
insert into TestLocationTable ( Id, shortname, Name) select 1, 'NA1', 'NewYork' 
insert into TestLocationTable ( Id, shortname, Name) select 2, 'NA2', 'NewYork' 
insert into TestLocationTable ( Id, shortname, Name) select 3, 'NA3', 'NewYork' 
insert into TestLocationTable ( Id, shortname, Name) select 4, 'EU1', 'London' 
insert into TestLocationTable ( Id, shortname, Name) select 5, 'EU2', 'London' 
insert into TestLocationTable ( Id, shortname, Name) select 6, 'AS1', 'Tokyo' 
insert into TestLocationTable ( Id, shortname, Name) select 7, 'AS2', 'HongKong' 
go

USE [TestDB] 
GO 
IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id 
WHERE st.name = N'OfficeLocation_Tabetype' AND ss.name = N'dbo') 
DROP TYPE [dbo].[OfficeLocation_Tabetype] 
GO 
USE [TestDB] 
GO 
CREATE TYPE [dbo].[OfficeLocation_Tabetype] AS TABLE( 
[Id] [int] NULL, 
[shortname] [char](3) NULL, 
[name] [varchar](100) NULL 
) 
GO