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

use [PLMPerfAnalyse]

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

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

  Print (@MyActionMsg1)


  CREATE TABLE tblUserCount
  (
          [UserCountID]         int NOT NULL,
          [DateTimeStamp]       datetime2 NOT NULL,
          [UserCount]           int NOT NULL,
          [MesslinieID]         int,                   -- fuer zuordnung des UserCount zur Messlinie (#New: 11.01.2013 S.Gueth)
          CONSTRAINT tblUserCount_PK PRIMARY KEY ([UserCountID])
  )
  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='MesslinieID'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + + '  [#New: 11.01.2013 S.Gueth fuer zuordnung des UserCount zur Messlinie]' )
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] int'
      execute (@ExecuteStr)
    end




goto CheckCreateIndex

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

  EXECUTE JF_PrimKeyCheckCreateDef 'tblUserCount_pk',                 @MyTableName, 'UserCountID'
  EXECUTE JF_IndexCheckCreateDef   'tblUserCount_IDX_DateTimeStamp',  @MyTableName, 'DateTimeStamp'

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

GO
