--แสดง index ไม่จำเป็นต้อง run
--DBCC SHOWCONTIG(PRT_Payment)

--สำหรับ Personal หน้า ส่งออก Excel
if not exists(SELECT * FROM  sys.indexes where name='IX_PNT_PersonSearch')CREATE NONCLUSTERED INDEX [IX_PNT_PersonSearch] ON [dbo].[PNT_Person]([CompanyID] ,PassDate,sexid,[ChkDeletePerson] ,ResignStatus)INCLUDE ( [FnameT],PersonID,PersonCode,PersonCardID,InitialID,Cmb1ID,Cmb2ID,Cmb3ID,Cmb4ID,Cmb5ID,Cmb6ID,PositionID,EmpTypeID,ContractID,ContractExpire,ContractBegin,BirthDate,StartDate,EndDate,TaxID,SSFID,ContactNo,IdentityID,LevelID,CurrentAddress,CurrentBuilding,CurrentSoi,CurrentRoad,CurrentDistric,CurrentAmphur,CurrentProvince,CurrentPostID,Salary,JGID,commanderID,[LnameT],FnameE,LnameE)
if not exists(SELECT * FROM  sys.indexes where name='IX_PNT_EducationSearch')CREATE NONCLUSTERED INDEX [IX_PNT_EducationSearch] ON [dbo].[PNT_Education](personid)INCLUDE ( EducateLevel,DegreeID,MajorID,EducateGrade,InstituteID)


if not exists(SELECT * FROM  sys.indexes where name='IX_PR_ConfigIncome')CREATE NONCLUSTERED INDEX [IX_PR_ConfigIncome] ON [dbo].[PR_ConfigIncome]([ID_Company] ASC,[IncomeType] ASC,[IncomeAuto] ASC)


if exists(SELECT * FROM  sys.indexes where name='IX_TAP_LeaveForm')drop index TAP_LeaveForm.IX_TAP_LeaveForm;
if not exists(SELECT * FROM  sys.indexes where name='IX_TAP_LeaveForm_1')CREATE NONCLUSTERED INDEX [IX_TAP_LeaveForm_1] ON [dbo].[TAP_LeaveForm] ([PersonID] ASC,[Leave_DateS] ASC,[Leave_DateE] ASC,[StatusDel] ASC,[Approve] ASC)


if not exists(SELECT * FROM  sys.indexes where name='IX_TAP_ChangHoliday ')CREATE NONCLUSTERED INDEX [IX_TAP_ChangHoliday ] ON [dbo].[TAP_ChangHoliday] ([PersonID] ASC,DateToChang ASC)




if exists(SELECT * FROM  sys.indexes where name='IX_TAP_OTNormal')drop index tap_otnormal.IX_TAP_OTNormal;
if exists(SELECT * FROM  sys.indexes where name='IX_TAP_OTNormal_1')drop index tap_otnormal.IX_TAP_OTNormal_1;
if not exists(SELECT * FROM  sys.indexes where name='IX_TAP_OTNormal_2')CREATE NONCLUSTERED INDEX [IX_TAP_OTNormal_2] ON [dbo].[TAP_OTNormal]([PersonID] ASC,[TypeOT] ASC,[OT_Date] ASC,[Approve] ASC,[StatusDel] ASC,data_save asc) 


if exists(SELECT * FROM  sys.indexes where name='IX_TAM_TimeDay')drop index tam_timeday.IX_TAM_TimeDay; 
if exists(SELECT * FROM  sys.indexes where name='IX_TAM_TimeDay_1')drop index tam_timeday.IX_TAM_TimeDay_1; 
if not exists(SELECT * FROM  sys.indexes where name='IX_TAM_TimeDay_PersonID_WorkDay')CREATE NONCLUSTERED INDEX [IX_TAM_TimeDay_PersonID_WorkDay] ON [dbo].[TAM_TimeDay]([PersonID] ASC,[WorkDay] ASC,No_ofShift asc)INCLUDE ( [ID_TimeDay],[ckMoveOTTDK],shiftcode,timein,timeout) 

