We check daily and weekly jobs schedule status that has been scheduled in SQL server in many servers for different projects. I wrote some queries to get the status as below.(if you have better solution please comment)
We can use a readymade solution SPHELP_JOBSCHEDULE store procedure which takes jobid, jobname, schedulename, scheduleid and schedule descirption. But this will return multiple columns and what if we have to query only the enabled status of a schedule.
So the other approach may be to query the MSDB table DBO.SYSSCHEDULES which will give us the ‘enabled’ column from which we can get the Schedule status of the job. For more information we can join the dbo.sysjobschedules and dbo.SYSJOBS.
SELECT enabled as JobScheuduledStatus FROM DBO.SYSSCHEDULES WHERE [NAME]=’JSCH_HR600d’
*this one is already published in BeyondRelational.com