Print ''                
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Table         : [tblStartzeiten]'
Print ' Creation Date : 20.03.2013'
Print ' Autor         : S.Gueth'
Print '-------------------------------------------------------------------------'

use [#DB_N#]

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

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

  Print (@MyActionMsg1)

  CREATE TABLE tblStartzeiten
  (
          [STARTZEITID]         int,
          [DATETIMESTAMP]       datetime2,
          [CLIENTNAME]          nvarchar( 25),
          [USERNAME]            nvarchar( 25),
          [SIDT_SECONDS]        FLOAT,
          [TCLOGIN_SECONDS]     FLOAT,
          [NXSTART_SECONDS]     FLOAT,
          [TCDB]                nvarchar( 10),
          [TIERS]               int,
          [MESSLINIEID]         int,
          [RESULTSXML]          nvarchar( 512),
          CONSTRAINT TBLSTARTZEITEN_PK PRIMARY KEY ([STARTZEITID])
  )
  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='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


goto CheckCreateIndex

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

  EXECUTE JF_PrimKeyCheckCreateDef 'TBLSTARTZEITEN_PK',                                   @MyTableName, 'STARTZEITID'
  EXECUTE JF_IndexCheckCreateDef   'TBLSTARTZEITEN_IDX_DATETIMESTAMP',                    @MyTableName, 'DATETIMESTAMP'
  EXECUTE JF_IndexCheckCreateDef   'TBLSTARTZEITEN_IDX_STARTZEITID_DATETIMESTAMP',        @MyTableName, 'STARTZEITID, DATETIMESTAMP'

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

GO
