Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Table         : [Tasks_Objects_Rel_tbl]'
Print ' Creation Date : 22.09.2008'
Print ' Autor         : A.Becker'
Print ' '
Print '-------------------------------------------------------------------------'

use [#DB_N#]


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

--------------------------------------------------------------------------------
TableCreate:
  set @MyActionMsg1=
      '-- Table: [' + @MyTableName + '] dose not exist and will be created new'
  Print (@MyActionMsg1)

  -- Info: Tabelle hält die verküpfungen von OnDeJobs zu den Referenzierten Object Daten
  CREATE TABLE [dbo].[Tasks_Objects_Rel_tbl] (
          [Id]                    [uniqueidentifier] NOT NULL,
          [TaskScheduler_tbl_Id]  [uniqueidentifier] NOT NULL,
          [Objects_tbl_Id]        [uniqueidentifier] NOT NULL,
          [JobControl_ObjectsProcessData_tbl_Id] [uniqueidentifier] NULL -- verweis auf die JobControl ProcessData solange = null ist das Objekt noch nicht in Process (new 26.09.08)
  ) 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)
  
  
  -- Ref auf die JobControl ProcessData solange = null ist das Objekt noch nicht in Process (new 26.09.08)
  set @ColumeName='JobControl_ObjectsProcessData_tbl_Id'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [Rel to ' + @ColumeName + ' .. (new 16.05.08)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [uniqueidentifier] NULL'
      execute (@ExecuteStr)
    end

  -- rename 26.09.2008 13:01 J.Fes
  set @ColumeNameOld='TaskId'
  set @ColumeNameNew='TaskScheduler_tbl_Id'
  if not (Select Coalesce(Col_length(@MyTableName ,@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
      set @ExecuteStr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteStr)
      execute (@ExecuteStr)
    end

  -- rename 26.09.2008 13:01 J.Fes
  set @ColumeNameOld='ObjectId'
  set @ColumeNameNew='Objects_tbl_Id'
  if not (Select Coalesce(Col_length(@MyTableName ,@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
      set @ExecuteStr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteStr)
      execute (@ExecuteStr)
    end

  goto CheckCreateIndex

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

  -- Indexe erstellen
  If NOT Exists (select Name from sysindexes where Name = 'Tasks_Objects_Rel_tbl_pk')
  begin
     print ('create Tasks_Objects_Rel_tbl.Tasks_Objects_Rel_tbl_pk')
     ALTER TABLE [dbo].[Tasks_Objects_Rel_tbl]
       ADD CONSTRAINT [Tasks_Objects_Rel_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 = 'Tasks_Objects_Rel_tbl_IDX1')
  begin
    print (' --> create index: Tasks_Objects_Rel_tbl_IDX1 -> Objects_tbl_Id')
    CREATE NONCLUSTERED INDEX [Tasks_Objects_Rel_tbl_IDX1] ON [dbo].[Tasks_Objects_Rel_tbl]
      ([TaskScheduler_tbl_Id])
    WITH (
      FILLFACTOR = 80,
      PAD_INDEX = OFF,
      DROP_EXISTING = OFF,
      STATISTICS_NORECOMPUTE = OFF,
      SORT_IN_TEMPDB = OFF,
      ONLINE = OFF,
      ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON)
    ON [PRIMARY]
  end
  
  If NOT Exists (select Name from sysindexes where Name = 'Tasks_Objects_Rel_tbl_IDX2')
  begin
    print (' --> create index: Tasks_Objects_Rel_tbl_IDX2 -> Objects_tbl_Id')
    CREATE NONCLUSTERED INDEX [Tasks_Objects_Rel_tbl_IDX2] ON [dbo].[Tasks_Objects_Rel_tbl]
      ([Objects_tbl_Id])
    WITH (
      FILLFACTOR = 80,
      PAD_INDEX = OFF,
      DROP_EXISTING = OFF,
      STATISTICS_NORECOMPUTE = OFF,
      SORT_IN_TEMPDB = OFF,
      ONLINE = OFF,
      ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON)
    ON [PRIMARY]
  end

  If NOT Exists (select Name from sysindexes where Name = 'Tasks_Objects_Rel_tbl_IDX3')
  begin
    print (' --> create index: Tasks_Objects_Rel_tbl_IDX3 -> JobControl_ObjectsProcessData_tbl_Id')
    CREATE NONCLUSTERED INDEX [Tasks_Objects_Rel_tbl_IDX3] ON [dbo].[Tasks_Objects_Rel_tbl]
      ([JobControl_ObjectsProcessData_tbl_Id])
    WITH (
      FILLFACTOR = 80,
      PAD_INDEX = OFF,
      DROP_EXISTING = OFF,
      STATISTICS_NORECOMPUTE = OFF,
      SORT_IN_TEMPDB = OFF,
      ONLINE = OFF,
      ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON)
    ON [PRIMARY]
  end


  Goto EndeExit


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

GO
