Print ''
Print '-------------------------------------------------------------------------'
Print ' #Doc.........: Cleanup !!all NonRef Datarows from Objects_Ir_Rel_tbl'
Print ' #New.........: 19.07.2024 A.Becker/A.Stein'
Print ' #LUp.........: 11.10.2024 moved to:.\DbMaintain_2Extendet\JobMgr_21_CleanUpUnref_IR_Rel_tbl.sql J.Fes/A.Stein'
Print ' Run ON DB....: #DB_N#'
Print '-------------------------------------------------------------------------'

SET NOCOUNT ON

USE [#DB_N#]
DECLARE @DoDeleteData INT
set @DoDeleteData = 1

DECLARE @orphanRelations TABLE (Rel_ID UNIQUEIDENTIFIER PRIMARY KEY NOT NULL, InfoText NVARCHAR(1000))

INSERT INTO @orphanRelations(Rel_ID, InfoText)
SELECT REL.Id AS Rel_ID
     --, REL.ObjectId AS REL_CHILD_ObjectId
	 --, REL.IrId     AS REL_PARENT_IrId
     --, COALESCE(OBJ_PARENT.Id, OBJ_CHILD.Id) AS ObjectId
     --, COALESCE(OBJ_PARENT.ObjectCliName, OBJ_CHILD.ObjectCliName) AS ObjectCliName
     --, COALESCE(OBJ_PARENT.ObjectClass,   OBJ_CHILD.ObjectClass)   AS ObjectClass
	 , 'Removed orphan object relation from [' + ISNULL( COALESCE(OBJ_PARENT.ObjectClass, OBJ_CHILD.ObjectClass) + ':CliName=' + COALESCE(OBJ_PARENT.ObjectCliName, OBJ_CHILD.ObjectCliName), 'NONE') + ']' AS InfoText
FROM Objects_Ir_Rel_tbl REL
LEFT JOIN Objects_tbl OBJ_PARENT ON OBJ_PARENT.Id=REL.IrId
LEFT JOIN Objects_tbl OBJ_CHILD  ON OBJ_CHILD.Id =REL.ObjectId
WHERE (OBJ_CHILD.Id  IS NULL)
   OR (OBJ_PARENT.Id IS NULL)


DECLARE @count INT
DECLARE @current INT = 0
DECLARE @relId UNIQUEIDENTIFIER
DECLARE @infoText NVARCHAR(1000)

SELECT @count = Count(1) FROM @orphanRelations
SET @infoText = 'Found ' + CAST(@count AS NVARCHAR) + ' orphan object relations which will be deleted now.'
print @infoText

WHILE EXISTS (SELECT 1 FROM @orphanRelations)
BEGIN

        SET @current = @current + 1

        SELECT TOP 1 @relId=Rel_ID, @infoText=InfoText
        FROM @orphanRelations


        set @infoText = CAST(@current AS NVARCHAR) + ' / ' + CAST(@count AS NVARCHAR) + ' - ' + @infoText
        RAISERROR(@infoText, 0, 1) WITH NOWAIT;
        --print @infoText
    if @DoDeleteData = 1
    begin
        DELETE FROM Objects_Ir_Rel_tbl WHERE Id=@relId
    end
        DELETE @orphanRelations WHERE Rel_ID=@relId

END

Print ''
Print '----------------------------------------------------'
Print ' SQL Script Excetuted on: [#DB_N#]'
Print '----------------------------------------------------'
GO

