Print ''
Print '-------------------------------------------------------------------------'
Print ' Description...: Cleanup duplicates from '
Print ' Creation Date : 06.11.2015'
Print ' LastMod Date  : 28.02.2011 22:53'
Print ' Autor         : A.Becker/S.Gueth/J.Feuerstein'
Print ' using Database: #DB_N#'
Print '-------------------------------------------------------------------------'

use [#DB_N#]

-- set nocount on
-- CREATE PROCEDURE dbo.ListDuplicateIrToItemRelations
-- AS
-- BEGIN

  -- SET NOCOUNT ON

  DECLARE @dupIrToItem TABLE (IR_DbId UNIQUEIDENTIFIER, RelCount INT)
  INSERT INTO @dupIrToItem
      SELECT
        IR_I.IR_DbId,
        Count(1) as RelCount
      FROM
        dbo.IR_I_rel_View IR_I
        INNER JOIN dbo.Objects_tbl I ON (IR_I.Item_DbId = I.Id AND I.ObjectClass = N'I')
      GROUP BY
        IR_I.IR_DbId
      HAVING
        Count(1) > 1

   SELECT DUP.RelCount,
          IR.ObjectName AS IrId, IR.ObjectClass AS IRClass, IR.Id AS IrDbId,
          I.ObjectName AS ItemId, I.ObjectClass AS ItemClass, I.Id AS ItemDbId

   FROM @dupIrToItem DUP
        LEFT JOIN dbo.IR_I_rel_View IR_I ON (IR_I.IR_DbId = DUP.IR_DbId)
        LEFT JOIN dbo.Objects_tbl I ON (I.Id = IR_I.Item_DbId)
        LEFT JOIN dbo.Objects_tbl IR ON (IR.Id = IR_I.IR_DbId)
   ORDER BY IR.Id

-- END
goto EndeExit


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

GO