if exists(SELECT * FROM  sys.indexes where name='IX_PNT_TranferPerson_PersonID_Head_Date')drop index PNT_TranferPerson.IX_PNT_TranferPerson_PersonID_Head_Date; 
if not exists(SELECT * FROM  sys.indexes where name='IX_PNT_TranferPerson')CREATE NONCLUSTERED INDEX [IX_PNT_TranferPerson] ON [dbo].PNT_TranferPerson(TranferDate ASC,PersonID ASC,TranferHead ASC) 



--ลบ TAP_TimeInOut_Approve pk ที่ติดตัวเลข และสร้างใหม่
 if exists(SELECT name FROM sysobjects WHERE    xtype = 'PK' AND parent_obj = OBJECT_ID('TAP_TimeInOut_Approve') and name LIKE '%[0-9]%') begin DECLARE @SQL VARCHAR(4000);SET @SQL = 'ALTER TABLE TAP_TimeInOut_Approve DROP CONSTRAINT |ConstraintName| ';SET @SQL = REPLACE(@SQL, '|ConstraintName|', ( SELECT   name FROM     sysobjects WHERE    xtype = 'PK' AND parent_obj = OBJECT_ID('TAP_TimeInOut_Approve')));EXEC (@SQL);ALTER TABLE dbo.TAP_TimeInOut_Approve ADD CONSTRAINT PK_TAP_TimeInOut_Approve PRIMARY KEY CLUSTERED (ID_TimeInOut) ON [PRIMARY]; end

if exists(SELECT * FROM  sys.indexes where name='prt_payment')drop index prt_payment.ix_prt_payment; --บรรทัดนี้ error ไม่เป็นไร
--go



ALTER INDEX IX_PR_ConfigIncome ON PR_ConfigIncome REBUILD 

ALTER INDEX IX_TAP_ChangHoliday ON TAP_ChangHoliday REBUILD 
ALTER INDEX PK_TAP_ChangHoliday ON TAP_ChangHoliday REBUILD 

ALTER INDEX PK_TAP_HolidayYear_Emp ON TAP_HolidayYear_Emp REBUILD 

ALTER INDEX IX_PNT_EducationSearch ON PNT_Education REBUILD 

ALTER INDEX IX_PNT_PersonSearch ON PNT_Person REBUILD 

ALTER INDEX [PK_PRT_Payment] ON [PRT_Payment] REBUILD 

ALTER INDEX [PK_TAM_Work] ON [TAM_Work] REBUILD;

ALTER INDEX IX_PNT_TranferPerson ON PNT_TranferPerson REBUILD;

ALTER INDEX [PK_TAM_ShifttemplateDetail] ON [TAM_ShifttemplateDetail] REBUILD 
ALTER INDEX [IX_TAM_ShifttemplateDetail] ON [TAM_ShifttemplateDetail] REBUILD 

ALTER INDEX [PK_TAM_Shift] ON [TAM_Shift] REBUILD;

ALTER INDEX [PK_TAM_TimeDay] ON [TAM_TimeDay] REBUILD 
ALTER INDEX [IX_TAM_TimeDay_PersonID_WorkDay] ON [TAM_TimeDay] REBUILD 

ALTER INDEX [PK_TAM_TimeDay_tmp] ON [TAM_TimeDay_tmp] REBUILD 

ALTER INDEX [PK_TAP_DataTime] ON [TAP_DataTime] REBUILD 

ALTER INDEX [PK_TAP_Diligent] ON [TAP_Diligent] REBUILD 
ALTER INDEX [IX_TAP_Diligent] ON [TAP_Diligent] REBUILD 


ALTER INDEX [PK_TAP_LeaveForm] ON [TAP_LeaveForm] REBUILD 
ALTER INDEX [IX_TAP_LeaveForm_1] ON [TAP_LeaveForm] REBUILD 

ALTER INDEX [PK_TAP_LogFile] ON [TAP_LogFile] REBUILD 

ALTER INDEX [PK_TAP_OTAuto] ON [TAP_OTAuto] REBUILD 


ALTER INDEX [PK_TAP_OTNormal] ON [TAP_OTNormal] REBUILD 
ALTER INDEX [IX_TAP_OTNormal_2] ON [TAP_OTNormal] REBUILD 

ALTER INDEX [PK_TAP_OTSpecial] ON [TAP_OTSpecial] REBUILD 
ALTER INDEX [IX_TAP_OTSpecial] ON [TAP_OTSpecial] REBUILD 

ALTER INDEX [PK_TAP_SUMMER] ON [TAP_SUMMER] REBUILD 

