Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Table         : [JobControl_ObjectsProcessData_tbl]'
Print ' Creation Date : 22.03.2006'
Print ' Autor         : J.Feuerstein'
Print ' '
Print '-------------------------------------------------------------------------'

use [#DB_N#]


Declare @MyActionMsg1 nvarchar (max)
Declare @MyTableName nvarchar (max)
DECLARE @ExecuteSQLstr nvarchar(max)

set @MyTableName='JobControl_ObjectsProcessData_tbl'

declare @indexNrStr   nvarchar (max)
declare @IndexName    nvarchar (max)

declare @ColumeName nvarchar (max)
declare @ColumeNameOld nvarchar (max)
declare @ColumeNameNew nvarchar (max)
--  declare @ExecuteSQLstr nvarchar (max)

DECLARE @ValueCurr nvarchar(max)
DECLARE @ValueDesired nvarchar(max)
DECLARE @ValueCompareStat int
DECLARE @UpdateDone bit
DECLARE @UpdateCounter int

--------------------------------------------------------------------------------
-- verzweigen auf TableCreate: oder TableUpdate:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[JobControl_ObjectsProcessData_tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  BEGIN
   goto TableUpdate
  END
ELSE
  BEGIN
   goto TableCreate
  END

--------------------------------------------------------------------------------
TableCreate:
  set @MyActionMsg1=
      '-- Table: [JobControl_ObjectsProcessData_tbl] dose not exist and will be created new'
  Print (@MyActionMsg1)

  CREATE TABLE [dbo].[JobControl_ObjectsProcessData_tbl] (
    [Id]                         uniqueidentifier NOT NULL,
    [Objects_tbl_ID]             uniqueidentifier NULL,   -- referenz zu den Ergebniss Daten
    [ProcessPacketName]          nvarchar (64)  , -- speichert den Namen des Prozesspaketes wenn = "" dann ist es ein vom TaskSeduler eingereiter Prozess
    [ProcessState]               nvarchar (34)  , -- 23.11.2011 / J.Fes Size geaendert von 10 auf 32+2=34
    [ProcessTimeStart]           datetime NULL  ,
    [ProcessTimeEnd]             datetime NULL  ,
    [ProcessCMDLine]             nvarchar (255) ,
    [ProcessResultCode]          nvarchar (32)  , -- "ProcessResultCode=0"="Ok" "ProcessResultCode<>0"=Err
    [ProcessResultMsg]           nvarchar (450) , -- Result Message size to 450 on 08.07.2013
    [ProcessResultExtendedData]  nvarchar (max) , -- #Doc: save Extendet Process Result Data new 08.07.2013 evtl in
    [ProcessResultHasWarning]    bit            , -- ProcessResultWarning 1=Warning 0=NoWarning
    [ProcessResultLink]          nvarchar (450) , -- Link für z.B. Verweise auf Dateien Ordnern oder weitern Links (neu 12.07.09)
                                                  -- 08.02.2018/J.Fes length extendet from 255 to 450
    [ProcessOnComputer]          nvarchar (64)  , -- speicher den Namen des Computers auf dem der Job abgearbeitet wurde
    [ProcessProcStateCol]        nvarchar (32)  , -- speichert den Namen der Prozessgruppe die bei der verarbeitung des Jobs in Refferenz steht (neu 21.10.2008 13:32 J.Fes)
    [ProcessPrgUsedVer]          nvarchar (255) , -- Info ueber die beim Process verwendete Program Versionen (#neu 05.04.2013/J.Fes/JobMgr.Issue-25) https://bugnet.addplm.com:85/Issues/IssueDetail.aspx?id=25
    [ProcessObjectSaveVersion]   nvarchar (64)  , -- Object wurde durch diesen Prozess in dieser Program Version gespeichert (#neu 05.04.2013/J.Fes/JobMgr.Issue-25) https://bugnet.addplm.com:85/Issues/IssueDetail.aspx?id=25
                                                  -- #Doc: ist diese Information angegeben so bekommt Objects_tbl.ID(#Objects_tbl_ID#).ObjectSaveVersion diese Daten
    [ProcessCategory]            nvarchar (128) , -- can be used to categorize the process results, for example for different NX Versions  (New 26.11.2021, S.Gueth)

    [Row_isLastRes]              bit ,            -- true dieser Datensatz ist das letzte Ergebniss zu Objects_tbl_ID und ProcessProcStateCol (New 1.2.09)
    [TaskScheduler_tbl_ID]       uniqueidentifier NULL -- Ref auf den Datensatz der TaskScheduler_tbl von dem aus dieser ObjectProcess Datensatz erzeugt wurde Info: der Ref Datensatz beinhaltet alle JobInformationen für diesen Task (neu 26.09.08 J.Fes)
  )
  ON [PRIMARY]
  
  Goto CheckCreateIndex

--------------------------------------------------------------------------------
TableUpdate:

  set @MyActionMsg1=
       '-- Table: [JobControl_ObjectsProcessData_tbl] exist and will be updated'
  Print (@MyActionMsg1)

  -- Ref auf den Datensatz der TaskScheduler_tbl
  --  von dem aus dieser ObjectProcess Datensatz erzeugt wurde Info der Ref Datensatz beinhaltet alle JobInformationen für diesen Task (26.09.08 J.Fes)
  set @ColumeName='TaskScheduler_tbl_ID'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('   |-> Create Column:' + @ColumeName + '  [Rel to TaskSchedul .. (new 26.09.08)]')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [uniqueidentifier] NULL'
      execute (@ExecuteSQLstr)
    end

  -- speichert den Namen der Prozessgruppe die bei der verarbeitung des Jobs in Refferenz steht (neu 21.10.2008 13:32 J.Fes)
  set @ColumeName='ProcessProcStateCol'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('   |-> Create Column:' + @ColumeName + '  [Rel to ProcGrupp .. (new 21.10.08)]')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(32)'
      execute (@ExecuteSQLstr)
    end

  --[IsLastResult]                    bit ,          ,
  -- true dieser Datensatz ist das letzte Ergebniss zu Objects_tbl_ID und ProcessProcStateCol (New 1.2.09)
  set @ColumeName='Row_isLastRes'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('   |-> Create Column:' + @ColumeName + '  [is last result .. (new 01.02.09)]')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] bit'
      execute (@ExecuteSQLstr)
    end

  set @ColumeName='ProcessResultLink'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('   |-> Create Column:' + @ColumeName + '  [Link to Object Process Result.. (new 12.07.09)]')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(255)'
      execute (@ExecuteSQLstr)
    end
    
  set @ColumeName='ProcessResultHasWarning'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('   |-> Create Column:' + @ColumeName + '  [ObjectJobResult has warning?.. (new 12.07.09)]')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] bit'
      execute (@ExecuteSQLstr)
    end

   -- angepasst 23.11.2011 / J.Fes
   set @ColumeName='ProcessState'
   if (Select Col_length(@MyTableName,@ColumeName)) <> 34*2
     BEGIN
      If Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX9')
      BEGIN
        -- Step entfernen Index
        set @ExecuteSQLstr='DROP INDEX [JobControl_ObjectsProcessData_tbl_IDX9]  ON [dbo].[' + @MyTableName + ']'
        print (@ExecuteSQLstr)
        execute sp_executesql @ExecuteSQLstr
      END
      -- Anpassen auf Sice = 34
      set @ExecuteSQLstr=  'ALTER TABLE [dbo].[' + @MyTableName + ']' + ' ALTER COLUMN [' + @ColumeName + '] nvarchar(34) COLLATE Latin1_General_CI_AS'
      print (@ExecuteSQLstr)
      execute sp_executesql @ExecuteSQLstr
     END

  -- new 05.04.2013 J.Fes
  set @ColumeName='ProcessPrgUsedVer'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('   |-> Create Column:' + @ColumeName + '  [Info about Program Versions that as been used during process .. (new 05.04.2013 18:58)]')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(255)'
      execute (@ExecuteSQLstr)
    end

  -- new 05.04.2013 J.Fes
  set @ColumeName='ProcessObjectSaveVersion'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('   |-> Create Column:' + @ColumeName + '  [Info about Save Version of Object (new 05.04.2013)]')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(64)'
      execute (@ExecuteSQLstr)
    end

   -- 08.07.2013 J.Fes Upsize / Resice
   set @ColumeName='ProcessResultMsg'
   if (Select Col_length(@MyTableName,@ColumeName)) <> 450*2
     BEGIN
      print ('   |-> UpSize Column:' + @ColumeName + '  [from 255 to 450 char (Update: 08.07.2013)]')
      -- Die maximale Schlüssellänge beträgt 900 Bytes = Maximale Größe von Indexschlüsseln
      -- wird z.B 512 Byte angegeben so erhält mann die Meldung:
      -- Warnung: Die maximale Schlüssellänge beträgt 900 Bytes. Der JobControl_ObjectsProcessData_tbl_IDX6-Index hat eine
      --       maximale Länge von 1024 Bytes. Bei einigen Kombinationen hoher Werte schlägt der INSERT-/UPDATE-Vorgang fehl.
      If Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX6')
      BEGIN
        -- Step entfernen Index
        set @ExecuteSQLstr='DROP INDEX [JobControl_ObjectsProcessData_tbl_IDX6] ON [dbo].[' + @MyTableName + ']'
        print ('   |-> Step 1/2: ' + @ExecuteSQLstr)
        execute sp_executesql @ExecuteSQLstr
      END
      -- Anpassen auf Sice = 450
      set @ExecuteSQLstr=  'ALTER TABLE [dbo].[' + @MyTableName + ']' + ' ALTER COLUMN [' + @ColumeName + '] nvarchar(450) COLLATE Latin1_General_CI_AS'
      print ('   |-> Step 2/2: ' + @ExecuteSQLstr)
      execute sp_executesql @ExecuteSQLstr
     END


  -- #LUp: 08.07.2013 J.Fes
  set @ColumeName='ProcessResultExtendedData'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('   |-> Create Column:' + @ColumeName + ' save Extendet Process Result .. (new 08.07.2013)')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(max)'
      execute (@ExecuteSQLstr)
    end


  -- #UPd 08.07.2013 J.Fes
  set @ColumeName='ProcessResultLink'
  if (Select Col_length(@MyTableName,@ColumeName)) <> 450*2
     BEGIN
      print ('   |-> UpSize Column:' + @ColumeName + '  [from 255 to 450 char (Update: 08.02.2018/J.Fes)]')
      -- Die maximale Schlüssellänge beträgt 900 Bytes = Maximale Größe von Indexschlüsseln
      -- wird z.B 512 Byte angegeben so erhält mann die Meldung:
      -- Warnung: Die maximale Schlüssellänge beträgt 900 Bytes. Der JobControl_ObjectsProcessData_tbl_IDX6-Index hat eine
      --       maximale Länge von 1024 Bytes. Bei einigen Kombinationen hoher Werte schlägt der INSERT-/UPDATE-Vorgang fehl.
      If Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX13')
      BEGIN
        -- Step entfernen Index
        set @ExecuteSQLstr='DROP INDEX [JobControl_ObjectsProcessData_tbl_IDX13] ON [dbo].[' + @MyTableName + ']'
        print ('   |-> Step 1/2: ' + @ExecuteSQLstr)
        execute sp_executesql @ExecuteSQLstr
      END
      -- Anpassen auf Sice = 450
      set @ExecuteSQLstr=  'ALTER TABLE [dbo].[' + @MyTableName + ']' + ' ALTER COLUMN [' + @ColumeName + '] nvarchar(450) COLLATE Latin1_General_CI_AS'
      print ('   |-> Step 2/2: ' + @ExecuteSQLstr)
      execute sp_executesql @ExecuteSQLstr
     END

   -- can be used to categorize the process results, for example for different NX Versions  (New 26.11.2021, S.Gueth)
  set @ColumeName='ProcessCategory'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('   |-> Create Column:' + @ColumeName + '  [new 26.11.21)]')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(128)'
      execute (@ExecuteSQLstr)
    end

  goto CheckCreateIndex

--------------------------------------------------------------------------------
CheckCreateIndex:


  If NOT Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_PK')
  begin
     print (' --> create index: JobControl_ObjectsProcessData_tbl_PK')
     ALTER TABLE [dbo].[JobControl_ObjectsProcessData_tbl]
       ADD CONSTRAINT [JobControl_ObjectsProcessData_tbl_PK]
       PRIMARY KEY NONCLUSTERED ([Id])
       WITH (
         FILLFACTOR = 80,
         PAD_INDEX = OFF,
         STATISTICS_NORECOMPUTE = OFF,
         ALLOW_ROW_LOCKS = ON,
         ALLOW_PAGE_LOCKS = ON)
  end

  If NOT Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX1')
  begin
    print (' --> create index: JobControl_ObjectsProcessData_tbl_IDX1 --> Objects_tbl_ID')
    CREATE NONCLUSTERED INDEX [JobControl_ObjectsProcessData_tbl_IDX1] ON [dbo].[JobControl_ObjectsProcessData_tbl]
      ([Objects_tbl_ID])
    WITH (
      FILLFACTOR = 80,
      PAD_INDEX = OFF,
      DROP_EXISTING = OFF,
      STATISTICS_NORECOMPUTE = OFF,
      SORT_IN_TEMPDB = OFF,
      ONLINE = OFF,
      ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON)
    ON [PRIMARY]
  end

/*
  -- 01.08.2015/J.Fes removed
  If NOT Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX2')
  begin
   print (' --> create index: JobControl_ObjectsProcessData_tbl_IDX2 --> [ObjectName], [ObjectFromSiteID], [ObjectCliName], [ObjectClass]')
   CREATE NONCLUSTERED INDEX [JobControl_ObjectsProcessData_tbl_IDX2] ON [dbo].[JobControl_ObjectsProcessData_tbl]
     ([ObjectName], [ObjectFromSiteID], [ObjectCliName], [ObjectClass])
   WITH (
     FILLFACTOR = 80,
     PAD_INDEX = OFF,
     DROP_EXISTING = OFF,
     STATISTICS_NORECOMPUTE = OFF,
     SORT_IN_TEMPDB = OFF,
     ONLINE = OFF,
     ALLOW_ROW_LOCKS = ON,
     ALLOW_PAGE_LOCKS = ON)
   ON [PRIMARY]
  end
  */
  -- 01.08.2015/J.Fes removed
  If Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX2')
  begin
   print (' --> drop index: JobControl_ObjectsProcessData_tbl_IDX2 --> 06.08.2015 NOT in Use')
   DROP INDEX [JobControl_ObjectsProcessData_tbl_IDX2] ON [dbo].[JobControl_ObjectsProcessData_tbl]
  end

  -- 01.08.2015/J.Fes removed
  set @ColumeName='ObjectCliName'
  if Not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print (' --> Delete Column:' + @ColumeName + ' in Table:' + @MyTableName)
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] DROP Column [' + @ColumeName +']'
      execute (@ExecuteSQLstr)
    end
  -- 01.08.2015/J.Fes removed
  set @ColumeName='ObjectName'
  if Not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print (' --> Delete Column:' + @ColumeName + ' in Table:' + @MyTableName)
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] DROP Column [' + @ColumeName +']'
      execute (@ExecuteSQLstr)
    end
  -- 01.08.2015/J.Fes removed
  set @ColumeName='ObjectFromSiteID'
  if Not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print (' --> Delete Column:' + @ColumeName + ' in Table:' + @MyTableName)
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] DROP Column [' + @ColumeName +']'
      execute (@ExecuteSQLstr)
    end
  -- 01.08.2015/J.Fes removed
  set @ColumeName='ObjectClass'
  if Not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print (' --> Delete Column:' + @ColumeName + ' in Table:' + @MyTableName)
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] DROP Column [' + @ColumeName +']'
      execute (@ExecuteSQLstr)
    end
  -- 01.08.2015/J.Fes removed
  set @ColumeName='JobControlRuleName'
  if Not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print (' --> Delete Column:' + @ColumeName + ' in Table:' + @MyTableName)
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] DROP Column [' + @ColumeName +']'
      execute (@ExecuteSQLstr)
    end

  -- 01.08.2015/J.Fes removed
  set @ColumeName='ProcessOnSiteID'
  if Not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print (' --> Delete Column:' + @ColumeName + ' in Table:' + @MyTableName)
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] DROP Column [' + @ColumeName +']'
      execute (@ExecuteSQLstr)
    end



  If NOT Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX3')
  begin
   print (' --> create index: JobControl_ObjectsProcessData_tbl_IDX3 --> ProcessPacketName')
   CREATE NONCLUSTERED INDEX [JobControl_ObjectsProcessData_tbl_IDX3] ON [dbo].[JobControl_ObjectsProcessData_tbl]
     ([ProcessPacketName])
   WITH (
     FILLFACTOR = 80,
     PAD_INDEX = OFF,
     DROP_EXISTING = OFF,
     STATISTICS_NORECOMPUTE = OFF,
     SORT_IN_TEMPDB = OFF,
     ONLINE = OFF,
     ALLOW_ROW_LOCKS = ON,
     ALLOW_PAGE_LOCKS = ON)
   ON [PRIMARY]
  end

  If NOT Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX4')
  begin
    print (' --> create index: JobControl_ObjectsProcessData_tbl_IDX4 --> TaskScheduler_tbl_ID')
    CREATE NONCLUSTERED INDEX [JobControl_ObjectsProcessData_tbl_IDX4] ON [dbo].[JobControl_ObjectsProcessData_tbl]
    (TaskScheduler_tbl_ID)
    WITH (
    FILLFACTOR = 80,
    PAD_INDEX = OFF,
    DROP_EXISTING = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)
    ON [PRIMARY]
  end


  If NOT Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX5')
  begin
    print (' --> create index: JobControl_ObjectsProcessData_tbl_IDX5 --> ProcessResultCode')
    CREATE NONCLUSTERED INDEX [JobControl_ObjectsProcessData_tbl_IDX5] ON [dbo].[JobControl_ObjectsProcessData_tbl]
    (ProcessResultCode)
    WITH (
    FILLFACTOR = 80,
    PAD_INDEX = OFF,
    DROP_EXISTING = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)
    ON [PRIMARY]
  end


  If NOT Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX6')
  begin
    print (' --> create index: JobControl_ObjectsProcessData_tbl_IDX6 --> ProcessResultMsg')
    CREATE NONCLUSTERED INDEX [JobControl_ObjectsProcessData_tbl_IDX6] ON [dbo].[JobControl_ObjectsProcessData_tbl]
    (ProcessResultMsg)
    WITH (
    FILLFACTOR = 80,
    PAD_INDEX = OFF,
    DROP_EXISTING = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)
    ON [PRIMARY]
  end

  If NOT Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX7')
  begin
    print (' --> create index: JobControl_ObjectsProcessData_tbl_IDX7 --> ProcessTimeStart')
    CREATE NONCLUSTERED INDEX [JobControl_ObjectsProcessData_tbl_IDX7] ON [dbo].[JobControl_ObjectsProcessData_tbl]
    (ProcessTimeStart)
    WITH (
    FILLFACTOR = 80,
    PAD_INDEX = OFF,
    DROP_EXISTING = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)
    ON [PRIMARY]
  end
  
  If NOT Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX8')
  begin
    print (' --> create index: JobControl_ObjectsProcessData_tbl_IDX8 --> ProcessTimeEnd')
    CREATE NONCLUSTERED INDEX [JobControl_ObjectsProcessData_tbl_IDX8] ON [dbo].[JobControl_ObjectsProcessData_tbl]
    (ProcessTimeEnd)
    WITH (
    FILLFACTOR = 80,
    PAD_INDEX = OFF,
    DROP_EXISTING = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)
    ON [PRIMARY]
  end
  
  

  If NOT Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX9')
  begin
    print (' --> create index: JobControl_ObjectsProcessData_tbl_IDX9 --> ProcessState')
    CREATE NONCLUSTERED INDEX [JobControl_ObjectsProcessData_tbl_IDX9] ON [dbo].[JobControl_ObjectsProcessData_tbl]
    (ProcessState)
    WITH (
    FILLFACTOR = 80,
    PAD_INDEX = OFF,
    DROP_EXISTING = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)
    ON [PRIMARY]
  end
  
  If NOT Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX10')
  begin
    print (' --> create index: JobControl_ObjectsProcessData_tbl_IDX10 --> Row_isLastRes (new 23.02.2009)')
    CREATE NONCLUSTERED INDEX [JobControl_ObjectsProcessData_tbl_IDX10] ON [dbo].[JobControl_ObjectsProcessData_tbl]
    (Row_isLastRes)
    WITH (
    FILLFACTOR = 80,
    PAD_INDEX = OFF,
    DROP_EXISTING = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)
    ON [PRIMARY]
  end
  
  If NOT Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX11')
  begin
    print (' --> create index: JobControl_ObjectsProcessData_tbl_IDX11 --> ProcessProcStateCol (new 24.02.2009)')
    CREATE NONCLUSTERED INDEX [JobControl_ObjectsProcessData_tbl_IDX11] ON [dbo].[JobControl_ObjectsProcessData_tbl]
    (ProcessProcStateCol)
    WITH (
    FILLFACTOR = 80,
    PAD_INDEX = OFF,
    DROP_EXISTING = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)
    ON [PRIMARY]
  end

  If NOT Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX12')
  begin
    print (' --> create index: JobControl_ObjectsProcessData_tbl_IDX12 --> ProcessPrgUsedVer (new 05.04.2013 J.Fes)')
    CREATE NONCLUSTERED INDEX [JobControl_ObjectsProcessData_tbl_IDX12] ON [dbo].[JobControl_ObjectsProcessData_tbl]
    (ProcessPrgUsedVer)
    WITH (
    FILLFACTOR = 80,
    PAD_INDEX = OFF,
    DROP_EXISTING = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)
    ON [PRIMARY]
  end
  
  If NOT Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX13')
  begin
    print (' --> create index: JobControl_ObjectsProcessData_tbl_IDX13 --> ProcessResultLink (new 03.11.2017 S.Gueth)')
    CREATE NONCLUSTERED INDEX [JobControl_ObjectsProcessData_tbl_IDX13] ON [dbo].[JobControl_ObjectsProcessData_tbl]
    (ProcessResultLink)
    WITH (
    FILLFACTOR = 80,
    PAD_INDEX = OFF,
    DROP_EXISTING = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)
    ON [PRIMARY]
  end
  
  -- #New 10.07.2018 S.Gueth
  If NOT Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX14')
  begin
    print (' --> create index: JobControl_ObjectsProcessData_tbl_IDX14 --> Objects_tbl_ID, ProcessProcStateCol (new 10.07.2018 S.Gueth)')
    CREATE NONCLUSTERED INDEX [JobControl_ObjectsProcessData_tbl_IDX14] ON [dbo].[JobControl_ObjectsProcessData_tbl]
    (
      [Objects_tbl_ID] ASC,
	    [ProcessProcStateCol] ASC
    )
    WITH (
      PAD_INDEX = OFF, 
      STATISTICS_NORECOMPUTE = OFF, 
      SORT_IN_TEMPDB = OFF, 
      DROP_EXISTING = OFF, 
      ONLINE = OFF, 
      ALLOW_ROW_LOCKS = ON, 
      ALLOW_PAGE_LOCKS = ON
    )
    ON [PRIMARY]
  end                           
  
  -- 18.10.2020/J.Fes moved from JobMgr_30_ApplProceduresReports03_ListOfResults.sql in this .sql file
  EXECUTE JF_IndexCheckCreateDef 'ProcCol_TimeEnd_ObjId', 'JobControl_ObjectsProcessData_tbl', '[ProcessProcStateCol] ASC, [ProcessTimeEnd] ASC, [Objects_tbl_ID] ASC'

  -- #LUp: 06.11.2021 J.Fes
  set @ColumeName='ProcessTimeDuration'
  set @ValueCurr=''
  set @ValueDesired='2022-01-07-04'
  set @ValueDesired='2022-01-07-05'
  EXECUTE zzzzzDbMaintain_valueGet @ColumeName , @ValueCurr Output , @ValueDesired , @ValueCompareStat Output
  if @ValueCompareStat <> 1
   begin
    -- #LUp: 06.11.2021 J.Fes
    if not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
      begin
       set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] DROP Column [' + @ColumeName +']'
       execute (@ExecuteSQLstr)
      end

    if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
      begin
        print ('    |-> Create/Update Column:' + @ColumeName + ' returns process duration .. (new 06.11.2021)')
        ALTER TABLE dbo.JobControl_ObjectsProcessData_tbl
        ADD ProcessTimeDuration AS
         case
          when [ProcessTimeStart] IS NULL then (-1)
          when [ProcessTimeEnd] IS NULL then (-2)
          when [ProcessTimeEnd]>=[ProcessTimeStart] then datediff(SECOND,[ProcessTimeStart],[ProcessTimeEnd])
          -- #New 07.01.2022/J.Fes Duration negative
          else datediff(SECOND,GetDate(),[ProcessTimeStart])
          -- else (-3)
         end
      end
      EXECUTE zzzzzDbMaintain_valueSave @ColumeName , @ValueDesired
      print @ColumeName + ' [Update:' + @ValueDesired + ' ->DONE!]'
   end




EndeExit:
 Print '------------------------------------------------------------------------'
 Print 'Done on Database: #DB_N#'
 Print '------------------------------------------------------------------------'
GO
