Print ''                
Print '-------------------------------------------------------------------------'
Print ' using Database: PLMPerfAnalyse'
Print ' Table         : [tblPingMess]'
Print ' Creation Date : 13.11.2012'
Print ' Autor         : S.G?th'
Print '-------------------------------------------------------------------------'

use [PLMPerfAnalyse]

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

declare @indexNrCur  int
declare @indexNrEnd  int
declare @indexNrStr       varchar(32)
declare @IndexName        varchar(max)
declare @ProcStateColName varchar(max)
set @indexNrEnd = 10


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

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

  Print (@MyActionMsg1)


  CREATE TABLE tblPingMess
  (
          [PingMessID]        int NOT NULL,
          [DateTimeStamp]     datetime2 NOT NULL,
          [MesslinieID]       int NOT NULL,
          [PingSent]          int NOT NULL,
          [PingRecv]          int NOT NULL,
          [PingLost]          int NOT NULL,
          [PingMin]           int NOT NULL,
          [PingMax]           int NOT NULL,
          [PingAvg]           int NOT NULL,
          CONSTRAINT tblPingMess_PK PRIMARY KEY ([PingMessID])
  )
  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)


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




goto CheckCreateIndex

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

  EXECUTE JF_PrimKeyCheckCreateDef 'tblPingMess_pk',                    @MyTableName, 'PingMessID'
  EXECUTE JF_IndexCheckCreateDef   'tblPingMess_IDX_DateTimeStamp',     @MyTableName, 'DateTimeStamp'
  EXECUTE JF_IndexCheckCreateDef   'tblPingMess_IDX_MesslinieID',       @MyTableName, 'MesslinieID' 

--------------------------------------------------------------------------------
EndeExit:
 Print ('   |-> Done on Database: PLMPerfAnalyse')
 Print '------------------------------------------------------------------------'

GO