if not exists(SELECT * FROM  sys.indexes where name='IX_TAP_TimeInOut2') CREATE NONCLUSTERED INDEX IX_TAP_TimeInOut2 ON dbo.TAP_TimeInOut ( ID_TimeInOut_Approve );
ALTER INDEX [PK_TAP_TimeInOut] ON [TAP_TimeInOut] REBUILD 
ALTER INDEX [IX_TAP_TimeInOut] ON [TAP_TimeInOut] REBUILD 
ALTER INDEX [IX_TAP_TimeInOut2] ON [TAP_TimeInOut] REBUILD 



if exists(SELECT * FROM  sys.indexes where name='tap_timeinout_approve') DROP INDEX tap_timeinout_approve ON dbo.tap_timeinout_approve;
if not exists(SELECT * FROM  sys.indexes where name='IX_TAP_TimeInOut_Approve') CREATE NONCLUSTERED INDEX [IX_TAP_TimeInOut_Approve] ON [dbo].[TAP_TimeInOut_Approve]([ID_Card] ASC,[StatusDel] ASC,[ID_SInOut] ASC,[DateI] ASC);
ALTER INDEX [PK_TAP_TimeInOut_Approve] ON [TAP_TimeInOut_Approve] REBUILD 
ALTER INDEX [IX_TAP_TimeInOut_Approve] ON [TAP_TimeInOut_Approve] REBUILD 





ALTER INDEX [PK_TAP_TimeSheet_1] ON [TAP_TimeSheet] REBUILD 
ALTER INDEX [IX_TAP_TimeSheet] ON [TAP_TimeSheet] REBUILD 

ALTER INDEX [PK_TAP_TmpSummerDoloson] ON [TAP_TmpSummerDoloson] REBUILD 

ALTER INDEX [PK_TAT_R_Date] ON [TAT_R_Date] REBUILD 

ALTER INDEX [PK_Tmp] ON [Tmp_Report] REBUILD 

ALTER INDEX [PK_TMP_DocEV] ON [TMP_DocEV] REBUILD 

ALTER INDEX [PK_Tmp_Shift] ON [Tmp_Shift] REBUILD 

ALTER INDEX [PK_Tmp_Shift_Time1_48] ON [Tmp_Shift_Time1_48] REBUILD 


------------------------------------------------------

ALTER INDEX [IX_PRT_TimeSheet] ON [PRT_TimeSheet] REBUILD 

ALTER INDEX [PK_PRT_TimeSheet_1] ON [PRT_TimeSheet] REBUILD 

ALTER INDEX [PK_PRT_TmpReportTax] ON [PRT_TmpReportTax] REBUILD 

ALTER INDEX [PK_PRT_TmpReportSSF] ON [PRT_TmpReportSSF] REBUILD 

ALTER INDEX [PK_PRT_TmpTax] ON [PRT_TmpTax] REBUILD 

ALTER INDEX [PK_PRT_TmpTax91] ON [PRT_TmpTax91] REBUILD 

ALTER INDEX [PK_tmpSumYTD] ON [tmpSumYTD] REBUILD 

-------------------
ALTER INDEX [PK_PNT_Person] ON [PNT_Person] REBUILD 
ALTER INDEX IX_PNT_Person_Cmb ON [PNT_Person] REBUILD 

ALTER INDEX [PK_PNT_PersonDetail] ON [PNT_PersonDetail] REBUILD 

ALTER INDEX [PK_PNT_PersonOther] ON [PNT_PersonOther] REBUILD 
ALTER INDEX [ix_personid] ON [PNT_PersonOther] REBUILD 

ALTER INDEX [PK_PNT_Resign] ON [PNT_Resign] REBUILD 
ALTER INDEX [IX_PNT_Resign_PersonID] ON [PNT_Resign] REBUILD 

ALTER INDEX [PK_PNT_TranferPerson] ON [PNT_TranferPerson] REBUILD 

ALTER INDEX [PK_PNT_Welfare] ON [PNT_Welfare] REBUILD 

ALTER INDEX [PK_PNT_ChangeSalary] ON [PNT_ChangeSalary] REBUILD 

ALTER INDEX [PK_TAP_WorkPigeon] ON [TAP_WorkPigeon] REBUILD

