Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Table         : [JobControlRules_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='JobControlRules_tbl'

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

--------------------------------------------------------------------------------
TableCreate:
  set @MyActionMsg1=
      '-- Table: [JobControlRules_tbl] dose not exist and will be created new'
  Print (@MyActionMsg1)
  CREATE TABLE [dbo].[JobControlRules_tbl] (

          [Id]                                  [uniqueidentifier] NOT NULL ,
          [JobOrder]                            [nvarchar] (32) ,    -- JobOrder defines the order of the Job's 06.11.2018: Upsize to 32 Char!!
          [JobName]                             [nvarchar] (32) ,    -- JobName #Doc: "der JobName ist eindeutig"
          [JobDescription]                      [nvarchar] (255) ,   -- Beschreibung des Job's
          [JobAktive]                           [bit] NULL ,         -- ist diese Regel aktive Ja/Nein
          [JobComputers]                        [nvarchar] (128) ,   -- hält fest welche Computer mit Computernamen diesen Job bearbeiten dürfen
          [JobOnTceSite_id_IsRefTo_Sites_tbl_id] [nvarchar] (64),    -- [uniqueidentifier] null ,
          [JobScriptName]                       [nvarchar] (50) ,    -- JobScriptName = Referenziert den Eintrag in JobScript_tbl (JobScripts)
          [JobPacket_ObjectCount]               [int] ,              -- Anzahl der zu extrahierenden Objekte je Packet
          [JobPacket_JobSumObjectCount]         [int] ,              -- J.Packet Max.Sum(Ap.Count)  #New 12.10.2012 J.Fes
                                                                     -- max Summe der Komponenten die in allen Assembyls des Paketes enthalten sein duerfen

          [JobPacket_JobSumCompCountUnique]     [int] ,              -- J.Packet Max.Sum(Ap.Count)  #New 02.05.2016 J.Fes
                                                                     -- max Summe der Unic Komponenten die in allen Assembyls des Paketes enthalten sein duerfen

          [JobPacket_ContainOnlyObjOfDiffrentI] [bit] NULL ,         -- JobPaket beinhaltet nur Objekte unterschiedlicher Items
                                                                     -- 'sel:O.Only Diffr.Items' #New: 12.10.2012 J.Fes if val >0 then Roule is used
          [JobResourcePoints]                   [int] ,              -- JobResourcePoints legt die Anzahl der JobResource Points fest
                                                                     --  |-> die dieser Job verbraucht. Wird 0 vergeben so wird kein ResourePoint gezaehlt. New:[31.10.2011]

          [JobNumberLast]                       [int] ,              -- letzte JobNumer
          [ObjectsSendCount]                    [int],               -- Anzahl der durch diese Regel gesendeten Objekte

          [TimeSchedule]                        [nvarchar](255) ,    -- definiert JobRule TimeSchedule Regeln
          [ProcStateCol]                        [varchar] (32) ,     -- hält fest in welchem Colume der Dataset Ir Tabelle der Processstatus verwaltet wird !
          [JobResult_ProcStateValueRule]        [nvarchar](max) ,    -- definiert die ProcStatValueRule new:[31.10.2011]
                                                                     -- Hierüber können Regeln festgelegt werden den P.Status eines JobErgebnisses festzulegen.

          [SelObjectsBy_ProcStateVal]           [nvarchar](34) ,     -- legt den Wert fest über den die Objekte selektiert werden z.b. 'N' 'M' ...
                                                                     -- Size angepasst auf 32+2 = 34

          [SelObjectsBy_ObjectClass]            [nvarchar](10) ,     -- selection der Daten über DS=Datasets oder IR=Irs IRforRefile
          [SelObjectsBy_UgMasterType]           [nvarchar](10) ,     -- selection über SP oder AP
          [SelObjectsBy_ObjectType]             [nvarchar](255) ,    -- selection der Daten über UGMASTER, UGPART , UGALTREP ..
                                                                     -- .Net siehe JC --> DatsetObjectTypes.UGMASTER
                                                                     -- #Change: 10.10.2013/J.Fes resize from 32 to 255
          [SelObjectsBy_ObjectIrType]           [nvarchar](255) ,    -- selection der Daten über ItemRevsision Type new 20.12.2011
          [SelObjectsBy_ObjectSpecSelRule]      [nvarchar](32) ,     -- extendet Data selection Rules #New: 18.07.2013 09:24 J.Fes
          [SelObjectsBy_ObjectSpecSelRule_IncObjTypes]
                                                [nvarchar](255) ,    -- defines if SelObjectsBy_ObjectSpecSelRule only ObjectTypes UGMASTER, UGPART , UGALTREP .. new 10.10.2013
          [SelObjectsBy_ObjectSpecSelRule_ProcStateVal]
                                                [nvarchar](34) ,     -- legt den StatusValue fest über den die via ObjectSpecSelRule Objekte selektiert werden z.b.  '%' 'N' 'M' ...
                                                                     -- #New 06.11.2013


          [SelObjectsBy_MinComponents]          [int] ,              -- select min Compontens
          [SelObjectsBy_MaxComponents]          [int] ,              -- select max Compontens
          [SelObjectsBy_MaxCompCountUnique]     [int] ,              -- #New:02.05.2016/J.Fes select max Unic Compontens

          [SelObjectsBy_MinApLevels]            [int] ,              -- select min AP Levesl
          [SelObjectsBy_MaxApLevels]            [int] ,              -- select min AP Levesl

          [SelObjectsBy_Volume]                 [nvarchar] (255) ,   -- select nach Volume
          [SelObjectsBy_Name]                   [nvarchar] (128) ,   -- select nach Names
          [SelObjectsBy_Group]                  [nvarchar] (32) ,    -- select nach Group
          [SelObjectsBy_Owner]                  [nvarchar] (32) ,    -- select nach User
          [SelObjectsBy_Status]                 [nvarchar] (max) ,   -- select nach Status 'sel:O.Status' #New: 12.10.2012 J.Fes
          [SelObjectsBy_SaveVersion]            [nvarchar] (512) ,   -- select via save version #New: 20.03.2019 S.Gueth
          [SelObjectsBy_MultiSiteObjects]       [nvarchar] (10) ,    -- MultiSite Object selection 10 = OwningSiteObjektes_Only 20 = RemoteSiteObjektes_Only 30 = RemoteAndOwningSiteObjektes_Only

          [SelObjectsBy_PFM]                    [nvarchar] (10) ,    -- PartFamily Object selection [1=PFM_Ignor] [2=PFM_No]  [4=PFM_Only] (new 21.10.2009 J.Fes)
          [SelObjectsBy_FsHitCount_YN]          [bit] NULL ,         -- Objekte via FsHitCount selectieren Yes/No (new 26.03.2010)

          [SelObjectsBy_SQLScript_YN]           [bit] NULL ,         -- ist diese Regel aktive Ja/Nein (neu in 18.10.2007)
          [SelObjectsBy_SQLScript_PPNE]         [nvarchar] (255),    -- Teilverzeichniss zum SQL Script (neu in 18.10.2007)

          [SelObjectsBy_ObjectLastModDateBefor] [datetime] ,         -- selection der Daten über Last Mod Befor Datetime
          [SelObjectsBy_ObjectLastModDateAfter] [datetime] ,         -- selection der Daten über Last Mod After Datetime

          [AddAdditional_Data]                  [nvarchar](32) ,     -- add additional Data Rule ITEM IR ITEM_IR #New: 15.09.2014 08:02 J.Fes

          [Note]                                [nvarchar] (MAX),    -- Notice Feld (new 17.11.2011 20:39)
          [JobControlActivInfo]                 [nvarchar] (max) ,   -- #New:31.10.2018/S.Gueth, stores last JobControlActivStatus Rule information

          [ExtendedConfig1_xml]                 [xml]                -- ExtendedConfig1 as XML (#New: 27.02.2015/S.Gueth)

  ) ON [PRIMARY]


  goto CheckCreateIndex

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

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

  Declare @ColumeName nvarchar (256)
  Declare @ColumeNameOld nvarchar (256)
  Declare @ColumeNameNew nvarchar (256)
  Declare @ExecuteStr nvarchar (max)


  -- #New:  05.08.2008
  set @ColumeName='SelObjectsBy_ProcStateVal'
  if (Select Coalesce(Col_length('JobControlRules_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' for table:  [JobControlRules_tbl] (new 05.08.2008)]')
      set @ExecuteStr='ALTER TABLE [dbo].[JobControlRules_tbl] ADD [' + @ColumeName +'] [nvarchar] (10) NULL'
      execute (@ExecuteStr)
    end

  -- #New:  03.07.2009 13:00
  set @ColumeName='SelObjectsBy_MaxApLevels'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' in Table:' + @MyTableName + ' select AP Objects by Max AP Levels (new 03.07.2009)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] Int'
      execute (@ExecuteStr)
    end

  -- #New:  03.07.2009 13:00
  set @ColumeName='SelObjectsBy_MinApLevels'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' in Table:' + @MyTableName + ' select AP Objects by Min AP Levels (new 03.07.2009)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] Int'
      execute (@ExecuteStr)
    end
    
  -- #New:  21.10.2009 J.Fes
  set @ColumeName='SelObjectsBy_PFM'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' in Table:' + @MyTableName + ' select Objects by PartFamily [1=PFM_Ignor] [2=PFM_No]  [4=PFM_Only] (new 21.10.2009 J.Fes)')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] Int'
      execute (@ExecuteStr)
      -- siehe auch ..\01-BinServer\Resources\T-SQL\JobMgr_90_MaintainDatabase.sql Abschnitt JobControlRules_tbl_Update_SelObjectsBy_PFM
    end

  -- #New:  31.10.2011 J.Fes
  set @ColumeName='SelObjectsBy_FsHitCount_YN'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' in Table:' + @MyTableName + ' Objekte via FsHitCount selectieren Yes/No (new 31.10.2011 J.Fes)')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] Bit'
      execute (@ExecuteStr)
    end

  -- #New:  31.10.2011 J.Fes
  set @ColumeName='JobResourcePoints'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
   begin
     print ('Create Column:' + @ColumeName + ' in Table:' + @MyTableName + ' JobResourcePoints legt die Anzahl der JobResource Points fest die dieser Job verbraucht. Wird 0 vergeben so wird kein ResourePoint gezaehlt. New:[31.10.2011 J.Fes]')
     set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] Int'
     execute (@ExecuteStr)
     -- siehe auch ..\02-VB.NET\JobServer\JobServer_JobControl_ClientServer.vb
   end

  -- #New:  31.10.2011 J.Fes
  set @ColumeName='JobResult_ProcStateValueRule'
  if (Select Coalesce(Col_length('JobControlRules_tbl',@ColumeName),0)) = 0
   begin
     print ('Create Column:' + @ColumeName + ' for table:  [JobControlRules_tbl] efiniert die ProcStatValueRule (new:31.10.2011 J.Fes)]')
     set @ExecuteStr='ALTER TABLE [dbo].[JobControlRules_tbl] ADD [' + @ColumeName +'] [nvarchar] (max) NULL'
     execute (@ExecuteStr)
   end


  set @ColumeName='JobComputers'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) <> -1
   BEGIN
      print ('Column: JobControlRules_tbl.JobComputers upsize to max char [Change:31.10.2011 J.Fes]')
      ALTER TABLE [dbo].[JobControlRules_tbl]
        ALTER COLUMN [JobComputers] nvarchar(max) COLLATE Latin1_General_CI_AS
   END

  set @ColumeName='Note'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
   BEGIN
     print ('Create Column:' + @ColumeName + ' in Table:' + @MyTableName + ' Note Colume New:[17.11.2011 J.Fes]')
     set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (max) NULL'
     execute (@ExecuteStr)
     -- siehe auch ..\02-VB.NET\JobServer\JobServer_JobControl_ClientServer.vb
   END

   -- angepasst 23.11.2011 / J.Fes
   set @ColumeName='SelObjectsBy_ProcStateVal'
   if (Select Col_length(@MyTableName,@ColumeName)) <> 34*2
     BEGIN
      -- 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 20.12.2011 / J.Fes  / verbleibt 10.01.2012
  set @ColumeName='SelObjectsBy_ObjectIrType'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
   BEGIN
     print ('Create Column:' + @ColumeName + ' in Table:' + @MyTableName + ' selection der Daten über ItemRevsision Type [new 20.12.2011 J.Fes]')
     set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (32) NULL'
     execute (@ExecuteStr)
     -- siehe auch ..\02-VB.NET\JobServer\JobServer_JobControl_ClientServer.vb
   END


  -- #New: 13.10.2012 J.Fes
  set @ColumeName='SelObjectsBy_Status'
  if (Select Coalesce(Col_length('JobControlRules_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' for table:  [JobControlRules_tbl] (new 13.10.2012)]')
      set @ExecuteStr='ALTER TABLE [dbo].[JobControlRules_tbl] ADD [' + @ColumeName +'] [nvarchar] (max) NULL'
      execute (@ExecuteStr)
    end

  -- #New:  31.10.2011 J.Fes
  set @ColumeName='JobPacket_JobSumObjectCount'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
   begin
     print ('Create Column:' + @ColumeName + ' in Table:' + @MyTableName + ' max sum of assembly components of that may containt in the jobpaket #New:[31.10.2011 J.Fes]')
     set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] Int'
     execute (@ExecuteStr)
     -- siehe auch ..\02-VB.NET\JobServer\JobServer_JobControl_ClientServer.vb
   end


  -- #New: 13.10.2012 J.Fes
  set @ColumeName='JobPacket_ContainOnlyObjOfDiffrentI'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' in Table:' + @MyTableName + ' JobPaket contains only Objektes of diffrent Items (#New 31.10.2011 J.Fes)')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] Bit'
      execute (@ExecuteStr)
    end

  -- #New: 18.07.2013/J.Fes
  set @ColumeName='SelObjectsBy_ObjectSpecSelRule'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' in Table:' + @MyTableName + ' extendet Data selection Rules (#New: 18.07.2013 J.Fes)')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar](32)'
      execute (@ExecuteStr)
    end

   -- 08.07.2013 J.Fes Upsize / Resice
  set @ColumeName='SelObjectsBy_UgMasterType'
  if (Select Col_length(@MyTableName,@ColumeName)) <> 10*2
    BEGIN
     print ('DownSize Column:' + @ColumeName + '  [10 char (Update: 10.10.2013)]')
     set @ExecuteSQLstr=  'ALTER TABLE [dbo].[' + @MyTableName + ']' + ' ALTER COLUMN [' + @ColumeName + '] nvarchar(10)'
     execute sp_executesql @ExecuteSQLstr
    END

  set @ColumeName='SelObjectsBy_ObjectType'
  if (Select Col_length(@MyTableName,@ColumeName)) <> 255*2
    BEGIN
     print ('UpSize Column:' + @ColumeName + '  [from 32 to 255 char (Update: 10.10.2013)]')
     set @ExecuteSQLstr=  'ALTER TABLE [dbo].[' + @MyTableName + ']' + ' ALTER COLUMN [' + @ColumeName + '] nvarchar(255)'
     execute sp_executesql @ExecuteSQLstr
    END

  set @ColumeName='SelObjectsBy_ObjectIrType'
  if (Select Col_length(@MyTableName,@ColumeName)) <> 255*2
    BEGIN
     print ('UpSize Column:' + @ColumeName + '  [from 32 to 255 char (Update: 10.10.2013)]')
     set @ExecuteSQLstr=  'ALTER TABLE [dbo].[' + @MyTableName + ']' + ' ALTER COLUMN [' + @ColumeName + '] nvarchar(255)'
     execute sp_executesql @ExecuteSQLstr
    END

  -- #New: 10.10.2013/J.Fes
  set @ColumeName='SelObjectsBy_ObjectSpecSelRule_IncObjTypes'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' for table:  [JobControlRules_tbl] SelObjectsBy_ObjectSpecSelRule only ObjectTypes UGMASTER, UGPART , UGALTREP .. new 10.10.2013]')
      set @ExecuteStr='ALTER TABLE [dbo].[JobControlRules_tbl] ADD [' + @ColumeName +'] [nvarchar] (255) NULL'
      execute (@ExecuteStr)
    end
  if (Select Col_length(@MyTableName,@ColumeName)) <> 255*2
    BEGIN
     print ('UpSize Column:' + @ColumeName + '  [from max to 255 char (Update: 06.11.2013)]')
     set @ExecuteSQLstr=  'ALTER TABLE [dbo].[' + @MyTableName + ']' + ' ALTER COLUMN [' + @ColumeName + '] nvarchar(255)'
     execute sp_executesql @ExecuteSQLstr
    END

  -- #New: 10.10.2013/J.Fes
  set @ColumeName='SelObjectsBy_ObjectSpecSelRule_ProcStateVal'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' for table:  [JobControlRules_tbl] SelObjectsBy_ObjectSpecSelRule inc with status #new 06.11.2013/J.Fes]')
      set @ExecuteStr='ALTER TABLE [dbo].[JobControlRules_tbl] ADD [' + @ColumeName +'] [nvarchar] (32) NULL'
      execute (@ExecuteStr)
    end

  -- #New: 15.09.2014/J.Fes
  set @ColumeName='AddAdditional_Data'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' in Table:' + @MyTableName + ' extendet Data selection Rules (#New: 18.07.2013 J.Fes)')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar](32)'
      execute (@ExecuteStr)
    end

  -- #New: 27.02.2015/S.Gueth drop column
  set @ColumeName='SerializedConfig2Object'
  if Not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print (' --> Delete Column:' + @ColumeName + ' in Table:' + @MyTableName)
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] DROP Column [' + @ColumeName +']'
      execute (@ExecuteStr)
    end

  set @ColumeName='ExtendedConfig1_xml'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' in Table:' + @MyTableName + ' extendet serialized xml objct (#New: 27.02.2015 S.Gueth)')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [xml]'
      execute (@ExecuteStr)
    end

  -- #New:02.05.2016 23:08 J.Fes
  set @ColumeName='SelObjectsBy_MaxCompCountUnique'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
   begin
     print ('Create Column:' + @ColumeName + ' in Table:' + @MyTableName + ' max n unic components may have a assembly #New:[02.05.2016 J.Fes]')
     set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] Int'
     execute (@ExecuteStr)
   end
  -- #New:02.05.2016 23:08 J.Fes
  set @ColumeName='JobPacket_JobSumCompCountUnique'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
   begin
     print ('Create Column:' + @ColumeName + ' in Table:' + @MyTableName + ' max sum of unic assembly components that may containt in the jobpaket #New:[02.05.2016 J.Fes]')
     set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] Int'
     execute (@ExecuteStr)
   end
   
  -- New 31.10.2018 S.Gueth
  set @ColumeName='JobControlActivInfo'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column: [' + @ColumeName + '] stores last JobControlActivStatus Rule information [(new 31.10.2018/S.Gueth)]')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (max)'
      execute (@ExecuteSQLstr)
    end   

  -- #LUp 06.11.2018 J.Fes
  set @ColumeName='JobOrder'
  if (Select Col_length(@MyTableName,@ColumeName)) <> 32*2
  BEGIN
   print ('UpSize Column:' + @ColumeName + '  [from max to 32 char (Update: 06.11.2018)]')
   set @ExecuteSQLstr=  'ALTER TABLE [dbo].[' + @MyTableName + ']' + ' ALTER COLUMN [' + @ColumeName + '] nvarchar(32)'
   execute sp_executesql @ExecuteSQLstr
  END

  -- #New: 20.03.2019 S.Gueth
  set @ColumeName='SelObjectsBy_SaveVersion'
  if (Select Coalesce(Col_length('JobControlRules_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' for table:  [JobControlRules_tbl] (new 13.10.2012)]')
      set @ExecuteStr='ALTER TABLE [dbo].[JobControlRules_tbl] ADD [' + @ColumeName +'] [nvarchar] (512) NULL'
      execute (@ExecuteStr)
    end

goto CheckCreateIndex

CheckCreateIndex:

  If NOT Exists (select Name from sysindexes where Name = 'JobControlRules_tbl_pk')
  begin
     Print(' --> create index: JobControlRules_tbl_pk')
     ALTER TABLE [dbo].[JobControlRules_tbl]
       ADD CONSTRAINT [JobControlRules_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 = 'JobControlRules_tbl_JobName_uq')
  begin
     Print(' --> create index: JobControlRules_tbl_JobName_uq')
     ALTER TABLE [dbo].[JobControlRules_tbl]
        ADD CONSTRAINT [JobControlRules_tbl_JobName_uq]
        UNIQUE NONCLUSTERED ([JobName])
        WITH (
          FILLFACTOR = 80,
          PAD_INDEX = OFF,
          IGNORE_DUP_KEY = OFF,
          STATISTICS_NORECOMPUTE = OFF,
          ALLOW_ROW_LOCKS = ON,
          ALLOW_PAGE_LOCKS = ON)
  end

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

GO
