Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Table         : [Objects_Ir_Rel_tbl]'
Print ' Creation Date : 22.03.2006'
Print ' Last Update.. : 11.06.2015/J.Fes/A.Becker'
Print ' Autor         : J.Feuerstein'
Print ' '
Print '-------------------------------------------------------------------------'

use [#DB_N#]


Declare @MyActionMsg1 nvarchar (max)
Declare @MyTableName nvarchar (max)
set @MyTableName='Objects_Ir_Rel_tbl'
--------------------------------------------------------------------------------
-- verzweigen auf TableCreate: oder TableUpdate:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Objects_Ir_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)

  CREATE TABLE [dbo].[Objects_Ir_Rel_tbl] (
         /* mappt IRS zu den Objects */
         [Id]                        uniqueidentifier NOT NULL,
         [ObjectId]                  uniqueidentifier NOT NULL,
         [IrId]                      uniqueidentifier NOT NULL
  )
  ON [PRIMARY]

goto CheckCreateIndex

--------------------------------------------------------------------------------
TableUpdate:
  set @MyActionMsg1=
       '-- Table: [' + @MyTableName + '] exist and will be updated'
  Print (@MyActionMsg1)
  goto CheckCreateIndex


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

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

  EXECUTE JF_IndexDelete 'Objects_Ir_Rel_tbl_IDX_ObjectId', @MyTableName,  'ObjectId', 'NO'
  EXECUTE JF_IndexDelete 'Objects_Ir_Rel_tbl_IDX_IrId', @MyTableName,  'IrId', 'NO'

  -- 08.03.2011 16:00 angepasst
  EXECUTE JF_PrimKeyCheckCreateDef 'Objects_Ir_Rel_tbl_pk',           @MyTableName, 'Id'
  
  -- 26.09.2019/A.Stein A.Becker -> drop old Index
  EXECUTE JF_IndexDelete 'Objects_Ir_Rel_tbl_IDX_ObjectId_V2', @MyTableName,  'ObjectId', 'NO'
  EXECUTE JF_IndexDelete 'Objects_Ir_Rel_tbl_IDX_IrId_V2', @MyTableName,  'IrId', 'NO'

  -- 11.06.2015/J.Fes A.Becker ->  Oti: INCLUDE
  -- LUp: 26.08.2019/A.Stein A.Becker -> Insert 2 Index
  EXECUTE JF_IndexCheckCreateDef   'Objects_Ir_Rel_tbl_IDX_ObjectId_V3', @MyTableName, 'ObjectId,IrId'
  EXECUTE JF_IndexCheckCreateDef   'Objects_Ir_Rel_tbl_IDX_IrId_V3',     @MyTableName, 'IrId,ObjectId'
GO

CreateViews:

Print '-- [IR_I_rel_View #New:14.11.2015 A.Becker] -----------------------------'
If Exists (select Name
           from sysobjects
           where Name = "IR_I_rel_View" and type = "V")
Drop View IR_I_rel_View
GO

CREATE VIEW IR_I_rel_View
AS
  SELECT Id,
         ObjectId AS IR_DbId,
         IrId AS Item_DbId
  FROM Objects_Ir_Rel_tbl
GO

Print '-- [DS_IR_rel_View #New: 14.11.2015 10:48 A.Becker] ---------------------'
If Exists (select Name
           from sysobjects
           where Name = "DS_IR_rel_View" and type = "V")
Drop View DS_IR_rel_View
GO

CREATE VIEW DS_IR_rel_View
AS
  SELECT Id,
         ObjectId AS DS_DbId,
         IrId AS IR_DbId
  FROM Objects_Ir_Rel_tbl
GO

EndeExit:
 Print '------------------------------------------------------------------------'
 Print 'Done on Database: #DB_N#'
 Print '------------------------------------------------------------------------'


GO
