Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Table         : [JobScript_tbl]'
Print ' Creation Date : 22.03.2006'
Print ' Autor         : J.Feuerstein'
Print ' '
Print '-------------------------------------------------------------------------'

use [#DB_N#]


Declare @MyActionMsg1 nvarchar (max)
--------------------------------------------------------------------------------
-- verzweigen auf TableCreate: oder TableUpdate:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[JobScript_tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  BEGIN
   goto TableUpdate
  END
ELSE
  BEGIN
   goto TableCreate
  END

--------------------------------------------------------------------------------
TableCreate:
  set @MyActionMsg1=
      '-- Table: [JobScript_tbl] dose not exist and will be created new'
  Print (@MyActionMsg1)
  CREATE TABLE [dbo].[JobScript_tbl] (
          [Id]                          [uniqueidentifier] NOT NULL ,
          [JobScriptName]               [nvarchar] (50) ,
          [JobScriptDescription]        [nvarchar] (255) ,
          [JobScriptParameters]         [nvarchar] (MAX) ,  -- JobClientScript Parameters
          [JobScriptParametersExtended] [nvarchar] (MAX) ,  -- New 15.12.2009/J.Fes erweiterte JobScript Parameters
          [JobScript_PPNE]              [nvarchar] (255) ,  -- JobClientScript .cmd tp\JobClientScript_NE
          [JobServerScript_PPNE]        [nvarchar] (255) ,  -- JobServer .Net Script -> excute via Scripts/Param (new 18.08.2009 J.Fes)
          [JobServerScriptParameters]   [nvarchar] (MAX) ,  -- JobServer .Net Script Parameter (new 18.08.2009 J.Fes)
          [Note]                        [nvarchar] (MAX)    -- Notice Feld (new 17.11.2011)
  ) ON [PRIMARY]

  goto CheckCreateIndex


--------------------------------------------------------------------------------
TableUpdate:

  set @MyActionMsg1=
       '-- Table: [JobScript_tbl] exist and will be updated'
  Print (@MyActionMsg1)

  Declare @ColumeName nvarchar (256)
  Declare @ColumeNameOld nvarchar (256)
  Declare @ColumeNameNew nvarchar (256)
  Declare @ExecuteStr nvarchar (max)

  -- Alle Updates hierher
  -- CodeExample: Editieren der Eigenschaften Datentype eines Column
  set @ColumeName='JobScriptParameters'
  if not (Select Coalesce(Col_length('JobScript_tbl',@ColumeName),0)) = 0
    begin
      print (' --> Edit Column:' + @ColumeName)
      set @ExecuteStr='ALTER TABLE [dbo].[JobScript_tbl] ALTER COLUMN [' + @ColumeName +'] [nvarchar](max)'
      execute (@ExecuteStr)
    end


  set @ColumeName='JobPaketGetNextScript_PPNE'
  if not (Select Coalesce(Col_length('JobScript_tbl',@ColumeName),0)) = 0
    begin
--       print (' --> Create Column:' + @ColumeName + '  [JobPacket Script to generate the next  (new 23.6.08 J.Fes)]')
--       set @ExecuteStr='ALTER TABLE [dbo].[JobScript_tbl] ADD [' + @ColumeName +'] [nvarchar](max)'
      print (' --> Delete Column:' + @ColumeName + '  [JobPacket Script to generate the next  (new 21.08.2009 J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[JobScript_tbl] DROP Column [' + @ColumeName +']'
      execute (@ExecuteStr)
    end


  set @ColumeName='JobServerScript_PPNE'
  if (Select Coalesce(Col_length('JobScript_tbl',@ColumeName),0)) = 0
    begin
      print (' --> Create Column:' + @ColumeName + '  [JobServer Script excute via Scripts/Param (new 18.08.2009 J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[JobScript_tbl] ADD [' + @ColumeName +'] [nvarchar](max)'
      execute (@ExecuteStr)
    end
    
  set @ColumeName='JobServerScriptParameters'
  if (Select Coalesce(Col_length('JobScript_tbl',@ColumeName),0)) = 0
    begin
      print (' --> Create Column:' + @ColumeName + '  [JobServer Script Parameter (new 18.08.2009 J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[JobScript_tbl] ADD [' + @ColumeName +'] [nvarchar](max)'
      execute (@ExecuteStr)
    end

  set @ColumeName='JobScriptParametersExtended'
  if (Select Coalesce(Col_length('JobScript_tbl',@ColumeName),0)) = 0
    begin
      print (' --> Create Column:' + @ColumeName + '  (new 15.12.2009 J.Fes)')
      set @ExecuteStr='ALTER TABLE [dbo].[JobScript_tbl] ADD [' + @ColumeName +'] [nvarchar](max)'
      execute (@ExecuteStr)
    end

  set @ColumeName='Note'
  if (Select Coalesce(Col_length('JobScript_tbl',@ColumeName),0)) = 0
    begin
      print (' --> Create Column:' + @ColumeName + '  [Notice Feld (new 17.11.2011 20:39)]')
      set @ExecuteStr='ALTER TABLE [dbo].[JobScript_tbl] ADD [' + @ColumeName +'] [nvarchar](max)'
      execute (@ExecuteStr)
    end




goto CheckCreateIndex

CheckCreateIndex:


  If NOT Exists (select Name from sysindexes where Name = 'JobScript_tbl_pk')
  begin
     print (' --> create index: JobScript_tbl_pk')
     ALTER TABLE [dbo].[JobScript_tbl]
       ADD CONSTRAINT [JobScript_tbl_pk]
       PRIMARY KEY NONCLUSTERED ([Id])
       WITH (
         FILLFACTOR = 80,
         PAD_INDEX = OFF,
         STATISTICS_NORECOMPUTE = OFF,
         ALLOW_ROW_LOCKS = ON,
         ALLOW_PAGE_LOCKS = ON)
  end

  If NOT Exists (select Name from sysindexes where Name = 'JobScript_tbl_uq')
  begin
     print (' --> create index: JobScript_tbl_uq')
     ALTER TABLE [dbo].[JobScript_tbl]
        ADD CONSTRAINT [JobScript_tbl_uq]
        UNIQUE NONCLUSTERED ([JobScriptName])
        WITH (
          FILLFACTOR = 80,
          PAD_INDEX = OFF,
          IGNORE_DUP_KEY = OFF,
          STATISTICS_NORECOMPUTE = OFF,
          ALLOW_ROW_LOCKS = ON,
          ALLOW_PAGE_LOCKS = ON)
  end
--------------------------------------------------------------------------------
EndeExit:
 Print '------------------------------------------------------------------------'
 Print (@MyActionMsg1)
 Print ('   |-> Done on Database: #DB_N#')
 Print '------------------------------------------------------------------------'

GO
