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

use [#DB_N#]


Declare @MyActionMsg1 nvarchar (max)
Declare @MyTableName nvarchar (max)
    set @MyTableName='Sites_tbl'

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

--------------------------------------------------------------------------------
TableCreate:
  set @MyActionMsg1=
      '-- Table: ' + @MyTableName + ' dose not exist and will be created new'
  Print (@MyActionMsg1)
  CREATE TABLE [dbo].[Sites_tbl] (
        [Id]                            [uniqueidentifier] NOT NULL ,
        [DataSourceType]                [nvarchar] (255) NULL ,       -- def. DataSourceType Values NotSet / TC / NativFileSys / SAP (#New: 08.11.2017 / J.Fes)

        [pSite_ID]                      [nvarchar] (50)  NOT NULL ,   -- ReferenzNummer in Oracle DB 99899900
        [SiteAktive]                    [bit]            NULL     ,   -- ist diese Site aktive Ja/Nein
        [SiteID]                        [nvarchar] (50)  NOT NULL ,   -- JobMgr interne kuerzel für die TCE DB z.B. TCNG
        [pName]                         [nvarchar] (255) ,            -- bezeichung der DB "TEST System 123"
        [DBNetworkAlias]                [nvarchar] (50) ,             -- Oracle Network Alias
        [UserName]                      [nvarchar] (32) ,             -- DB UserName
        [Password]                      [nvarchar] (128) NULL ,       -- DB Pw
        [OracleHome]                    [nvarchar] (255) NULL ,
        [OracleBin]                     [nvarchar] (255) NULL ,
        [OracleTblNameInfodbaReplace]   [nvarchar] (32) NULL ,        -- 07.03.2011 13:03 new definiert den replace fuer INFODBA.

        [DBProvider]                    [nvarchar] (255) NULL ,       -- SqlClient= System.Data.SqlClient OracleClient=System.Data.OracleClient
        [DBConnectionString]            [nvarchar] (max) NULL ,       -- Data Source=DbServerName\InstanceName;Initial Catalog=CatalogName;User ID=#UserName#;Password=#Password#;Connection Timeout=30;Persist Security Info=True;
        [DbConnectionTimeout]           [int] NULL ,                  -- neu (02.11.2009)
        [DbCommandTimeout]              [int] NULL ,                  -- neu (02.11.2009)

        [Tns_Admin]                     [nvarchar] (255) NULL ,

        [TCArchitecture]                [nvarchar] (255) NULL ,       -- def. TCArchitecture Values NotSet / TCUA / TCEng (#New: 29.03.2013 / J.Fes)

        [DbServerName]                  [nvarchar] (255) NULL ,       -- #New 17.04.2017/J.Fes used for MSSQL-Server
        [DbInstanceName]                [nvarchar] (255) NULL ,       -- #New 17.04.2017/J.Fes used for MSSQL-Server


        [ImportItemSearchNames]         [nvarchar] (max) NULL ,       -- aenderugn von 255 auf max Date 08.11.2011
        [ImportGroupSearchNames]        [nvarchar] (max) NULL ,       -- neu (08.11.2011)

        [ImportIrLastModDateBegin]      [Datetime] NULL ,             -- Datum ab dem beim naechsten Site.Imp via Ir.LastMod fortgefahren wird
                                                                      -- 28.04.2016/J.Fes Rename: from:ImportItemLastModifyDateBegin to:ImportIrLastModDateBegin
        [ImportIrLastModDateEnd]        [Datetime] NULL ,             -- 28.04.2016/J.Fes Rename: from:ImportItemLastModifyDateEnd   to:ImportIrLastModDateEnd

        [ImportIrLastReleaseDateBegin]  [Datetime] NULL ,             -- #New:28.04.2016/J.Fes contols date where next Site.Imp starts via Ir.LastRelease
        [ImportDsLastModDateBegin]      [Datetime] NULL ,             -- #New:31.07.2018/J.Fes contols date where next Site.Imp starts via Ds.LastRelease


        [ImportAssembMaxComp]           [int] NULL ,
        [ImportAssembMaxLev]            [int] NULL ,
        [ImportAssembMaxTimeSec]        [int] NULL ,                  -- maximale Dauer eines Assembly Importes (neu 19.3.08)

        [ImportMsgAfterNImports]        [int] NULL ,                  -- nach vievielen Imports soll eine Ausgabe im InfoFenster ausgegeben werden (neu 19.3.08)
        [ImportLevelType]               [nvarchar] (10) ,             -- ImportlevelType (10=full) (11=fullUpdate) (20=medium) (31=quick1) (32=quick2)
        [ImportReadnew]                 [bit] NULL ,                  -- sollen die Daten neu gelesen werden neu 21.11.2007

        [ImportPrjData]                 [bit] NULL ,                  -- sollen die Prj Daten gelesen werden neu 02.04.2013/J.Fes
        [ImportItemDataAll]             [bit] NULL ,                  -- sollen die Item Daten gelesen werden #New 16.08.2013/J.Fes
        [ImportMultiSiteData]           [bit] NULL ,                  -- wird in dieser DB TC Multisite verwendet (Default = YES) #New: 21.08.2013/J.Fes
        [ImportFs_Properties]           [bit] NULL ,                  -- soll die File Properties (Default = YES) #New: 26.08.2013/J.Fes

        [ImportDataCleanup]             [bit] NULL ,                  -- sollen die Daten bereinigt werden ? #New 27.03.2013/J.Fes

        [ImportDBReconnetWaitTime_mSec] [int] NULL ,                  -- DB Reconnect wait time (new 27.12.2008)
        [ImportDBReconnetRetrysMax]     [int] NULL ,                  -- DB Reconnect N Retrys  (new 27.12.2008)

        [DataSyncStartDateTime]         [Datetime] NULL ,             -- new (13.06.2011) controls DataSearch.DateTime for Register:"Sites/Data Import" Buttom:"Syn.Site.Data

        [NativeRoot_DP]                 [nvarchar] (255) ,            -- Rootpath für den Import
        [NativeFileMaskInclude]         [nvarchar] (255) ,            -- Einschluss Liste *.Prt
        [NativeFileMaskExclude]         [nvarchar] (255) ,            -- Ausschluss Liste *XYZ*.prt
        [NativeMaskAp]                  [nvarchar] (255) ,            -- Kennzeichner für Assemply Parts
        [NativeWriteNxBomFile]          [bit] NULL ,                  -- Native Import - Soll eine .txt Datei mit der NxBOM generiert werden? #New 31.10.2019/A.Stein
        [NativeNxBomRootDir]            [nvarchar] (255) ,            -- Rootpath zur ablage der NxBOM Dateien #New 31.10.2019/A.Stein
        [NativeBomReadFromCache]        [bit] NULL ,                  -- Soll die BOM Struktur aus dem Cache oder aus dem .prt file gelesen werden? #New 31.10.2019/A.Stein

        [Group]                         [nvarchar] (32) ,             -- Org.Group
        [Note]                          [nvarchar] (255) ,            -- Org.Note
        [JobControlActivInfo]           [nvarchar] (max) ,            -- #New:31.10.2018/S.Gueth, stores last JobControlActivStatus Rule information

        [ImportTcIrSearchMethode]       [nvarchar] (10) ,             -- ImportTcIrSearchMethode 1 = IrsAll 2 = IrsOnlyContainigUGMASTER  (J.Fes new 08.07.2010)
        [ImportTcObjectFs_DPNE]         [bit] NULL ,                  -- sollen die ObjectFs_DPNE Daten importiert werden ? (J.Fes new 08.07.2010)
        [ImportTcObjectFs_NE]           [bit] NULL ,                  -- sollen die ObjectFs_NE Daten importiert werden ? (J.Fes new 08.07.2010)
        [ImportApWithCompCound0AsSp]    [bit] NULL ,                  -- sollen Baugruppen die eine Bom besitzen aber 0 Komponenten haben als Sp gewertet werden (J.Fes new 10.08.2010 09:40)
        [ImportTcApBomLineUgGometryNo_DoCount] [bit] NULL,            -- sollen die BOM Lines die Ug Geometrie=No haben mit gezaehlt werden? (J.Fes neu 08.11.2011)
                                                                      -- 18.07.2014/JFes not any more in use
        [ObjectAttrDefs]                [xml] NULL ,                  -- #New 07.08.2014 / J.Fes

        [Config_IrRevLatestRule]        [nvarchar] (32) NULL,         -- #New:03.06.2015  IrRevLatestRule
        [Config_BOMPreciseRule]         [nvarchar] (32) NULL,         -- #New:03.06.2015  BOMPreciseRule
        [Import_Settings3_xml]          [xml] NULL,                   -- #New:28.04.2016/J.Fes
        [SAPSystemSettings_xml]         [xml] NULL,                   -- #New:08.11.2017/J.Fes
        [GenericSystemSettings_xml]     [xml] NULL                    -- #New:08.05.2019/A.Stein

       ) ON [PRIMARY]

  goto CheckCreateIndex
--------------------------------------------------------------------------------
TableUpdate:

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

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

  -- Alle Updates hierher
  set @ColumeName='ImportAssembMaxTimeSec'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [max duration for importing assembly (new 19.3.08)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [int] NULL'
      execute (@ExecuteStr)
    end


  set @ColumeName='ImportMsgAfterNImports'
  if (Select Coalesce(Col_length('Sites_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [after how may Imports done a new Message comes up in the message area (new 19.3.08)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [int] NULL'
      execute (@ExecuteStr)
    end

  /*
  set @ColumeName='ImportObjectNewProcStateMask'
  if (Select Coalesce(Col_length('Sites_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [defines the default status for New Objects (new 15.05.08)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Sites_tbl] ADD [' + @ColumeName +'] [nvarchar] (11) NULL'
      execute (@ExecuteStr)
    end

  set @ColumeName='ImportObjectUpdProcStateMask'
  if (Select Coalesce(Col_length('Sites_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [defines the default status for update Objects (new 15.05.08)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Sites_tbl] ADD [' + @ColumeName +'] [nvarchar] (11) NULL'
      execute (@ExecuteStr)
    end
  */
  -- löschen 27.12.2008
  set @ColumeName='ImportObjectNewProcStateMask'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Delete Column:' + @ColumeName + ' 27.12.2008 using now ProcessGroupDef')
      set @ExecuteStr = 'ALTER TABLE [dbo].[' + @MyTableName + '] DROP COLUMN [' + @ColumeName + ']'
      execute (@ExecuteStr)
    end

  -- löschen 27.12.2008
  set @ColumeName='ImportObjectUpdProcStateMask'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Delete Column:' + @ColumeName + ' 27.12.2008 using now ProcessGroupDef')
      set @ExecuteStr = 'ALTER TABLE [dbo].[' + @MyTableName + '] DROP COLUMN [' + @ColumeName + ']'
      execute (@ExecuteStr)
    end

  -- [DBProvider]     [nvarchar] (255) NULL ,         --  MSSql= System.Data.SqlClient Oracle=System.Data.OracleClient
  set @ColumeName='DBProvider'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [defines the DBProvider (new 19.08.08)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (255) NULL'
      execute (@ExecuteStr)
    end

  -- [DBConnectionString]     [nvarchar] (max) NULL , --  Data Source=DbServerName\InstanceName;Initial Catalog=CatalogName;User ID=#UserName#;Password=#Password#;Connection Timeout=30;Persist Security Info=True;
  set @ColumeName='DBConnectionString'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [defines the DBConnectionString (new 19.08.08)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (max) NULL'
      execute (@ExecuteStr)
    end


  set @ColumeName='ImportDBReconnetWaitTime_mSec'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [DB Reconnect wait time (new 27.12.2008)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [int] NULL'
      execute (@ExecuteStr)
    end

  set @ColumeName='ImportDBReconnetRetrysMax'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [DB Reconnect N Retrys  (new 27.12.2008)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [int] NULL'
      execute (@ExecuteStr)
    end
    
  set @ColumeName='DbConnectionTimeout'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 02.11.2009)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [int] NULL'
      execute (@ExecuteStr)
    end

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


  set @ColumeName='ImportTcIrSearchMethode'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 08.07.2010)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (10) NULL'
      execute (@ExecuteStr)
    end


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

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

  -- sollen Baugruppen 0 Komponenten haben als Sp gewertet werden (J.Fes new 10.08.2010 09:40)
  set @ColumeName='ImportApWithCompCound0AsSp'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 10.08.2010)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [Bit] NULL'
      execute (@ExecuteStr)
    end


  set @ColumeName='OracleTblNameInfodbaReplace'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 07.03.2011 13:04)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (32) NULL'
      execute (@ExecuteStr)
    end

   set @ColumeName='DataSyncStartDateTime'
   if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
     begin
       print ('Create Column:' + @ColumeName +  ' [new (13.06.2011) controls DataSearch.DateTime for Register:Sites/Data Import Buttom:Syn.Site.Data]')
       set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] datetime'
       execute (@ExecuteStr)
     end

  set @ColumeName='ImportItemSearchNames'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) <> -1
  begin
     print ('Column: Sites_tbl.ImportItemSearchNames upsize to max char [Change:08.11.2011 17:25 J.Fes]')
     ALTER TABLE [dbo].[Sites_tbl]
       ALTER COLUMN [ImportItemSearchNames] nvarchar(max) COLLATE Latin1_General_CI_AS
  end



  set @ColumeName='ImportGroupSearchNames'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 08.11.2011 17:21)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (max) NULL'
      execute (@ExecuteStr)
    end

-- 18.07.2014 09:39 entnommen
-- -- sollen die BOM Lines die Ug Geometrie=No haben mit gezaehlt werden? (J.Fes neu 08.11.2011)
--   set @ColumeName='ImportTcApBomLineUgGometryNo_DoCount'
--   if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
--     begin
--       print ('Create Column:' + @ColumeName + ' [(new 10.08.2010)]')
--       set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [Bit] NULL'
--       execute (@ExecuteStr)
--     end

  set @ColumeName='ImportTcApBomLineUgGometryNo_DoCount'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print (' --> Delete Column:' + @ColumeName + '  [ImportTcApBomLineUgGometryNo_DoCount deleted (18.07.2014/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] DROP Column [' + @ColumeName +']'
      execute (@ExecuteStr)
    end


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

  -- TCArchitecture
  set @ColumeName='TCArchitecture'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [defines the TCArchitecture (new 29.03.2013/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (255) NULL'
      execute (@ExecuteStr)
    end


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



  -- ImportItemDataAll
  -- 02.12.2013/J.Fes if exist ImportItemData rename to ImportItemDataAll
  set @ColumeNameOld='ImportItemData'
  set @ColumeNameNew='ImportItemDataAll'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:[' + @ColumeNameOld + '] to:[' + @ColumeNameNew + ']')
      set @ExecuteStr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteStr)
      execute (@ExecuteStr)
    end

  -- Update 12.02.2014 / J.Fes
  set @ColumeName='ImportItemDataAll'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 16.08.2013/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [Bit] NULL'
      execute (@ExecuteStr)
    end

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

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


  set @ColumeName='ObjectAttrDefs'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(#New: 07.08.2014/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [xml] NULL'
      execute (@ExecuteStr)
    end


  set @ColumeName='Config_IrRevLatestRule'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [add colume for Config_IrRevLatestRule (new 03.06.2015/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (255) NULL'
      execute (@ExecuteStr)
    end

  set @ColumeName='Config_BOMPreciseRule'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [add colume for Config_IrRevLatestRule (new 03.06.2015/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (255) NULL'
      execute (@ExecuteStr)
    end

   set @ColumeName='ImportIrLastReleaseDateBegin'
   if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
     begin
       print ('Create Column:' + @ColumeName +  ' [#New:28.04.2016/J.Fes contols date where next Site.Imp starts via Ir.LastRelease]')
       set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] datetime'
       execute (@ExecuteStr)
     end

   -- #LUp: 28.04.2016/J.Fes
   set @ColumeNameOld='ImportItemLastModifyDateBegin'
   set @ColumeNameNew='ImportIrLastModDateBegin'
   if not (Select Coalesce(Col_length(@MyTableName,@ColumeNameOld),0)) = 0
     begin
       print ('rename Column:[' + @ColumeNameOld + '] to:[' + @ColumeNameNew + ']')
       set @ExecuteStr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
       print (@ExecuteStr)
       execute (@ExecuteStr)
     end

   -- #LUp: 28.04.2016/J.Fes
   set @ColumeNameOld='ImportItemLastModifyDateEnd'
   set @ColumeNameNew='ImportIrLastModDateEnd'
   if not (Select Coalesce(Col_length(@MyTableName,@ColumeNameOld),0)) = 0
     begin
       print ('rename Column:[' + @ColumeNameOld + '] to:[' + @ColumeNameNew + ']')
       set @ExecuteStr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
       print (@ExecuteStr)
       execute (@ExecuteStr)
     end

   -- #New: 28.04.2016/J.Fes
   set @ColumeName='Import_Settings3_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] NULL'
      execute (@ExecuteStr)
    end

   --#New 17.04.2017 J.Fes
   set @ColumeName='Password'
   if (Select Col_length(@MyTableName,@ColumeName)) <> 128*2
    BEGIN
     print ('UpSize Column:' + @ColumeName + '  [to 128 char (Update: 17.04.2017)]')
     set @ExecuteStr=  'ALTER TABLE [dbo].[' + @MyTableName + ']' + ' ALTER COLUMN [' + @ColumeName + '] nvarchar(128)'
     execute sp_executesql @ExecuteStr
    END

   --#New 17.04.2017/J.Fes
   set @ColumeName='DbServerName'
   if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    BEGIN
      print ('Create Column:' + @ColumeName + '  [defines DbServerName (new 17.04.2017)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (255) NULL'
      execute (@ExecuteStr)
    END

   --#New 17.04.2017/J.Fes
   set @ColumeName='DbInstanceName'
   if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    BEGIN
      print ('Create Column:' + @ColumeName + '  [defines DbInstanceName used for MSSQL Server connection string (new 17.04.2017)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (255) NULL'
      execute (@ExecuteStr)
    END

  -- #New: 08.11.2017/J.Fes  TC / NativFileSys / SAP
  set @ColumeName='DataSourceType'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [defines the DataSourceType #New: 08.11.2017 J.Fes]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (255) NULL'
      execute (@ExecuteStr)
    end

   -- #New: 08.11.2017/J.Fes
   set @ColumeName='SAPSystemSettings_xml'
   if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' in Table:' + @MyTableName + ' settings for SAP System (#New: 08.11.2017 J.Fes)')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [xml] NULL'
      execute (@ExecuteStr)
    end

   -- #New: 08.05.2019/A.Stein
   set @ColumeName='GenericSystemSettings_xml'
   if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' in Table:' + @MyTableName + ' settings for Generic System (#New: 08.11.2017 J.Fes)')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [xml] NULL'
      execute (@ExecuteStr)
    end

   -- #New: 31.07.2018/J.Fes
   set @ColumeName='ImportDsLastModDateBegin'
   if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
     begin
       print ('Create Column:' + @ColumeName +  ' [#New:31.07.2018/J.Fes contols date where next Site.Imp starts via Ds.LastRelease]')
       set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] datetime'
       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 @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (max)'
      execute (@ExecuteStr)
    end

  -- New 31.10.2019 A.Stein
  set @ColumeName='NativeWriteNxBomFile'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 31.10.2019/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [Bit] NULL'
      execute (@ExecuteStr)
    end

  -- New 31.10.2019 A.Stein
  set @ColumeName='NativeNxBomRootDir'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [defines the DataSourceType #New: 08.11.2017 J.Fes]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (255) NULL'
      execute (@ExecuteStr)
    end
      
  -- New 31.10.2019 A.Stein
  set @ColumeName='NativeBomReadFromCache'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 31.10.2019/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [Bit] NULL'
      execute (@ExecuteStr)
    end
    
goto CheckCreateIndex



CheckCreateIndex:

  If NOT Exists (select Name from sysindexes where Name = 'Sites_tbl_pk')
  begin
     print (' --> create Sites_tbl_pk')
     ALTER TABLE [dbo].[Sites_tbl]
       ADD CONSTRAINT [Sites_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 = 'Sites_tbl_SiteID_uq')
  begin
     print (' --> create Sites_tbl_SiteID_uq')
     ALTER TABLE [dbo].[Sites_tbl]
        ADD CONSTRAINT [Sites_tbl_SiteID_uq]
        UNIQUE NONCLUSTERED ([SiteID])
        WITH (
          FILLFACTOR = 80,
          PAD_INDEX = OFF,
          IGNORE_DUP_KEY = OFF,
          STATISTICS_NORECOMPUTE = OFF,
          ALLOW_ROW_LOCKS = ON,
          ALLOW_PAGE_LOCKS = ON)
  end

  If NOT Exists (select Name from sysindexes where Name = 'Sites_tbl_pSite_ID_uq')
  begin
     print (' --> create Sites_tbl_pSite_ID_uq')
     ALTER TABLE [dbo].[Sites_tbl]
        ADD CONSTRAINT [Sites_tbl_pSite_ID_uq]
        UNIQUE NONCLUSTERED ([pSite_ID])
        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
