Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Table         : [Objects_tbl]'
Print ' Creation Date : 22.03.2006'
Print ' Last Update.. : 17.10.2018/J.Fes'
Print ' Autor         : J.Feuerstein'
Print '-------------------------------------------------------------------------'

use [#DB_N#]

Declare @MyActionMsg1 nvarchar (max)
Declare @MyTableName nvarchar (max)
set @MyTableName='Objects_tbl'
DECLARE @ExecuteSQLstr nvarchar(max)

declare @indexNrCur  int
declare @ProcStatColsN int
declare @ObjectAttrsN int
declare @indexNrStr   nvarchar (max)
declare @IndexName    nvarchar (max)

declare @ColumeName nvarchar (max)
declare @ColumeNameOld nvarchar (max)
declare @ColumeNameNew nvarchar (max)
declare @ExecuteStr nvarchar (max)

-- def. Anzahl ProcStateCol's L.Update 06.08.2014
set @ProcStatColsN = 20
-- def. Anzahl ObjectAttr's L.Update 06.08.2014
set @ObjectAttrsN = 10


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

--------------------------------------------------------------------------------
TableCreate:
  set @MyActionMsg1=
      '-- Table: [Objects_tbl] dose not exist and will be created new'

  Print (@MyActionMsg1)

  -- Wichtig: bei struktur anpassungen immer Procedure: Object_Import mit anpassen !!
  CREATE TABLE [dbo].[Objects_tbl] (
         [Id]                             uniqueidentifier NOT NULL ,  --  Interne Datensatz ID
                                          -- change A.Becker 22.08.2012 (KION): Puid Feld von 15 auf 32 Zeichen erweitert
         [ObjectPuid]                     nvarchar(64) COLLATE Latin1_General_CS_AS NULL , -- PUID des Objektes neu 2.1.09 J.Fes Info durch Angabe von Latin1_General_CS_AS wird erreicht das das der Feldinhalt case sensitiv betrachtet wird
         [ObjectName]                     nvarchar(128)  , --  Name des Objects = ID Refact:(08.02.2012) diesen Spalten Namen auf ObjectId umbenennen.
                                                          -- 04.06.2014 auf 64 erweitert siehe auch import Routionen
                                                          -- 25.10.2014 auf 128 erweitert siehe auch import Routinen (S.Gueth)
         [ObjectName2]                    nvarchar(128)  , --  Name 2 = Part - Name neu 1.12.08 J.Fes    diese Spalten Namen auf ObjectName umstellen.
                                                          -- 13.06.2014 auf 64 Zeichen erweitert siehe auch import Routionen
                                                          -- 25.10.2014 auf 128 erweitert siehe auch import Routinen (S.Gueth)
         [ObjectDescription]              nvarchar(255) , --  Description des Object's neu 1.12.08 J.Fes

         [ObjectFromSiteID]               nvarchar(32)  , --  die Site auf dem die DataSet gezogen wurde
         [ObjectClass]                    nvarchar(10)  , --  I-(Item) IR-(ItemRev) DS-(Dataset)
         [ObjectType]                     nvarchar(32)  , --  bei DS =UGMASTER UGPART UGALTREP IR='CAD IR' oder 'NC-IR'
         [ObjectCliName]                  nvarchar(255) , --  ObjectCLIName ("C"omannd"L"ine"I"nterface - Name) (umbenannt von ObjectNamedId am 24.04.08 J.Fes)
         [ObjectOwningSiteID]             nvarchar(32)  , --  Site dem das Object gehört
         [ObjectExportRecord]             nvarchar(255) , --  List of sites object is exported to #New: 16.08.2013 15:44
         [ObjectExportRecordDate]         datetime      , --  Letztes Export Datum #new 20.08.2013 / J.Fes

         [ObjectOwner]                    nvarchar(32)  , --  Owner des Datasets
         [ObjectGroup]                    nvarchar(255) , --  Object gehoert der Gruppe --> 16.09.2015 size from 32 to 255
         [ObjectCreateDate]               datetime      , --  erzeugungs Datum
         [ObjectLastModUser]              nvarchar(32)  , --  Last Modi User
         [ObjectLastModDate]              datetime      , --  Last Modi Date

         [ObjectUgMaster_Type]            nvarchar(32)  , --  SP = Single Part AP = Assemplypart NP = Part No BOM NO // NPB = Part No BOM Yes
         [ObjectUgMaster_CompCount]       int           , --  Anzahl der verbauten Komponenten wenn es eine Baugruppe ist
         [ObjectUgMaster_CompCountUnique] int           , --  Anzahl der verbauten eindeutigen Komponenten wenn es eine Baugruppe ist -- new 22.08.2012 a.becker (KION)
         [ObjectUgMaster_APLevels]        int           , --  Anzahl der verbauung Ebenen wenn es eine Baugruppe ist

         [ObjectUgMaster_Lev1CompN]       int           , --  #New: 06.08.2014/J.Fes Level 1 Anzahl der verbauten Komponenten
         [ObjectUgMaster_Lev1CompUniqueN] int           , --  #New: 06.08.2014/J.Fes Level 1 Anzahl der eindeutigen/Unique Komponenten
         [ObjectUgMaster_ApBOM_isPrecise] bit           , --  #New: 06.08.2014/J.Fes Informs if AP BOM is Precise

         [ObjectUgMaster_FsHitCount]      int           , --  anzahl der Hitcounts des Datasets - Parts
         [ObjectFs_DPNE]                  nvarchar(255) , --  Full Path to File Object
         [ObjectFs_Vol]                   nvarchar(64)  , --  Volume
         [ObjectFs_NE]                    nvarchar(64)  , --  FileName
         [ObjectFs_SizeBytes]             bigint        , --  Größe des Files in Bytes -- new 22.08.2012 a.becker  -- #Change: 08.11.2012 J.Fes von int auf bigint
         [ObjectFs_HashCode]              nvarchar(64)  , --  HashCode des Files       -- new 22.08.2012 a.becker
         [ObjectFS_LastModDate]           datetime      , --  last Mode date of file Object
         [ObjectDs_Ver]                   int           , --  #New 27.05.2016/J.Fes DS.Version current
         [ObjectDs_VerKeepLimt]           int           , --  #New 27.05.2016/J.Fes DS.Version KeepLimit
         [ObjectDs_VersN]                 int           , --  #New 27.05.2016/J.Fes DS.Version N exist


         [ObjectSaveVersion]              nvarchar(64)  , --  Save Version des Objektes -- new 22.08.2012 a.becker -- old Name: ObjectCADVersion


         [ObjectUgMaster_PartFamMemName]  nvarchar(64)  , --  Name des PFM Templates

         [ObjectReleaseState]             nvarchar(128)  , --  Release State #new 1.12.08  10.01.2019 MTU need extend to 128
         [ObjectReleaseDate]              datetime      , --  Release Datum #new 1.12.08


         [ObjectNote]                     nvarchar(255) , --  Notice Feld
         [ObjectPrjIds]                   nvarchar(255) , --  Project Liste -- #new: 01.04.2013 J.Fes

         [ObjectSyncTrigger]              int           , --  #New 17.06.2016/J.Fes ObjectSyncTrigger Value null or

         [ObjectIsIrLatest]               bit           , --  #New: 03.06.2015/J.Fes Object is IR Latest by Rev. Rule
         [ObjectIrLatestRank]             int           , --  #New: 03.06.2015/J.Fes Object is IR Latest RANK by Rev. Rule
         
         [MultiCadAP]                     nvarchar(255) , --  #New: 20.08.2019/S.Gueth Multi Cad Assembly
         [MultiCadItemRevs]               nvarchar(max) , --  #New: 20.08.2019/S.Gueth Multi Cad Assembly Item Revisions

         [ImportObjectErr]                nvarchar(255) , --  info über den Importvorgang des Objektes (neu 31.12.2008)

         [ProcStateCol01]                 nvarchar(34)  , --  Processstatus zu Proc1 Felgroesse angep. von 10 auf 32+2 = 34
         [ProcStateCol02]                 nvarchar(34)  , --  Processstatus zu Proc2
         [ProcStateCol03]                 nvarchar(34)  , --  ..
         [ProcStateCol04]                 nvarchar(34)  , --  ..
         [ProcStateCol05]                 nvarchar(34)  , --  ..
         [ProcStateCol06]                 nvarchar(34)  , --  ..
         [ProcStateCol07]                 nvarchar(34)  , --  ..
         [ProcStateCol08]                 nvarchar(34)  , --  ..
         [ProcStateCol09]                 nvarchar(34)  , --  ..
         [ProcStateCol10]                 nvarchar(34)  , --  Processstatus zu Proc10

                                                          --  #New: 06.08.2014/J.Fes
         [ProcStateCol11]                 nvarchar(34)  , --  Processstatus zu Proc11
         [ProcStateCol12]                 nvarchar(34)  , --  ..
         [ProcStateCol13]                 nvarchar(34)  , --  ..
         [ProcStateCol14]                 nvarchar(34)  , --  ..
         [ProcStateCol15]                 nvarchar(34)  , --  ..
         [ProcStateCol16]                 nvarchar(34)  , --  ..
         [ProcStateCol17]                 nvarchar(34)  , --  ..
         [ProcStateCol18]                 nvarchar(34)  , --  ..
         [ProcStateCol19]                 nvarchar(34)  , --  ..
         [ProcStateCol20]                 nvarchar(34)  , --  Processstatus zu Proc20
         [ProcStateColRf]                 nvarchar(34)  , --  Processstatus zu Refile
         [ProcStateColMc]                 nvarchar(34)  , --  #New: 26.02.2016 add for Multistie Check

                                                          --  #New: 06.08.2014/J.Fes
                                                          --  #LUp: 27.04.2016/J.Fes Upsice from 34 to 255 char
         [ObjectAttr01]                   nvarchar(255) , --  Attribute 01
         [ObjectAttr02]                   nvarchar(255) , --  ..
         [ObjectAttr03]                   nvarchar(255) , --  ..
         [ObjectAttr04]                   nvarchar(255) , --  ..
         [ObjectAttr05]                   nvarchar(255) , --  ..
         [ObjectAttr06]                   nvarchar(255) , --  ..
         [ObjectAttr07]                   nvarchar(255) , --  ..
         [ObjectAttr08]                   nvarchar(255) , --  ..
         [ObjectAttr09]                   nvarchar(255) , --  ..
         [ObjectAttr10]                   nvarchar(255)   --  Attribute 10

  )
  ON [PRIMARY]

  goto CheckCreateIndex

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

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

  set @ColumeName='ObjectPuid'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName +  ' .. PUID of Object (new 2.1.2009 J.Fes)')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(15) COLLATE Latin1_General_CS_AS NULL'
      execute (@ExecuteStr)
    end


  set @ColumeName='ImportObjectErr'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName +  ' .. stores info about importObject Results (new 31.12.2008)')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(64)'
      execute (@ExecuteStr)
    end


  set @ColumeName='ObjectName2'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName +  ' .. for Part-Name (new 16.12.08 J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(128)'
      execute (@ExecuteStr)
    end

  -- Ref auf die JobControl ProcessData solange = null ist das Objekt noch nicht in Process (new 26.09.08)
  set @ColumeName='ObjectDescription'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName +  ' .. Object Description (new 01.12.08 J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(255)'
      execute (@ExecuteStr)
    end

  set @ColumeName='ObjectReleaseState'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName +  ' .. Object ReleaseState (new 01.12.08 J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(64)'
      execute (@ExecuteStr)
    end

  set @ColumeName='ObjectReleaseDate'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName +  ' .. Object ObjectReleaseDate (new 01.12.08 J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] datetime'
      execute (@ExecuteStr)
    end

  -- ergaenzt 16.05.2008 08:38
  set @ColumeName='ProcStateColRf'
  if (Select Coalesce(Col_length('Objects_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [refile Processstatus (new 16.05.08)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] [nvarchar] (11) NULL'
      execute (@ExecuteStr)
    end

  -- rename 24.04.2008 12:00 J.Fes von ObjectNamedId --> ObjectCliName
  set @ColumeNameOld='ObjectNamedId'
  set @ColumeNameNew='ObjectCliName'
  if not (Select Coalesce(Col_length('Objects_tbl',@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
      set @ExecuteStr='sp_rename ' + '''' + '[dbo].[Objects_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')
     ALTER TABLE [dbo].[Objects_tbl] DROP CONSTRAINT [Objects_tbl_ID_uq]
     DROP INDEX [Objects_tbl_IDX2] ON [dbo].[Objects_tbl]
     ALTER TABLE [dbo].[Objects_tbl]
     ALTER COLUMN [ObjectCliName] nvarchar(255) COLLATE Latin1_General_CI_AS
  end

  set @ColumeName='ObjectFS_LastModDate'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName +  ' .. ObjectFS_LastModDate (new 25.04.2014 J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] datetime NULL'
      execute (@ExecuteStr)
    end

  -- 23.11.2011/J.Fes Anpassung der ProcStateColName auf Size = 32+2=34 Zeichen siehe JobControlRules_tbl.JobName
  set @indexNrCur = 1
  WHILE @indexNrCur <= @ProcStatColsN +1
  BEGIN
    set @indexNrStr=right('00' + convert(nvarchar,@indexNrCur),2)
    set @ColumeName='ProcStateCol' + @indexNrStr
    set @IndexName = 'Objects_tbl_IDX_PsCol' + @indexNrStr
    if @indexNrCur = @ProcStatColsN +1
    begin
      -- Sonderregel: Process [ProcStateColRf]
      set @ColumeName='ProcStateColRf'
      set @IndexName = 'Objects_tbl_IDX_PsColRf'
    end

    -- #Step: ProcState Cols erzeugen
    if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [Create additional ProcStatCols (#New:06.08.2014)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + ' ] ADD [' + @ColumeName +'] nvarchar(34)'
      execute (@ExecuteStr)
    end

    -- #Step: ProcStateCol index bereinigen
    if (Select Col_length('Objects_tbl',@ColumeName)) <> 34*2
     BEGIN
      If Exists (select Name from sysindexes where Name = @IndexName)
      BEGIN
        -- Step entfernen Index
        set @ExecuteSQLstr='DROP INDEX [' + @IndexName + ']  ON [dbo].[' + @MyTableName + ']'
        print (@ExecuteSQLstr)
        execute sp_executesql @ExecuteSQLstr
      END
      -- Anpassen auf Size = 34
      set @ExecuteSQLstr=  'ALTER TABLE [dbo].[' + @MyTableName + ']' + ' ALTER COLUMN [' + @ColumeName + '] nvarchar(34) COLLATE Latin1_General_CI_AS'
      print (@ExecuteSQLstr)
      execute sp_executesql @ExecuteSQLstr
     END
     set @indexNrCur = @indexNrCur +1
  END

  -- 06.08.2014/J.Fes wieder hinzufuegen ObjectAttr01..10 --
  set @indexNrCur = 1
  WHILE @indexNrCur <= @ObjectAttrsN
  BEGIN
   set @indexNrStr=right('00' + convert(nvarchar,@indexNrCur),2)
   set @ColumeName='ObjectAttr' + @indexNrStr
    if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    -- #Step: add Columes
    begin
      print ('Create Column:' + @ColumeName + '  [Create additional ObjectAttr (#New:06.08.2014)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + ' ] ADD [' + @ColumeName +'] nvarchar(255)'
      execute (@ExecuteStr)
    end
   set @indexNrCur = @indexNrCur +1
  END

  -- delete 10.01.2012 11:49
  set @ColumeName='ObjectIrType'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      set @IndexName = 'Objects_tbl_IDX43'
      set @ExecuteSQLstr='DROP INDEX [' + @IndexName + ']  ON [dbo].[' + @MyTableName + ']'
      print (@ExecuteSQLstr)
      execute sp_executesql @ExecuteSQLstr

      print (' --> Delete Column:' + @MyTableName + ':' + @ColumeName + '  [ItemRevision Type from Dataset (new 10.01.2012 J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] DROP Column [' + @ColumeName +']'
      execute (@ExecuteStr)
    end


  -- 22.08.2012/A.Becker: Aendern der PUID FieldSize von 15 auf 32 wenn sie kleiner als 32 ist
  -- 28.11.2017/J.Fes: change PUID FieldSize from 32 to 64 chars -> SAP Mapping
  if (Select Col_length(@MyTableName, 'ObjectPuid')) < 64*2
  BEGIN
     print ('Column:ObjectPuid upsize to 64 char 28.11.2017/J.fes')
     DROP INDEX [Objects_tbl_IDX20] ON [dbo].[Objects_tbl]
     ALTER TABLE [dbo].[Objects_tbl]
     ALTER COLUMN [ObjectPuid] nvarchar(64) COLLATE Latin1_General_CS_AS NULL
     -- rem 31.03.2013/J.Fes umgestellt auf: Latin1_General_CS_AS beruecksichtigen der Gross/Klein Schreibung
  END
  
  -- 22.08.2012/A.Becker: Neu Feld 
  set @ColumeName='ObjectFs_SizeBytes'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [FileSize in Bytes (new 22.08.2012)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] bigint NULL'
      execute (@ExecuteStr)
    end

  -- 08.11.2012 17:09 J.Fes
  -- 'bigint  if (Select Col_length('Objects_tbl',@ColumeName)) <> 34*2
  set @ColumeName='ObjectFs_SizeBytes'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 4
    begin
      print ('Update Column:' + @ColumeName + '  [DataType int to bigint (change 08.11.2012 J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ALTER COLUMN [' + @ColumeName +'] bigint NULL'
      execute (@ExecuteStr)
    end


  -- 22.08.2012/A.Becker: Neu Feld 
  set @ColumeName='ObjectFs_HashCode'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [HashCode des Files (new 22.08.2012)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] [nvarchar] (64) NULL'
      execute (@ExecuteStr)
    end

  -- 22.08.2012/A.Becker: Neu Feld
  set @ColumeName='ObjectUgMaster_CompCountUnique'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [Anzahl eindeutiger Komponenten (new 22.08.2012)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] int NULL'
      execute (@ExecuteStr)
    end


  set @ColumeName='ObjectCADVersion'
  if ((Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) > 0) and
     ((Select Coalesce(Col_length(@MyTableName,'ObjectSaveVersion'),0)) > 0)
    begin
      -- 10.04.2013 14:26 / J.Fes CleanUp ObjectSaveVersion
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] DROP Column[' + @ColumeName +']'
      execute (@ExecuteStr)
    end

  -- rename 05.04.2013 18:47 J.Fes von ObjectCADVersion --> ObjectSaveVersion
  set @ColumeNameOld='ObjectCADVersion'
  set @ColumeNameNew='ObjectSaveVersion'
  if not (Select Coalesce(Col_length('Objects_tbl',@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
      set @ExecuteStr='sp_rename ' + '''' + '[dbo].[Objects_tbl].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteStr)
      execute (@ExecuteStr)
    end


  -- 22.08.2012/A.Becker: Neu Feld
  set @ColumeName='ObjectSaveVersion'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [CAD Version des Objektes (new 22.08.2012)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] [nvarchar] (64) NULL'
      execute (@ExecuteStr)
    end
  
  -- Colume ObjectSaveVersion length von 32 -> 64 vergroesseern (07.05.2019 S.Gueth)
  set @ColumeName='ObjectSaveVersion'
  if (Select Col_length('Objects_tbl',@ColumeName)) <> 64*2
  begin
     print ('Column:' + @ColumeName + ' upsize to 64 char 07.05.2019')
     ALTER TABLE [dbo].[Objects_tbl]
     ALTER COLUMN [ObjectSaveVersion] nvarchar(64) COLLATE Latin1_General_CI_AS
  end

  -- 31.03.2013/J.Fes umgestellt auf: Latin1_General_CS_AS beruecksichtigen der Gross/Klein Schreibung
  set @ColumeName='ObjectPuid'
  if (
      SELECT collation_name
      FROM sys.columns
      WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM sys.objects WHERE type = 'U' AND name = @MyTableName)
      AND name = @ColumeName
      )<> "Latin1_General_CS_AS"
   BEGIN
      print ('Update Column:ObjectPuid set COLLATE Latin1_General_CS_AS #new 31.03.2013/J.Fes')
      DROP INDEX [Objects_tbl_IDX20] ON [dbo].[Objects_tbl]
      ALTER TABLE [dbo].[Objects_tbl]
      ALTER COLUMN [ObjectPuid] nvarchar(64) COLLATE Latin1_General_CS_AS NULL
   END


  -- 01.04.2013/J.Fes: Neu Feld
  set @ColumeName='ObjectPrjIds'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [Prj Id Liste des Objektes (new 01.04.2013)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] [nvarchar] (255) NULL'
      execute (@ExecuteStr)
    end

  -- 16.08.2013/J.Fes: New
  set @ColumeName='ObjectExportRecord'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [ObjectExportRecord Liste des Objektes (new 16.08.2013)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] [nvarchar] (255) NULL'
      execute (@ExecuteStr)
    end


  set @ColumeName='ObjectExportRecordDate'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [ObjectExportRecord Last Export Date (new 20.08.2013)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] [datetime]'
      execute (@ExecuteStr)
    end

   -- Colume ObjectName length von 64 -> 128 vergroesseern (25.10.2017 S.Gueth)
  set @ColumeName='ObjectName'
  if (Select Col_length('Objects_tbl',@ColumeName)) <> 128*2
  begin
     print ('Column:' + @ColumeName + ' upsize to 128 char 25.10.2017 ')
     print ('Column:' + @ColumeName + ' DROP INDEX Objects_tbl_IDX1 add 17.10.2018/J.Fes')
     DROP INDEX [Objects_tbl_IDX1] ON [dbo].[Objects_tbl]
     ALTER TABLE [dbo].[Objects_tbl]
     ALTER COLUMN [ObjectName] nvarchar(128) COLLATE Latin1_General_CI_AS
  end

  -- Colume ObjectName2 length von 64 -> 128 vergroesseern (25.10.2017 S.Gueth)
  set @ColumeName='ObjectName2'
  if (Select Col_length('Objects_tbl',@ColumeName)) <> 128*2
  begin
     print ('Column:' + @ColumeName + ' upsize to 128 char 25.10.2017')
     DROP INDEX [Objects_tbl_IDX23] ON [dbo].[Objects_tbl]
     ALTER TABLE [dbo].[Objects_tbl]
     ALTER COLUMN [ObjectName2] nvarchar(128) COLLATE Latin1_General_CI_AS
  end

  -- #New: 06.08.2014 /J.Fes
  set @ColumeName='ObjectUgMaster_Lev1CompN'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [#Doc:Contains how many (n) Componentes on Lev1 is inc. in Assembly-BOM (new 06.08.2014/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] int NULL'
      execute (@ExecuteStr)
    end

  -- #New: 06.08.2014 /J.Fes
  set @ColumeName='ObjectUgMaster_Lev1CompUniqueN'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [#Doc:Contains how many unic (n) Componentes on Lev1 is inc. in Assembly-BOM (new 06.08.2014/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] int NULL'
      execute (@ExecuteStr)
    end

  -- #New: 06.08.2014 /J.Fes
  set @ColumeName='ObjectUgMaster_ApBOM_isPrecise'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [#Doc:Contains if AP BOM is Precise (new 06.08.2014/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] bit NULL'
      execute (@ExecuteStr)
    end


  -- Colume ImportObjectErr lengs von 64 -> 255 vergroesseern (14.08.2014 20:12 J.Fes)
  if (Select Col_length(@MyTableName,'ImportObjectErr')) <> 255*2
  begin
     print ('Column:ImportObjectErr upsize to 255 char')
     ALTER TABLE [dbo].[Objects_tbl]
     ALTER COLUMN [ImportObjectErr] nvarchar(255) COLLATE Latin1_General_CI_AS
  end


   -- #New: 03.06.2015 11:46 /J.Fes
  set @ColumeName='ObjectIsIrLatest'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [#Doc:Contains if Obj is ObjectIsIrLatest (new 03.06.2015/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] bit NULL'
      execute (@ExecuteStr)
    end

  -- #New: 03.06.2015 11:46 /J.Fes
  set @ColumeName='ObjectIrLatestRank'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [#Doc:Contains Data ObjectIrLatestRank (new 03.06.2015/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] int NULL'
      execute (@ExecuteStr)
    end

  -- #New: 03.06.2015 11:46 /J.Fes  #Removed 09.06.2015/J.Fes
  set @ColumeName='ObjectIsIrLatestWorking'
  if ((Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) > 0)
    begin
      -- 10.04.2013 14:26 / J.Fes CleanUp ObjectSaveVersion
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] DROP Column[' + @ColumeName +']'
      execute (@ExecuteStr)
    end
--   if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
--     begin
--       print ('Create Column:' + @ColumeName + ' [#Doc:Contains if Obj is ObjectIsIrLatest Working (new 03.06.2015/J.Fes)]')
--       set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] bit NULL'
--       execute (@ExecuteStr)
--     end


  -- #Change: 16.09.2015 16:12/J.Fes
  if (Select Col_length(@MyTableName,'ObjectGroup')) <> 255*2
  begin
     print ('Table:' + @MyTableName + ' // Column:ObjectGroup upsize to 255 char (16.09.2015/J.Fes)')
     DROP INDEX [Objects_tbl_IDX18] ON [dbo].[Objects_tbl]
     ALTER TABLE [dbo].[Objects_tbl]
     ALTER COLUMN [ObjectGroup] nvarchar(255)
  end

  -- ergaenzt 26.02.2016 13:47
  set @ColumeName='ProcStateColMc'
  if (Select Coalesce(Col_length('Objects_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [TC MultiSite Check (new 26.02.2016)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] [nvarchar] (34) NULL'
      execute (@ExecuteStr)
    end

  -- 27.04.2016/J.Fes Upsize from 34 to 255
  set @indexNrCur = 1
  WHILE @indexNrCur <= @ObjectAttrsN
  BEGIN
   set @indexNrStr=right('00' + convert(nvarchar,@indexNrCur),2)
   set @ColumeName='ObjectAttr' + @indexNrStr
   set @IndexName = 'Objects_tbl_IDX_ObjectAttr' + @indexNrStr
   if (Select Col_length(@MyTableName,@ColumeName)) <> 255*2
     begin
        print ('    Table:' + @MyTableName + ' // Colume:'  + @ColumeName +  ' upsize from 32 to 255 nvarchar (27.04.2016/J.Fes)')
        If Exists (select Name from sysindexes where Name = @IndexName)
        BEGIN
           print ('     |-> remove Index.: ' + @IndexName )
           set @ExecuteStr='DROP INDEX [' + @IndexName + '] ON [dbo].[Objects_tbl]'
           execute (@ExecuteStr)
        END

        print ('     |-> UpSize Colume: ' + @ColumeName + 'nvarchar(255)'  )
        set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl]' +
                        ' ALTER COLUMN [' + @ColumeName + '] nvarchar(255)'
        execute (@ExecuteStr)
     end
   set @indexNrCur = @indexNrCur +1
  END

  -- #New: 27.05.2016/J.Fes
  set @ColumeName='ObjectDs_Ver'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [#Doc:Contains current file version number of Dataset (#New 27.05.2016/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] int NULL'
      execute (@ExecuteStr)
    end

  -- #New: 27.05.2016/J.Fes
  set @ColumeName='ObjectDs_VerKeepLimt'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [#Doc:Contains Dataset KeepLimit (#New 27.05.2016/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] int NULL'
      execute (@ExecuteStr)
    end

  -- #New: 27.05.2016/J.Fes
  set @ColumeName='ObjectDs_VersN'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [#Doc:Contains how many Dataset Versions are exist (#New 27.05.2016/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] int NULL'
      execute (@ExecuteStr)
    end

  -- #New: 17.06.2016/J.Fes
  set @ColumeName='ObjectSyncTrigger'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [#Doc:Contains ObjectSyncTrigger (#New 27.05.2016/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] int NULL'
      execute (@ExecuteStr)
    end
    
    -- #New: 20.08.2019 S.Gueth
  set @ColumeName='MultiCadAP'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [#Doc:Add MultiCadAP (#New: 20.08.2019 S.Gueth)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] nvarchar(255)'
      execute (@ExecuteStr)
    end
    
     -- #New: 20.08.2019 S.Gueth
  set @ColumeName='MultiCadItemRevs'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [#Doc:Add MultiCadItemRevs (#New: 20.08.2019 S.Gueth)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] nvarchar(max)'
      execute (@ExecuteStr)
    end

  -- #New 11.01.2019/J.Fes
  set @ColumeName='ObjectReleaseState'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) <> 255*2
  BEGIN
   set @IndexName = 'Objects_tbl_IDX31'
   print ('    Table:' + @MyTableName + ' // Colume:'  + @ColumeName +  ' upsize from 32 to 255 nvarchar (11.01.2019/J.Fes)')
   If Exists (select Name from sysindexes where Name = @IndexName)
   BEGIN
      print ('     |-> remove Index.: ' + @IndexName )
      set @ExecuteStr='DROP INDEX [' + @IndexName + '] ON [dbo].[Objects_tbl]'
      execute (@ExecuteStr)
   END

   print ('     |-> UpSize Colume: ' + @ColumeName + 'nvarchar(255)'  )
   set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl]' +
                   ' ALTER COLUMN [' + @ColumeName + '] nvarchar(255)'
   execute (@ExecuteStr)
  END

goto CheckCreateIndex

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

  EXECUTE JF_PrimKeyCheckCreateDef 'Objects_tbl_pk',   @MyTableName, 'Id'
  EXECUTE JF_IndexCheckCreateDef   'Objects_tbl_IDX1', @MyTableName, 'ObjectName'

  --- ReAdd 26.07.2010 18:41 J.Fes Wichtig !!!
  EXECUTE JF_IndexCheckCreateDef   'Objects_tbl_IDX2', @MyTableName, '[ObjectCliName], [ObjectFromSiteID], [ObjectClass]'
  EXECUTE JF_IndexCheckCreateDef   'Objects_tbl_IDX3', @MyTableName, 'ObjectFromSiteID'
  EXECUTE JF_IndexCheckCreateDef   'Objects_tbl_IDX4', @MyTableName, 'ObjectClass'

  -- #UPD: 06.08.2014 / J.Fes
  -- #Step: create index for ProcStateColRf ProcStateCol01..20
  set @indexNrCur = 1
  WHILE @indexNrCur <= @ProcStatColsN
  BEGIN
    set @indexNrStr=right('00' + convert(nvarchar,@indexNrCur),2)
    set @ColumeName='ProcStateCol' + @indexNrStr
    set @IndexName = 'Objects_tbl_IDX_PsCol' + @indexNrStr
    -- Create Index: (neue Methode 28.01.2011]
    EXECUTE JF_IndexCheckCreateDef @IndexName, @MyTableName, @ColumeName
    set @indexNrCur = @indexNrCur +1
  END

  -- #LUp: ProcStateColRf angepasst 23.11.2011 J.Fes
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX_PsColRf', @MyTableName, 'ProcStateColRf'
  -- #New: 26.02.2016/J.Fes add for Multisite Check
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX_PsColMc', @MyTableName, 'ProcStateColMc'


  -- #New: 06.08.2014 / J.Fes
  -- #Step: create index for  ObjectsAttr01..10
  set @indexNrCur = 1
  WHILE @indexNrCur <= @ObjectAttrsN
  BEGIN
    set @indexNrStr=right('00' + convert(nvarchar,@indexNrCur),2)
    set @ColumeName='ObjectAttr' + @indexNrStr
    set @IndexName = 'Objects_tbl_IDX_ObjectAttr' + @indexNrStr
    EXECUTE JF_IndexCheckCreateDef @IndexName, @MyTableName, @ColumeName
    set @indexNrCur = @indexNrCur +1
  END



  -- Create Index: (neue Methode 28.01.2011]
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX17', @MyTableName, 'ObjectOwner'
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX18', @MyTableName, 'ObjectGroup'
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX19', @MyTableName, 'ObjectLastModUser'

  -- 30.08.2019/J.Fes A.Becker A.Stein
  EXECUTE JF_IndexDelete 'Objects_tbl_IDX20', @MyTableName,  'ObjectPuid', 'NO'
  -- 30.08.2019/J.Fes siehe JobMgr_30_ApplProceduresObjects.sql  299 - 307 SELECT @Id_r=Objects_tbl.Id ..
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX20_V2', @MyTableName, '[ObjectPuid],[ObjectFromSiteID],[ObjectClass]'


  -- #New 13.07.2010 19:10 siehe auch Objects_tbl_Object_GetDS
  -- #Update 13.06.2014 18:45 Objects_tbl_IDX21 wurde nicht erzeugt korriegiert
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX21', @MyTableName, '[ObjectFromSiteID] ASC, [ObjectPuid] ASC'
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX22', @MyTableName, 'ObjectDescription'
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX23', @MyTableName, 'ObjectName2'
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX25', @MyTableName, 'ObjectFs_Vol'

  -- rem New 26.08.2011 17:41 geting faster HitCounts
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX30', @MyTableName, 'ObjectFs_NE'
  -- rem New 26.08.2011 17:42 geting ObjectReleaseState
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX31', @MyTableName, 'ObjectReleaseState'

  -- 18.10.2011 21:03 Optimice Get JobPaket Rf AP
  -- EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX40', @MyTableName, '[ObjectUgMaster_APLevels] ASC, [ObjectUgMaster_CompCount] ASC, [ObjectLastModDate] DESC'
  -- 11.06.2015/J.Fes ObjectCreateDate ObjectLastModDate
  EXECUTE JF_IndexDelete 'Objects_tbl_IDX40', @MyTableName,  'ObjectUgMaster_APLevels.._CompCount..ObjectCreateDate', 'NO'
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX40_V2', @MyTableName, '[ObjectUgMaster_APLevels] ASC, [ObjectUgMaster_CompCount] ASC, [ObjectLastModDate] DESC'

--   EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX40_AP3', @MyTableName,
--   '[ObjectType] ASC, [ObjectFromSiteID] ASC, [ObjectClass] ASC, [ObjectUgMaster_Type] ASC, [ObjectIrLatestRank] DESC, [ObjectUgMaster_APLevels] ASC, [ObjectUgMaster_CompCount] ASC, [ObjectLastModDate] DESC'
  -- #New: 11.06.2015/J.Fes + A.Becker
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX40_AP1', @MyTableName,
  '[ObjectType] ASC, [ObjectFromSiteID] ASC, [ObjectClass] ASC, [ObjectUgMaster_Type] ASC, [ObjectUgMaster_APLevels] ASC, [ObjectUgMaster_CompCount] ASC'

  -- #New: 11.06.2015/J.Fes + A.Becker
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX40_AP2', @MyTableName,
  '[ObjectType] ASC, [ObjectFromSiteID] ASC, [ObjectClass] ASC, [ObjectUgMaster_Type] ASC, [ObjectIrLatestRank] DESC, [ObjectUgMaster_APLevels] ASC, [ObjectUgMaster_CompCount] ASC'

  -- #New: 11.06.2015/J.Fes + A.Becker
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX40_SP1', @MyTableName,
  '[ObjectType] ASC, [ObjectFromSiteID] ASC, [ObjectClass] ASC, [ObjectUgMaster_Type] ASC, [ObjectLastModDate] DESC'

  -- #New: 11.06.2015/J.Fes + A.Becker
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX40_SP2', @MyTableName,
  '[ObjectType] ASC, [ObjectFromSiteID] ASC, [ObjectClass] ASC, [ObjectUgMaster_Type] ASC, [ObjectIrLatestRank] DESC, [ObjectLastModDate] DESC'

  -- New 21.10.2011 21:13  Optimice Get JobPaket SP
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX41', @MyTableName, '[ObjectCreateDate] DESC'

  -- New 13.12.2011 21:35  Optimice Get JobPaket SP
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX42', @MyTableName, '[ObjectUgMaster_FsHitCount]'

  -- #New: 28.08.2013 21:50 J.Fes
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX43', @MyTableName, '[ObjectExportRecordDate] DESC'

  -- New 21.10.2011 21:13  Optimice search via  ObjectLastModDate
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX44', @MyTableName, '[ObjectLastModDate] DESC'



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

GO
