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

use [#DB_N#]

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

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


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

  -- Info: bei Struktur anpassungen GitterVar_Cls.ReplaceTextString beachten
  CREATE TABLE [dbo].[JobComputers_tbl] (
          [Id] [uniqueidentifier] NOT NULL ,
          [ComputerName]         [nvarchar]  (64) ,
          [ComputerFQDN]         [nvarchar]  (256) , -- Full Qualified ComputerDomainName (new 09.07.2010)
          [ComputerAktiv]        [bit] NULL ,
          [TceSiteId_Default_id_IsRefTo_Sites_tbl_id] [nvarchar] (64) ,
          [TceUser]              [nvarchar] (32) ,
          [TcePassword]          [nvarchar] (32) ,
          [TimeSchedule]         [nvarchar] (max) ,
          [JobStateInfoCur]      [nvarchar] (max) ,  -- stellt den aktuellen/letzte JobStatus Info des JobCLinets dar neu 16.01.2008
          [LastConnectionDateTimeLocal]   [datetime] NULL ,
          [LastConnectionDateTimeGMT]     [datetime] NULL ,
          -- [LastConnectionDateTimeGMT_DIF]  AS  Right('0000' + CONVERT(varchar,FLOOR(DateDiff(ss, LastConnectionDateTimeGMT,GetDate())/60)),4) + ' [min]:' + Right('0'+CONVERT(varchar,DateDiff(ss, LastConnectionDateTimeGMT,GetDate())-FLOOR(DateDiff(ss, LastConnectionDateTimeGMT,GetDate())/60)*60),2) + ' [sec]' ,
          [OS]                   [nvarchar] (max) ,
          [OsUser]               [nvarchar] (32) ,
          [OsPassword]           [nvarchar] (32) ,
          [OSDestrib_DP]         [nvarchar] (max) , -- Destrib Path
          [Memory]               [nvarchar] (32) ,
          [CPUMhz]               [nvarchar] (32) ,
          [IP]                   [nvarchar] (32)  NULL ,
          [ComputerDescription]  [nvarchar] (128) ,
          [ComputerAssignment]   [nvarchar] (32)  , -- Server Client (Zuteilung {f}; Zuweisung {f}; Verwendung {f}  assignment  )
          [SelObjectsBy_Volume]  [nvarchar] (max) , -- JobComputer bezogener Volumefilter (wieder eingebaut am 01.11.2007 18:48:36)
          [NxVersion]            [nvarchar] (128) ,
          [TceVersion]           [nvarchar] (128) ,
          [Place]                [nvarchar] (64) ,
          [Notice]               [nvarchar] (max) ,
          [Group]                [nvarchar] (32) ,    -- Group
          [Order]                [nvarchar] (32) ,    -- Reihenfolge
          [Custom01]             [nvarchar] (64) ,
          [Custom02]             [nvarchar] (64) ,
          [Custom03]             [nvarchar] (64) ,
          [Custom04]             [nvarchar] (64)
  ) ON [PRIMARY]

  goto CheckCreateIndex


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

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

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


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

  -- 09.07.2010 21:25 - umbennenen von User..Custom
  set @ColumeNameOld='User01'
  set @ColumeNameNew='Custom01'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
      -- EXEC sp_rename '[dbo].[TaskScheduler_tbl].[Active]','Aktive','COLUMN'
      set @ExecuteStr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteStr)
      execute (@ExecuteStr)
    end
  set @ColumeNameOld='User02'
  set @ColumeNameNew='Custom02'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
      -- EXEC sp_rename '[dbo].[TaskScheduler_tbl].[Active]','Aktive','COLUMN'
      set @ExecuteStr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteStr)
      execute (@ExecuteStr)
    end
  set @ColumeNameOld='User03'
  set @ColumeNameNew='Custom03'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
      -- EXEC sp_rename '[dbo].[TaskScheduler_tbl].[Active]','Aktive','COLUMN'
      set @ExecuteStr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteStr)
      execute (@ExecuteStr)
    end
  set @ColumeNameOld='User04'
  set @ColumeNameNew='Custom04'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
      -- EXEC sp_rename '[dbo].[TaskScheduler_tbl].[Active]','Aktive','COLUMN'
      set @ExecuteStr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteStr)
      execute (@ExecuteStr)
    end

goto CheckCreateIndex

  CheckCreateIndex:

  If NOT Exists (select Name from sysindexes where Name = 'JobComputers_tbl_pk')
  begin
     print (' --> create index: JobComputers_tbl_pk')
     ALTER TABLE [dbo].[JobComputers_tbl]
       ADD CONSTRAINT [JobComputers_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 = 'JobComputers_tbl_uq')
  begin
     Print(' --> create index: JobComputers_tbl_uq')
     ALTER TABLE [dbo].[JobComputers_tbl]
        ADD CONSTRAINT [JobComputers_tbl_uq]
        UNIQUE NONCLUSTERED ([ComputerName])
        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 (@MyActionMsg1)
 Print ('   |-> Done on Database: #DB_N#')
 Print '------------------------------------------------------------------------'

GO
