Find SQL jobs to delete that refer to non-existent databases

Question

I am attempting to find all our SQL jobs which are no longer required due to the database they used no longer existing.

The logic is simple, select a list of jobs with commands, then check within the command if the database is listed in the sysdatabase table. This is what I have so far which is almost there:

SELECT 
   Srv.srvname AS ServerName,
   Job.name AS JobName,
   JStep.step_id,
   --JStep.step_name AS StepName,
   JStep.command
   --,Job.enabled 
    into #JobListTable
FROM   msdb.dbo.sysjobs Job
JOIN   msdb.dbo.sysjobsteps JStep
   ON  JStep.job_id = Job.job_id 
JOIN   MASTER.dbo.sysservers Srv
   ON  Srv.srvid = Job.originating_server_id
WHERE  JStep.subsystem='SSIS'
--SELECT * FROM #JobListTable

DECLARE @JobName SYSNAME, @StepId INT, @Command NVARCHAR(MAX),@PosStart BIGINT, @PosEnd BIGINT, @Server NVARCHAR(128),@JobList VARCHAR(MAX), @DBNAMETEMP SYSNAME, @SQLStatement VARCHAR(MAX)
DECLARE @MAXID INT, @Counter INT

SET @COUNTER = 1
SELECT @MAXID = COUNT(*) FROM #JobListTable
    
WHILE (@COUNTER <= @MAXID)
--WHILE (@COUNTER <= 3)
    BEGIN
            SET @JobName=(SELECT top 1 JobName FROM #JobListTable)-- WHERE command=@Command)
            SET @command=(SELECT top 1 command FROM #JobListTable)-- WHERE JobName=@JobName)  
            SET @StepId=(SELECT top 1 step_id FROM #JobListTable)

            SELECT @Command = [s].[command]
            FROM msdb.dbo.[sysjobs] [j]
            INNER JOIN msdb.dbo.[sysjobsteps] [s] ON [s].[job_id] = [j].[job_id]
            WHERE [j].[name] = @JobName
              AND [s].[step_id] = @StepId

            SET @PosStart = PATINDEX('%/SERVER %', @Command)+8
            SET @PosEnd = CHARINDEX(' ', @Command, @PosStart)
            SET @Server = SUBSTRING(@Command, @PosStart, @PosEnd - @PosStart)
            SET @PosStart = PATINDEX('%"\"%', @Command)+3
            SET @PosEnd = PATINDEX('%\""%', @Command)
            SET @Command = SUBSTRING(@Command, @PosStart, @PosEnd - @PosStart)

            --select

            --right(@Command, len(@Command) - charindex('Initial Catalog=',@Command ,1)+1) DBIndexPos,
            --charindex(';',right(@Command, len(@Command) - charindex('Initial Catalog=',@Command ,1)+1),1) SemiColonPos,
            SET @DBNAMETEMP = 
            replace(left(right(@Command, len(@Command) - charindex('Initial Catalog=',@Command ,1)+1), charindex(';',right(@Command, len(@Command) - charindex('Initial Catalog=',@Command ,1)+1),1)-1),'Initial Catalog=','') --DatabaseName

            --PRINT @DBNAMETEMP 
            
            select 'exec msdb..sp_delete_jobstep @job_name = ''' + j.name + ''', @step_id = ' + convert(varchar(100), js.step_id)
            from msdb..sysjobs j
                inner join msdb..syscategories c
                    on c.category_id = j.category_id
                inner join msdb..sysjobsteps js
                    on js.job_id = j.job_id
                where @DBNAMETEMP not in (select name from sysdatabases)


            SET @SQLStatement= 'EXEC msdb.dbo.sp_delete_job @job_name =''' + @jobName + ''', @delete_unused_schedule=1' 
            PRINT(@SQLStatement)

            --select * from #dblist

                SET @COUNTER = @COUNTER + 1
        
        DELETE FROM #JobListTable WHERE JobName=@JobName

END
    DROP TABLE #JobListTable
    --DROP TABLE #dblist

Answer 1

Finally got it working..

This code allows you to remove all the job steps where it relates to an old database, then you can follow up and delete jobs where no job steps exist in each job. Happy I resolved my own first post! Long time reader!

I created a temp table with the job info (including the command column which has the SSIS connection details):

SELECT Srv.srvname AS ServerName,Job.name AS JobName,Job.job_id, JStep.step_id, JStep.command as DatabaseName
into #JobListTable
FROM   msdb.dbo.sysjobs Job
JOIN   msdb.dbo.sysjobsteps JStep
   ON  JStep.job_id = Job.job_id 
JOIN   MASTER.dbo.sysservers Srv
   ON  Srv.srvid = Job.originating_server_id
WHERE  JStep.subsystem='SSIS'

I then updated the (aliased) column DatabaseName to remove all the rest string I didn't want, leaving just the Database Name from the connection string:

UPDATE #JobListTable
SET DatabaseName = substring(DatabaseName, patindex('%Initial Catalog=%',DatabaseName)+16, (patindex('%Provider=%',DatabaseName)-patindex('%Initial Catalog=%',DatabaseName)-17))
WHERE patindex('%Initial Catalog=%', DatabaseName) > 0

The next section builds the sp_delete_jobstep command from the temp table checking that the database name found in the connection string does not exist in the sys.databases table:

select 'exec msdb..sp_delete_jobstep @job_name = ''' + j.name + ''', @step_id = ' + convert(varchar(100), jlt.step_id)
        from #JobListTable jlt
        --from msdb..sysjobs j
            inner join msdb..sysjobs j
                on j.job_id = jlt.job_id
            where jlt.DatabaseName not in (select name from sys.databases)

Clean up the temp table:

drop table #JobListTable

Finally, if you then want to delete all the jobs with no job steps, run this:

select c.name, j.job_id, j.name, j.description, j.enabled, j.date_created,
'exec msdb..sp_delete_job @job_name = ''' + j.name + ''', @delete_history = 1,
@delete_unused_schedule = 1' as delete_cmd
from msdb..sysjobs j
 inner join msdb..syscategories c
   on c.category_id = j.category_id
 where not exists (
    select 1 from msdb..sysjobsteps js
    where js.job_id = j.job_id) and NOT ( c.name = 'Database Maintenance')