if not( exists (SELECT * FROM syscolumns WHERE OBJECT_NAME(id) = 'ctpPracticeM' AND name = N'MasterID')) ALTER TABLE ctpPracticeM ADD MasterID varchar(12) NULL GO if not( exists (SELECT * FROM syscolumns WHERE OBJECT_NAME(id) = 'ctpPracticeM' AND name = N'ManageFlag')) BEGIN CREATE TABLE [dbo].[ctpPracticeMNew] ( [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 , [ManageFlag] [smallint] NULL , [MasterPwd] [varchar] (8) NULL , [Remarks] [varchar] (250) NULL , [BgImage] [varchar] (100) 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 , [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 , [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 ) INSERT INTO ctpPracticeMNew (PracticeType, PracticeCode, PracticeSeq, PracticeName, UnusedFlag, TCloseFlag, DefaultFlag, MasterPwd, Remarks, StartDate, EndDate, NowDate, Settlement, ExCode, ExRate, ExConsumptionTax, ConsumptionTax, ComputeFlag, PCapitalAmount, CCapitalAmount, COfficeGuarantee, COfficeRentalFee, COfficeAssetsPrice, HpOpeningCostH, HpOpeningCostL, HpRentalFeeH, HpRentalFeeL, HpManagerSalary, HpClerkSalary, HpIMaxSell, HpIMaxBuy, HpStockCheck, HpStockAutoFlag, HpVoteCheck, HpVoteTitle1, HpVoteTitle2, HpVoteTitle3, HpVoteTitle4, HpVoteTitle5, PBankLoanLimit, CBankLoanLimit, CCreditFee, CCreditRate, CCreditLimit, PCreditLimit, XCreditActFee, XSecActFee, XSecRate, XSecTax, PDealerDiscAmnt, PDealerDiscRate, CDealerDiscAmnt, CDealerDiscRate, PMakerDiscAmnt, PMakerDiscRate, PMakerMakeRate, PMakerCostRate, PMakerSellRate, CMakerDiscAmnt, CMakerDiscRate, CMakerMakeRate, CMakerCostRate, CMakerSellRate, XAdFee, XAdFee2, SearchNGFlag, POption01, POption02, POption03, POption04, POption05, AddDate, UpdDate, AddProc, UpdProc) SELECT PracticeType, PracticeCode, PracticeSeq, PracticeName, UnusedFlag, TCloseFlag, DefaultFlag, MasterPwd, Remarks, StartDate, EndDate, NowDate, Settlement, ExCode, ExRate, ExConsumptionTax, ConsumptionTax, ComputeFlag, PCapitalAmount, CCapitalAmount, COfficeGuarantee, COfficeRentalFee, COfficeAssetsPrice, HpOpeningCostH, HpOpeningCostL, HpRentalFeeH, HpRentalFeeL, HpManagerSalary, HpClerkSalary, HpIMaxSell, HpIMaxBuy, HpStockCheck, HpStockAutoFlag, HpVoteCheck, HpVoteTitle1, HpVoteTitle2, HpVoteTitle3, HpVoteTitle4, HpVoteTitle5, PBankLoanLimit, CBankLoanLimit, CCreditFee, CCreditRate, CCreditLimit, PCreditLimit, XCreditActFee, XSecActFee, XSecRate, XSecTax, PDealerDiscAmnt, PDealerDiscRate, CDealerDiscAmnt, CDealerDiscRate, PMakerDiscAmnt, PMakerDiscRate, PMakerMakeRate, PMakerCostRate, PMakerSellRate, CMakerDiscAmnt, CMakerDiscRate, CMakerMakeRate, CMakerCostRate, CMakerSellRate, XAdFee, XAdFee2, SearchNGFlag, POption01, POption02, POption03, POption04, POption05, AddDate, UpdDate, AddProc, UpdProc FROM ctpPracticeM UPDATE ctpPracticeMNew SET ManageFlag = 0 drop table [dbo].[ctpPracticeM] EXEC sp_rename 'ctpPracticeMNew', 'ctpPracticeM' ALTER TABLE [dbo].[ctpPracticeM] WITH NOCHECK ADD CONSTRAINT [PK_ctpPracticeM] PRIMARY KEY CLUSTERED ( [PracticeCode] ) ALTER TABLE [dbo].[ctpPracticeM] WITH NOCHECK 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_ManageFlag] DEFAULT (0) FOR [ManageFlag], 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] END if not( exists (SELECT * FROM syscolumns WHERE OBJECT_NAME(id) = 'ctpTradeTrn' AND name = N'ItemName')) BEGIN CREATE TABLE [dbo].[ctpTradeTrnNew] ( [PracticeCode] [varchar] (8) NOT NULL , [APrivateCode] [varchar] (12) NOT NULL , [ACompanyCode] [varchar] (12) NOT NULL , [ATenantCode] [varchar] (12) NOT NULL , [ALoginId] [varchar] (12) NULL , [ActNo] [int] NULL , [TradeCode] [varchar] (4) NOT NULL , [OrderNo] [int] NOT NULL , [OrderSeq] [smallint] NOT NULL , [BPrivateCode] [varchar] (12) NULL , [BCompanyCode] [varchar] (12) NULL , [BTenantCode] [varchar] (12) NULL , [ItemType] [varchar] (1) NULL , [ItemCode] [varchar] (4) NULL , [ItemName] [varchar] (40) NULL , [ItemImage] [varchar] (100) NULL , [PaymentTerm] [smallint] NULL , [ProcessStatus] [varchar] (1) NULL , [BusinessDateA] [datetime] NULL , [BusinessDateB] [datetime] NULL , [BusinessDateE] [datetime] NULL , [BusinessDateT] [datetime] NULL , [TransportFlag] [int] NULL , [SellsDiscFlag] [int] NULL , [COfficeAssetsFlag] [int] NULL , [Qty] [int] NULL , [StandardPrice] [money] NULL , [MakesPrice] [money] NULL , [StockPrice] [money] NULL , [SellsPrice] [money] NULL , [TransportPrice] [money] NULL , [ConsumptionTax] [money] NULL , [CCreditRate] [real] NULL , [MakesAmnt] [money] NULL , [StockAmnt] [money] NULL , [SellsAmnt] [money] NULL , [TaxAmnt] [money] NULL , [TransportAmnt] [money] NULL , [TransportTax] [money] NULL , [CreditAmnt] [money] NULL , [AAmnt] [money] NULL , [BAmnt] [money] NULL , [RemarksA] [varchar] (250) NULL , [RemarksB] [varchar] (250) NULL , [RemarksE] [varchar] (250) NULL , [AddDate] [datetime] NOT NULL , [UpdDate] [datetime] NULL , [AddProc] [varchar] (20) NULL , [UpdProc] [varchar] (20) NULL ) INSERT INTO ctpTradeTrnNew (PracticeCode, APrivateCode, ACompanyCode, ATenantCode, ALoginId, ActNo, TradeCode, OrderNo, OrderSeq, BPrivateCode, BCompanyCode, BTenantCode, ItemType, ItemCode, ItemImage, PaymentTerm, ProcessStatus, BusinessDateA, BusinessDateB, BusinessDateE, BusinessDateT, TransportFlag, SellsDiscFlag, COfficeAssetsFlag, Qty, StandardPrice, MakesPrice, StockPrice, SellsPrice, TransportPrice, ConsumptionTax, CCreditRate, MakesAmnt, StockAmnt, SellsAmnt, TaxAmnt, TransportAmnt, TransportTax, CreditAmnt, AAmnt, BAmnt, RemarksA, RemarksB, RemarksE, AddDate, UpdDate, AddProc, UpdProc) SELECT PracticeCode, APrivateCode, ACompanyCode, ATenantCode, ALoginId, ActNo, TradeCode, OrderNo, OrderSeq, BPrivateCode, BCompanyCode, BTenantCode, ItemType, ItemCode, ItemImage, PaymentTerm, ProcessStatus, BusinessDateA, BusinessDateB, BusinessDateE, BusinessDateT, TransportFlag, SellsDiscFlag, COfficeAssetsFlag, Qty, StandardPrice, MakesPrice, StockPrice, SellsPrice, TransportPrice, ConsumptionTax, CCreditRate, MakesAmnt, StockAmnt, SellsAmnt, TaxAmnt, TransportAmnt, TransportTax, CreditAmnt, AAmnt, BAmnt, RemarksA, RemarksB, RemarksE, AddDate, UpdDate, AddProc, UpdProc FROM ctpTradeTrn drop table [dbo].[ctpTradeTrn] EXEC sp_rename 'ctpTradeTrnNew', 'ctpTradeTrn' ALTER TABLE [dbo].[ctpTradeTrn] WITH NOCHECK ADD CONSTRAINT [PK_ctpTradeTrn] PRIMARY KEY CLUSTERED ( [PracticeCode], [APrivateCode], [ACompanyCode], [ATenantCode], [TradeCode], [OrderNo], [OrderSeq], [AddDate] ) ALTER TABLE [dbo].[ctpTradeTrn] WITH NOCHECK ADD CONSTRAINT [DF_ctpTradeTrn_APrivateCode] DEFAULT ('') FOR [APrivateCode], CONSTRAINT [DF_ctpTradeTrn_ACompanyCode] DEFAULT ('') FOR [ACompanyCode], CONSTRAINT [DF_ctpTradeTrn_ATenantCode] DEFAULT ('') FOR [ATenantCode], CONSTRAINT [DF_ctpTradeTrn_ActNo] DEFAULT (0) FOR [ActNo], CONSTRAINT [DF_ctpTradeTrn_OrderNo] DEFAULT (0) FOR [OrderNo], CONSTRAINT [DF_ctpTradeTrn_OrderSeq] DEFAULT (0) FOR [OrderSeq], CONSTRAINT [DF_ctpTradeTrn_BPrivateCode] DEFAULT ('') FOR [BPrivateCode], CONSTRAINT [DF_ctpTradeTrn_BCompanyCode] DEFAULT ('') FOR [BCompanyCode], CONSTRAINT [DF_ctpTradeTrn_BTenantCode] DEFAULT ('') FOR [BTenantCode], CONSTRAINT [DF_ctpTradeTrn_PaymentTerm] DEFAULT (0) FOR [PaymentTerm], CONSTRAINT [DF_ctpTradeTrn_TransportFlag] DEFAULT (0) FOR [TransportFlag], CONSTRAINT [DF_ctpTradeTrn_SellsDiscFlag] DEFAULT (0) FOR [SellsDiscFlag], CONSTRAINT [DF_ctpTradeTrn_COfficeAssetsFlag] DEFAULT (0) FOR [COfficeAssetsFlag], CONSTRAINT [DF_ctpTradeTrn_Qty] DEFAULT (0) FOR [Qty], CONSTRAINT [DF_ctpTradeTrn_StandardPrice] DEFAULT (0) FOR [StandardPrice], CONSTRAINT [DF_ctpTradeTrn_MakesPrice] DEFAULT (0) FOR [MakesPrice], CONSTRAINT [DF_ctpTradeTrn_StockPrice] DEFAULT (0) FOR [StockPrice], CONSTRAINT [DF_ctpTradeTrn_SellsPrice] DEFAULT (0) FOR [SellsPrice], CONSTRAINT [DF_ctpTradeTrn_TransportPrice] DEFAULT (0) FOR [TransportPrice], CONSTRAINT [DF_ctpTradeTrn_ConsumptionTax] DEFAULT (0) FOR [ConsumptionTax], CONSTRAINT [DF_ctpTradeTrn_CCreditRate] DEFAULT (0) FOR [CCreditRate], CONSTRAINT [DF_ctpTradeTrn_MakesAmnt] DEFAULT (0) FOR [MakesAmnt], CONSTRAINT [DF_ctpTradeTrn_StockAmnt] DEFAULT (0) FOR [StockAmnt], CONSTRAINT [DF_ctpTradeTrn_SellsAmnt] DEFAULT (0) FOR [SellsAmnt], CONSTRAINT [DF_ctpTradeTrn_TaxAmnt] DEFAULT (0) FOR [TaxAmnt], CONSTRAINT [DF_ctpTradeTrn_TransportAmnt] DEFAULT (0) FOR [TransportAmnt], CONSTRAINT [DF_ctpTradeTrn_TransportTax] DEFAULT (0) FOR [TransportTax], CONSTRAINT [DF_ctpTradeTrn_CreditAmnt] DEFAULT (0) FOR [CreditAmnt], CONSTRAINT [DF_ctpTradeTrn_AAmnt] DEFAULT (0) FOR [AAmnt], CONSTRAINT [DF_ctpTradeTrn_BAmnt] DEFAULT (0) FOR [BAmnt] END