ALTER INDEX [PK_Tmp_ReportSTI_1] ON [Tmp_ReportSTI] REBUILD
-----------------------------------------
ALTER INDEX [PK_EVP_DetailEvolution] ON [EVP_DetailEvolution] REBUILD

ALTER INDEX [PK_EVP_MainEvolution] ON [EVP_MainEvolution] REBUILD

ALTER INDEX [PK_EVP_MainEvolutionBySeft] ON [EVP_MainEvolutionBySeft] REBUILD

----------------------------
ALTER INDEX [PK_TAP_TranferCost] ON [TAP_TranferCost] REBUILD
ALTER INDEX [PK_TAP_TranferCost_1] ON [TAP_TranferCost] REBUILD
ALTER INDEX [IX_PRT_Amount] ON [PRT_Amount] REBUILD 
ALTER INDEX [PK_PRT_Amount] ON [PRT_Amount] REBUILD 
ALTER INDEX [PK_PRT_AmountFix] ON [PRT_AmountFix] REBUILD 
ALTER INDEX [PK_PRT_AmountFix_Log] ON [PRT_AmountFix_Log] REBUILD 

ALTER INDEX PK_PNT_Education ON PNT_Education  REBUILD 
ALTER INDEX  PK_PNM_Degree ON  PNM_Degree REBUILD 
ALTER INDEX PK_PNM_Major ON PNM_Major  REBUILD 
ALTER INDEX PK_PNM_Institute ON PNM_Institute  REBUILD 
ALTER INDEX PK_PNM_Amphur ON PNM_Amphur  REBUILD 
ALTER INDEX PK_PNM_Initial ON PNM_Initial  REBUILD 
ALTER INDEX PK_PNM_Cmb1 ON   PNM_Cmb1 REBUILD 
ALTER INDEX PK_PNM_Cmb2 ON   PNM_Cmb2 REBUILD 
ALTER INDEX PK_PNM_Cmb3 ON   PNM_Cmb3 REBUILD 
ALTER INDEX PK_PNM_Cmb4 ON   PNM_Cmb4 REBUILD 
ALTER INDEX PK_PNM_Cmb5 ON   PNM_Cmb5 REBUILD 
ALTER INDEX PK_PNM_Cmb6 ON   PNM_Cmb6 REBUILD 
ALTER INDEX PK_PNM_Contract ON  PNM_Contract REBUILD 
ALTER INDEX  PK_PNM_District ON   PNM_District REBUILD 
ALTER INDEX PK_PNM_Sex ON  PNM_Sex REBUILD 
ALTER INDEX PK_PNM_EmpType ON  PNM_EmpType REBUILD 
ALTER INDEX PK_PNM_Position ON PNM_Position  REBUILD 
ALTER INDEX PK_PNM_Level ON  PNM_Level REBUILD 
ALTER INDEX PK_PNM_Nationality ON PNM_Nationality  REBUILD 
ALTER INDEX PK_COM_Company ON COM_Company  REBUILD 
ALTER INDEX PK_PNM_Institute ON PNM_Institute  REBUILD 
ALTER INDEX PK_PNM_JG ON PNM_JG  REBUILD 
ALTER INDEX PK_PNM_ResignType ON PNM_ResignType  REBUILD 
ALTER INDEX PK_PNM_MResign ON PNM_MResign  REBUILD 
ALTER INDEX PK_PRT_DataPay ON PRT_DataPay  REBUILD 
ALTER INDEX IX_PRT_DataPay_PersonID ON PRT_DataPay  REBUILD 
ALTER INDEX PK_PNM_Province ON PNM_Province  REBUILD 
ALTER INDEX PK_PNM_Zone ON PNM_Zone  REBUILD 
ALTER INDEX PK_PNM_Marital ON  PNM_Marital REBUILD 
ALTER INDEX  PK_PRM_BankName ON PRM_BankName REBUILD 
ALTER INDEX  PK_PRM_BranchBank ON PRM_BranchBank REBUILD 
ALTER INDEX PK_PNM_Citizen  ON PNM_Citizen REBUILD 
ALTER INDEX PK_PNM_Religion  ON PNM_Religion REBUILD 
ALTER INDEX PK_PNM_Blood  ON PNM_Blood REBUILD 
ALTER INDEX  PK_PNM_Military ON PNM_Military REBUILD