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'

--------------------------------------------------------------------------------
-- 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,
    [ObjectName]                      nvarchar (32)  , -- Name
    [ObjectFromSiteID]                nvarchar (32)  , -- von welcher site
    [ObjectCliName]                   nvarchar (128) , --
    [ObjectClass]                     nvarchar (10)  , -- DS IR
    [JobControlRuleName]              nvarchar (32)  , -- speichert den Namen der JobRule über die das Objekt bearbeitet wird
    [ProcessPacketName]               nvarchar (64)  , -- speichert den Namen des Prozesspaketes wenn = "" dann ist es ein vom TaskSeduler eingereiter Prozess
    [ProcessOnSiteID]                 nvarchar (32)  , -- speichert auf welcher Site das Object verarbeitet wurde
    [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 (255) , -- Result Message
    [ProcessResultHasWarning]         bit            , -- ProcessResultWarning 1=Warning 0=NoWarning
    [ProcessResultLink]               nvarchar (255) , -- Link für z.B. Verweise auf Dateien Ordnern oder weitern Links (neu 12.07.09)
    [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)
    [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)

  Declare @ColumeName nvarchar (256)
  Declare @ColumeNameOld nvarchar (256)
  Declare @ColumeNameNew nvarchar (256)
  Declare @ExecuteStr nvarchar (max)
  
  -- rename 24.04.2008 12:00 J.Fes von ObjectNamedId --> ObjectCliName
  set @ColumeNameOld='ObjectNamedId'
  set @ColumeNameNew='ObjectCliName'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
     set @ExecuteStr='sp_rename ' + '''' + '[dbo].[JobControl_ObjectsProcessData_tbl].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteStr)
      execute (@ExecuteStr)
    end


  -- Colume ObjectCliName lengs von 128 -> 255 vergroesseern (24.04.2008 J.Fes)
  if (Select Col_length(@MyTableName,'ObjectCliName')) <> 255*2
    begin
     print ('   |-> Column:ObjectCliName upsize to 255 char')
     DROP INDEX [JobControl_ObjectsProcessData_tbl_IDX2] ON [dbo].[JobControl_ObjectsProcessData_tbl]

     ALTER TABLE [dbo].[JobControl_ObjectsProcessData_tbl]
     ALTER COLUMN [ObjectCliName] nvarchar(255) COLLATE Latin1_General_CI_AS

     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


  -- 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 @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [uniqueidentifier] NULL'
      execute (@ExecuteStr)
    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 @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(32)'
      execute (@ExecuteStr)
    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 @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] bit'
      execute (@ExecuteStr)
    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 @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(255)'
      execute (@ExecuteStr)
    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 @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] bit'
      execute (@ExecuteStr)
    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


  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')
    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

  If NOT Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX2')
  begin
   print (' --> create index: JobControl_ObjectsProcessData_tbl_IDX2')
   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

  If NOT Exists (select Name from sysindexes where Name = 'JobControl_ObjectsProcessData_tbl_IDX3')
  begin
   print (' --> create index: JobControl_ObjectsProcessData_tbl_IDX3')
   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')
    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



--------------------------------------------------------------------------------
EndeExit:
 -- Print '------------------------------------------------------------------------'
 -- Print (@MyActionMsg1)
 Print ('   |-> Done on Database: #DB_N#')
 Print '------------------------------------------------------------------------'

GO
