Print ''                
Print '-------------------------------------------------------------------------'
Print ' using Database: PLMPerfAnalyse'
Print ' Table         : [tblMesslinien]'
Print ' Creation Date : 13.11.2012'
Print ' Autor         : S.Gueth'
Print '-------------------------------------------------------------------------'

use [PLMPerfAnalyse]

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

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

  Print (@MyActionMsg1)


  CREATE TABLE tblMesslinien
  (
          [MesslinieID]         int NOT NULL,
          [MesslinieName]       nvarchar( 50) NOT NULL,
          [Reihenfolge]         int,
          [PerfMessColor]       nvarchar( 20),
          [PingMessColor]       nvarchar( 20),
          [Visible]             int,
          [IncludePingMess]     int,
          [RelatedMesslinieId]  int,
          [UserCountColor]      nvarchar( 20),  -- fuer farbsteuerung der UserCount.Messlinie (#New: 11.01.2013 S.Gueth)
          [UserCountLabel]      nvarchar( 20),  -- fuer Label Text UserCount.Messlinie (#New: 11.01.2013 S.Gueth)
          [IncludeStartzeiten]  int,            -- fuer Startzeienmessung von Teamcenter und NX (#New: 20.03.2013)
          CONSTRAINT tblMesslinien_PK PRIMARY KEY ([MesslinieID])
  )
  ON [PRIMARY]
  
  INSERT INTO tblMesslinien (MesslinieID, MesslinieName, Reihenfolge, PerfMessColor, PingMessColor) VALUES (1, 'Test Messlinie', 0, 'Blue', 'Yellow')



  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='UserCountColor'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
  begin
      print ('Create Column:[' + @ColumeName + '] fuer farbsteuerung der UserCount.Messlinie (#New: 11.01.2013 S.Gueth)'  )
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(20)'
      execute (@ExecuteStr)
  end

  set @ColumeName='UserCountLabel'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
  begin
      print ('Create Column:[' + @ColumeName + '] fuer Label Text UserCount.Messlinie (#New: 11.01.2013 S.Gueth)')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(20)'
      execute (@ExecuteStr)
  end

  set @ColumeName='IncludeStartzeiten'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
  begin
      print ('Create Column:[' + @ColumeName + '] fuer Startzeienmessung von Teamcenter und NX (#New: 20.03.2013)')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] int'
      execute (@ExecuteStr)
  end


goto CheckCreateIndex

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

  EXECUTE JF_PrimKeyCheckCreateDef 'tblMesslinien_pk',           @MyTableName, 'MesslinieID'

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

GO
