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

use [PLMPerfAnalyse]

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

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

  Print (@MyActionMsg1)


  CREATE TABLE [dbo].[tblComputerDetails]
  (
        [ComputerDetailID]      int NOT NULL,
        [ComputerID]            int NOT NULL, 
        [DetailDatum]           datetime2 NOT NULL,
        [OSVersion]             nvarchar(100),
        [IEVersion]             nvarchar( 50),
        [JavaVersion]           nvarchar( 50),
        [TCEVersion]            nvarchar(100),
        [UGVersion]             nvarchar(100),
        [CPUTyp]                nvarchar(100),
        [RAMSizeMB]             int,
        [GFXTyp]                nvarchar(100),
        [GFXBios]               nvarchar( 50),
        [GFXTreiber]            nvarchar(100),
        CONSTRAINT tblComputerDetails_PK PRIMARY KEY ([ComputerDetailID])
  )
  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 'tblComputerDetails_pk',                   @MyTableName, 'ComputerDetailID'
  EXECUTE JF_IndexCheckCreateDef   'tblComputerDetails_IDX_ComputerID',       @MyTableName, 'ComputerID'
  EXECUTE JF_IndexCheckCreateDef   'tblComputerDetails_IDX_DetailDatum',      @MyTableName, 'DetailDatum' 

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

GO
