Execute all jobs in same job category
I wonder why I couldn't find a script on the Internet which generates statements to execute all jobs in the same job category.
The only reason I could think of is that it's simple to write so here goes my version
SET
NOCOUNT ON
USE tempdb
GO
DECLARE
@Category AS VARCHAR(255)
SET @Category = 'Database Maintenance'
SELECT
a.name
INTO #jobsincategory
FROM [msdb].[dbo].[sysjobs] a
LEFT JOIN [msdb].[dbo].[syscategories] b
ON a.category_id = b.category_id
WHERE b.name = @Category
DECLARE
jobsincategory CURSOR READ_ONLY FOR
SELECT
*
FROM #jobsincategory
DECLARE
@name VARCHAR(40)
OPEN jobsincategory
FETCH
NEXT FROM jobsincategory
INTO @name
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
DECLARE @message VARCHAR(100)
SELECT @message = 'exec msdb..sp_start_job @job_name =''' + @name + ''''
PRINT @message
END
FETCH NEXT FROM jobsincategory
INTO @name
END
CLOSE jobsincategory
DEALLOCATE jobsincategory
GO
DROP
TABLE #jobsincategory
The outcome looks like this:
exec msdb..sp_start_job @job_name ='job1'
exec msdb..sp_start_job @job_name ='job2'
exec msdb..sp_start_job @job_name ='job3'
...