if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cthPTenantM]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[cthPTenantM]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ctpPracticeM]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ctpPracticeM]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ctpTownT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ctpTownT]
GO

CREATE TABLE [dbo].[cthPTenantM] (
	[PracticeCode] [varchar] (8) NOT NULL ,
	[TownNo] [int] NOT NULL ,
	[TownSeq] [int] NOT NULL ,
	[TenantType] [varchar] (20) NULL ,
	[PrivateCode] [varchar] (12) NULL ,
	[CompanyCode] [varchar] (12) NULL ,
	[TenantCode] [varchar] (12) NULL ,
	[HPMode] [varchar] (2) NULL ,
	[DefaultDoc] [varchar] (1000) NULL ,
	[ExtURL] [varchar] (1000) NULL ,
	[DomainName] [varchar] (255) NULL ,
	[DomainTLD] [varchar] (16) NULL ,
	[TenantName] [varchar] (40) NULL ,
	[TenantNameFont] [varchar] (40) NULL ,
	[TenantNameSize] [varchar] (1) NULL ,
	[TenantCopy] [varchar] (100) NULL ,
	[TenantGuide] [varchar] (255) NULL ,
	[TenantGuideFont] [varchar] (40) NULL ,
	[TenantGuideSize] [varchar] (1) NULL ,
	[CountVTitle1] [varchar] (100) NULL ,
	[CountVTitle2] [varchar] (100) NULL ,
	[CountSTitle1] [varchar] (100) NULL ,
	[CountSTitle2] [varchar] (100) NULL ,
	[CountVisit] [money] NULL ,
	[CountSells] [money] NULL ,
	[CurCode] [varchar] (10) NULL ,
	[ConsumptionTax] [money] NULL ,
	[TransportFlag] [int] NULL ,
	[ShopDiscAmnt] [money] NULL ,
	[ShopDiscRate] [real] NULL ,
	[BackGroundImage] [varchar] (255) NULL ,
	[ShopImage] [varchar] (100) NULL ,
	[ForeImage] [varchar] (100) NULL ,
	[BackImage] [varchar] (100) NULL ,
	[GuideImage] [varchar] (100) NULL ,
	[CountImage] [varchar] (100) NULL ,
	[TenantCopyColor] [varchar] (10) NULL ,
	[TenantForeColor] [varchar] (10) NULL ,
	[TenantBackColor] [varchar] (10) NULL ,
	[GuideForeColor] [varchar] (10) NULL ,
	[GuideBackColor] [varchar] (10) NULL ,
	[ItemPageForeColor] [varchar] (10) NULL ,
	[ItemPageBackColor] [varchar] (10) NULL ,
	[ItemNameForeColor] [varchar] (10) NULL ,
	[ItemNameBackColor] [varchar] (10) NULL ,
	[ShelfForeFont1] [varchar] (40) NULL ,
	[ShelfForeSize1] [varchar] (1) NULL ,
	[ShelfForeColor1] [varchar] (10) NULL ,
	[ShelfBackColor1] [varchar] (10) NULL ,
	[ShelfForeFont2] [varchar] (40) NULL ,
	[ShelfForeSize2] [varchar] (1) NULL ,
	[ShelfForeColor2] [varchar] (10) NULL ,
	[ShelfBackColor2] [varchar] (10) NULL ,
	[ShelfForeFont3] [varchar] (40) NULL ,
	[ShelfForeSize3] [varchar] (1) NULL ,
	[ShelfForeColor3] [varchar] (10) NULL ,
	[ShelfBackColor3] [varchar] (10) NULL ,
	[ShelfForeFont4] [varchar] (40) NULL ,
	[ShelfForeSize4] [varchar] (1) NULL ,
	[ShelfForeColor4] [varchar] (10) NULL ,
	[ShelfBackColor4] [varchar] (10) NULL ,
	[ShelfFrameColor] [varchar] (10) NULL ,
	[ShelfFrameType] [varchar] (1) NULL ,
	[MyHPTitle] [varchar] (40) NULL ,
	[MyHPLink] [varchar] (100) NULL ,
	[MyFileTitle] [varchar] (40) NULL ,
	[MyFileLink] [varchar] (255) NULL ,
	[MyCharLink] [varchar] (255) NULL ,
	[LoginId] [varchar] (12) NULL ,
	[EstablishDate] [datetime] NULL ,
	[CloseDate] [datetime] NULL ,
	[CloseFlag] [int] NULL ,
	[ActionFlag] [smallint] NULL ,
	[AddDate] [datetime] NULL ,
	[UpdDate] [datetime] NULL ,
	[AddProc] [varchar] (20) NULL ,
	[UpdProc] [varchar] (20) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ctpPracticeM] (
	[PracticeType] [varchar] (8) NULL ,
	[PracticeCode] [varchar] (8) NOT NULL ,
	[PracticeSeq] [int] NULL ,
	[PracticeName] [varchar] (40) NULL ,
	[UnusedFlag] [tinyint] NULL ,
	[TCloseFlag] [tinyint] NULL ,
	[DefaultFlag] [smallint] NULL ,
	[MasterPwd] [varchar] (8) NULL ,
	[Remarks] [varchar] (250) NULL ,
	[BgImage] [varchar] (50) NULL ,
	[StartDate] [datetime] NULL ,
	[EndDate] [datetime] NULL ,
	[NowDate] [datetime] NULL ,
	[Settlement] [smallint] NULL ,
	[ExCode] [varchar] (3) NULL ,
	[ExRate] [real] NULL ,
	[ExConsumptionTax] [money] NULL ,
	[ConsumptionTax] [money] NULL ,
	[ComputeFlag] [smallint] NULL ,
	[PCapitalAmount] [money] NULL ,
	[CCapitalAmount] [money] NULL ,
	[PCapitalAmountLock] [varchar] (2) NULL ,
	[CCapitalAmountLock] [varchar] (2) NULL ,
	[COfficeGuarantee] [money] NULL ,
	[COfficeRentalFee] [money] NULL ,
	[COfficeAssetsPrice] [money] NULL ,
	[HpOpeningCostH] [money] NULL ,
	[HpOpeningCostL] [money] NULL ,
	[HpRentalFeeH] [money] NULL ,
	[HpRentalFeeL] [money] NULL ,
	[HpManagerSalary] [money] NULL ,
	[HpClerkSalary] [money] NULL ,
	[HpIMaxSell] [smallint] NULL ,
	[HpIMaxBuy] [smallint] NULL ,
	[HpStockCheck] [smallint] NULL ,
	[HpStockAutoFlag] [smallint] NULL ,
	[HpVoteCheck] [smallint] NULL ,
	[HpVoteTitle1] [varchar] (50) NULL ,
	[HpVoteTitle2] [varchar] (50) NULL ,
	[HpVoteTitle3] [varchar] (50) NULL ,
	[HpVoteTitle4] [varchar] (50) NULL ,
	[HpVoteTitle5] [varchar] (50) NULL ,
	[HpNewItem] [varchar] (2) NULL ,
	[PBankLoanLimit] [money] NULL ,
	[CBankLoanLimit] [money] NULL ,
	[CCreditFee] [money] NULL ,
	[CCreditRate] [money] NULL ,
	[CCreditLimit] [money] NULL ,
	[PCreditLimit] [money] NULL ,
	[XCreditActFee] [money] NULL ,
	[XSecActFee] [money] NULL ,
	[XSecRate] [real] NULL ,
	[XSecTax] [money] NULL ,
	[PDealerDiscAmnt] [money] NULL ,
	[PDealerDiscRate] [real] NULL ,
	[CDealerDiscAmnt] [money] NULL ,
	[CDealerDiscRate] [real] NULL ,
	[PMakerDiscAmnt] [money] NULL ,
	[PMakerDiscRate] [real] NULL ,
	[PMakerMakeRate] [real] NULL ,
	[PMakerCostRate] [real] NULL ,
	[PMakerSellRate] [real] NULL ,
	[CMakerDiscAmnt] [money] NULL ,
	[CMakerDiscRate] [real] NULL ,
	[CMakerMakeRate] [real] NULL ,
	[CMakerCostRate] [real] NULL ,
	[CMakerSellRate] [real] NULL ,
	[XAdFee] [money] NULL ,
	[XAdFee2] [money] NULL ,
	[SearchNGFlag] [smallint] NULL ,
	[POption01] [money] NULL ,
	[POption02] [money] NULL ,
	[POption03] [money] NULL ,
	[POption04] [money] NULL ,
	[POption05] [money] NULL ,
	[AddDate] [datetime] NULL ,
	[UpdDate] [datetime] NULL ,
	[AddProc] [varchar] (20) NULL ,
	[UpdProc] [varchar] (20) NULL ,
	[MallCount] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ctpTownT] (
	[PracticeCode] [varchar] (8) NOT NULL ,
	[TownNo] [int] NOT NULL ,
	[TownName] [varchar] (40) NULL ,
	[NumberOfShop] [tinyint] NULL ,
	[BgImage] [varchar] (100) NULL ,
	[TramImage] [varchar] (100) NULL ,
	[RoadImage] [varchar] (100) NULL ,
	[ShopImage0] [varchar] (100) NULL ,
	[ShopImage1] [varchar] (100) NULL ,
	[ShopImage2] [varchar] (100) NULL ,
	[ShopImage3] [varchar] (100) NULL ,
	[ShopImage4] [varchar] (100) NULL ,
	[ShopImage5] [varchar] (100) NULL ,
	[HpVoteTitle1] [varchar] (50) NULL ,
	[HpVoteTitle2] [varchar] (50) NULL ,
	[HpVoteTitle3] [varchar] (50) NULL ,
	[HpVoteTitle4] [varchar] (50) NULL ,
	[HpVoteTitle5] [varchar] (50) NULL ,
	[AddDate] [datetime] NULL ,
	[UpdDate] [datetime] NULL ,
	[AddProc] [varchar] (20) NULL ,
	[UpdProc] [varchar] (20) NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[cthPTenantM] WITH NOCHECK ADD 
	CONSTRAINT [PK_cthPTenantM] PRIMARY KEY  CLUSTERED 
	(
		[PracticeCode],
		[TownNo],
		[TownSeq]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ctpPracticeM] WITH NOCHECK ADD 
	CONSTRAINT [PK_ctpPracticeM] PRIMARY KEY  CLUSTERED 
	(
		[PracticeCode]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ctpTownT] WITH NOCHECK ADD 
	CONSTRAINT [PK_ctpTownT] PRIMARY KEY  CLUSTERED 
	(
		[PracticeCode],
		[TownNo]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[cthPTenantM] ADD 
	CONSTRAINT [DF_cthPTenantM_TownNo] DEFAULT (0) FOR [TownNo],
	CONSTRAINT [DF_cthPTenantM_TownSeq] DEFAULT (0) FOR [TownSeq],
	CONSTRAINT [DF_cthPTenantM_CountVisit] DEFAULT (0) FOR [CountVisit],
	CONSTRAINT [DF_cthPTenantM_CountSells] DEFAULT (0) FOR [CountSells],
	CONSTRAINT [DF_cthPTenantM_ConsumptionTax] DEFAULT (0) FOR [ConsumptionTax],
	CONSTRAINT [DF_cthPTenantM_TransportFlag] DEFAULT (0) FOR [TransportFlag],
	CONSTRAINT [DF_cthPTenantM_ShopDiscAmnt] DEFAULT (0) FOR [ShopDiscAmnt],
	CONSTRAINT [DF_cthPTenantM_ShopDiscRate] DEFAULT (0) FOR [ShopDiscRate],
	CONSTRAINT [DF_cthPTenantM_CloseFlag] DEFAULT (0) FOR [CloseFlag],
	CONSTRAINT [DF_cthPTenantM_ActionFlag] DEFAULT (1) FOR [ActionFlag]
GO

ALTER TABLE [dbo].[ctpPracticeM] ADD 
	CONSTRAINT [DF_ctpPracticeM_PracticeSeq] DEFAULT (0) FOR [PracticeSeq],
	CONSTRAINT [DF_ctpPracticeM_UnusedFlag] DEFAULT (0) FOR [UnusedFlag],
	CONSTRAINT [DF_ctpPracticeM_TCloseFlag] DEFAULT (0) FOR [TCloseFlag],
	CONSTRAINT [DF_ctpPracticeM_DefaultFlag] DEFAULT (0) FOR [DefaultFlag],
	CONSTRAINT [DF_ctpPracticeM_Settlement] DEFAULT (0) FOR [Settlement],
	CONSTRAINT [DF_ctpPracticeM_ExRate] DEFAULT (0) FOR [ExRate],
	CONSTRAINT [DF_ctpPracticeM_ExConsumptionTax] DEFAULT (0) FOR [ExConsumptionTax],
	CONSTRAINT [DF_ctpPracticeM_ConsumptionTax] DEFAULT (0) FOR [ConsumptionTax],
	CONSTRAINT [DF_ctpPracticeM_ComputeFlag] DEFAULT (0) FOR [ComputeFlag],
	CONSTRAINT [DF_ctpPracticeM_PCapitalAmount] DEFAULT (0) FOR [PCapitalAmount],
	CONSTRAINT [DF_ctpPracticeM_CCapitalAmount] DEFAULT (0) FOR [CCapitalAmount],
	CONSTRAINT [DF_ctpPracticeM_COfficeGuarantee] DEFAULT (0) FOR [COfficeGuarantee],
	CONSTRAINT [DF_ctpPracticeM_COfficeRentalFee] DEFAULT (0) FOR [COfficeRentalFee],
	CONSTRAINT [DF_ctpPracticeM_COfficeAssetsPrice] DEFAULT (0) FOR [COfficeAssetsPrice],
	CONSTRAINT [DF_ctpPracticeM_HpOpeningCostH] DEFAULT (0) FOR [HpOpeningCostH],
	CONSTRAINT [DF_ctpPracticeM_HpOpeningCostL] DEFAULT (0) FOR [HpOpeningCostL],
	CONSTRAINT [DF_ctpPracticeM_HpRentalFeeH] DEFAULT (0) FOR [HpRentalFeeH],
	CONSTRAINT [DF_ctpPracticeM_HpRentalFeeL] DEFAULT (0) FOR [HpRentalFeeL],
	CONSTRAINT [DF_ctpPracticeM_HpManagerSalary] DEFAULT (0) FOR [HpManagerSalary],
	CONSTRAINT [DF_ctpPracticeM_HpClerkSalary] DEFAULT (0) FOR [HpClerkSalary],
	CONSTRAINT [DF_ctpPracticeM_HpIMaxSell] DEFAULT (0) FOR [HpIMaxSell],
	CONSTRAINT [DF_ctpPracticeM_HpIMaxBuy] DEFAULT (0) FOR [HpIMaxBuy],
	CONSTRAINT [DF_ctpPracticeM_HpStockCheck] DEFAULT (0) FOR [HpStockCheck],
	CONSTRAINT [DF_ctpPracticeM_HpStockAutoFlag] DEFAULT (0) FOR [HpStockAutoFlag],
	CONSTRAINT [DF_ctpPracticeM_HpVoteCheck] DEFAULT (0) FOR [HpVoteCheck],
	CONSTRAINT [DF_ctpPracticeM_PBankLoanLimit] DEFAULT (0) FOR [PBankLoanLimit],
	CONSTRAINT [DF_ctpPracticeM_CBankLoanLimit] DEFAULT (0) FOR [CBankLoanLimit],
	CONSTRAINT [DF_ctpPracticeM_CCreditFee] DEFAULT (0) FOR [CCreditFee],
	CONSTRAINT [DF_ctpPracticeM_CCreditRate] DEFAULT (0) FOR [CCreditRate],
	CONSTRAINT [DF_ctpPracticeM_CCreditLimit] DEFAULT (0) FOR [CCreditLimit],
	CONSTRAINT [DF_ctpPracticeM_PCreditLimit] DEFAULT (0) FOR [PCreditLimit],
	CONSTRAINT [DF_ctpPracticeM_XCreditActFee] DEFAULT (0) FOR [XCreditActFee],
	CONSTRAINT [DF_ctpPracticeM_XSecActFee] DEFAULT (0) FOR [XSecActFee],
	CONSTRAINT [DF_ctpPracticeM_XSecRate] DEFAULT (0) FOR [XSecRate],
	CONSTRAINT [DF_ctpPracticeM_XSecTax] DEFAULT (0) FOR [XSecTax],
	CONSTRAINT [DF_ctpPracticeM_PDealerDiscAmnt] DEFAULT (0) FOR [PDealerDiscAmnt],
	CONSTRAINT [DF_ctpPracticeM_PDealerDiscRate] DEFAULT (0) FOR [PDealerDiscRate],
	CONSTRAINT [DF_ctpPracticeM_CDealerDiscAmnt] DEFAULT (0) FOR [CDealerDiscAmnt],
	CONSTRAINT [DF_ctpPracticeM_CDealerDiscRate] DEFAULT (0) FOR [CDealerDiscRate],
	CONSTRAINT [DF_ctpPracticeM_PMakerDiscAmnt] DEFAULT (0) FOR [PMakerDiscAmnt],
	CONSTRAINT [DF_ctpPracticeM_PMakerDiscRate] DEFAULT (0) FOR [PMakerDiscRate],
	CONSTRAINT [DF_ctpPracticeM_PMakerMakeRate] DEFAULT (0) FOR [PMakerMakeRate],
	CONSTRAINT [DF_ctpPracticeM_PMakerCostRate] DEFAULT (0) FOR [PMakerCostRate],
	CONSTRAINT [DF_ctpPracticeM_PMakerSellRate] DEFAULT (0) FOR [PMakerSellRate],
	CONSTRAINT [DF_ctpPracticeM_CMakerDiscAmnt] DEFAULT (0) FOR [CMakerDiscAmnt],
	CONSTRAINT [DF_ctpPracticeM_CMakerDiscRate] DEFAULT (0) FOR [CMakerDiscRate],
	CONSTRAINT [DF_ctpPracticeM_CMakerMakeRate] DEFAULT (0) FOR [CMakerMakeRate],
	CONSTRAINT [DF_ctpPracticeM_CMakerCostRate] DEFAULT (0) FOR [CMakerCostRate],
	CONSTRAINT [DF_ctpPracticeM_CMakerSellRate] DEFAULT (0) FOR [CMakerSellRate],
	CONSTRAINT [DF_ctpPracticeM_XAdFee] DEFAULT (0) FOR [XAdFee],
	CONSTRAINT [DF_ctpPracticeM_XAdFee2] DEFAULT (0) FOR [XAdFee2],
	CONSTRAINT [DF_ctpPracticeM_SearchNGFlag] DEFAULT (0) FOR [SearchNGFlag],
	CONSTRAINT [DF_ctpPracticeM_POption01] DEFAULT (0) FOR [POption01],
	CONSTRAINT [DF_ctpPracticeM_POption02] DEFAULT (0) FOR [POption02],
	CONSTRAINT [DF_ctpPracticeM_POption03] DEFAULT (0) FOR [POption03],
	CONSTRAINT [DF_ctpPracticeM_POption04] DEFAULT (0) FOR [POption04],
	CONSTRAINT [DF_ctpPracticeM_POption05] DEFAULT (0) FOR [POption05]
GO

ALTER TABLE [dbo].[ctpTownT] ADD 
	CONSTRAINT [DF_ctpTownT_TownNo] DEFAULT (0) FOR [TownNo],
	CONSTRAINT [DF_ctpTownT_NumberOfShop] DEFAULT (0) FOR [NumberOfShop]
GO