Print ''                
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Table         : [tblPerfMess]'
Print ' Creation Date : 13.11.2012'
Print ' Autor         : S.Güth'
Print '-------------------------------------------------------------------------'

use [#DB_N#]

Declare @MyActionMsg1 nvarchar (max)
Declare @MyTableName nvarchar (max)
set @MyTableName='tblPerfMess'
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].[tblPerfMess]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  BEGIN
   goto TableUpdate
  END
ELSE
  BEGIN
   goto TableCreate
  END

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

  Print (@MyActionMsg1)


  CREATE TABLE tblPerfMess
  (
          PerfMessID          int NOT NULL,
          MessungID           int NOT NULL,
          DateTimeStamp       datetime2 NOT NULL,
          ComputerID          int NOT NULL, 
          MesslinieID         int NOT NULL,
          MessNummer          int,
          LoadUpdCpuReal      FLOAT(7),
          LoadUpdCpu          FLOAT(7),
          TotalReal           FLOAT(7),
          UGMGRReal           FLOAT(7),
          PDIReal             FLOAT(7),
          SQLReal             FLOAT(7),
          CONSTRAINT tblPerfMess_PK PRIMARY KEY ([PerfMessID])
  )
  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 'tblPerfMess_pk',                                      @MyTableName, 'PerfMessID'
  EXECUTE JF_IndexCheckCreateDef   'tblPerfMess_IDX_DateTimeStamp',                       @MyTableName, 'DateTimeStamp'
  EXECUTE JF_IndexCheckCreateDef   'tblPerfMess_IDX_MessungID_DateTimeStamp',             @MyTableName, 'MessungID, DateTimeStamp' 
  EXECUTE JF_IndexCheckCreateDef   'tblPerfMess_IDX_MessungID_MessungID_DateTimeStamp',   @MyTableName, 'MessungID, MessNummer, DateTimeStamp' 

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

GO
