USE [HCITools];
GO

/*============================================================================= 

  Look for specific jobs to delete if they exists on the server.

  -----------------------------------------------
  US OCTPDBA-336
  Clean up of the previously defined jobs to be replaced with [_D92060 - Monitor Wait Stats]
 
  Contexte d'utilisation
  ----------------------
    Everywhere where hciTools exists
  
  Création : 13.10.2022 / TSC

=============================================================================*/

SET XACT_ABORT ON;
SET NOCOUNT ON;

DECLARE @found_jobs_names TABLE (job_name VARCHAR(500) NOT NULL);

INSERT INTO @found_jobs_names ([job_name])
VALUES ('DR92060 - Monitor Wait Stats - Central'),
       ('ZZ-LPE-Monitor Wait Stats'),
       ('ZZ-LPE-Monitor Wait Stats - AMAREP'),
       ('ZZ-LPE-Monitor Wait Stats - AMA051'),
       ('ZZ-LPE-Monitor Wait Stats - AMA562'),
       ('ZZ-LPE-Monitor Wait Stats - AMA603');

/* declare variables */
DECLARE @job_name VARCHAR(500);

DECLARE clean_jobs CURSOR FAST_FORWARD READ_ONLY FOR
SELECT job_name
  FROM @found_jobs_names;

OPEN clean_jobs;

FETCH NEXT FROM clean_jobs
 INTO @job_name;

WHILE @@FETCH_STATUS = 0
BEGIN

    IF EXISTS (   SELECT 1
                    FROM msdb.dbo.sysjobs sj
                   WHERE LOWER(sj.[name]) = LOWER(@job_name))
    BEGIN
		        RAISERROR(
            'found job "%s". Dropping',
            1,
            1,
            @job_name) WITH NOWAIT;

        EXEC msdb.dbo.sp_delete_job @job_name = @job_name,
                                    @delete_unused_schedule = 1;

    END;
    FETCH NEXT FROM clean_jobs
     INTO @job_name;
END;

CLOSE clean_jobs;
DEALLOCATE clean_jobs;