How to get list of jobs that run on particular day in Microsoft SQL Server 2005??

Question

Hi ,
Can any one Please tell me a query to find out all the jobs that run on particular day of a week.
All the jobs are scheduled to run in Microsoft SQL Server 2005->SQL Server Management Studio->SQL Server Agent->jobs.
In the properties of job and in the schedule information the days on which this job should run is given.
Please help .

Answer 1

you could try this and filter the schedule field for the day you want. i use this query a lot to get job schedules which i then post to our internal wiki so business users can easily see when things run.

select 
 msdb.dbo.sysjobs.name as jobname, 
 msdb.dbo.udf_schedule_description(
  msdb.dbo.sysschedules.freq_type, 
  msdb.dbo.sysschedules.freq_interval, 
  msdb.dbo.sysschedules.freq_subday_type, 
  msdb.dbo.sysschedules.freq_subday_interval, 
  msdb.dbo.sysschedules.freq_relative_interval, 
  msdb.dbo.sysschedules.freq_recurrence_factor, 
  msdb.dbo.sysschedules.active_start_date, 
  msdb.dbo.sysschedules.active_end_date, 
  msdb.dbo.sysschedules.active_start_time, 
  msdb.dbo.sysschedules.active_end_time) as schedule
from 
 msdb.dbo.sysjobs 
 left outer join 
 msdb.dbo.sysjobschedules on msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobschedules.job_id 
 left outer join 
 msdb.dbo.sysschedules on msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id  
where 
 msdb.dbo.sysjobs.enabled = 1
order by 
 msdb.dbo.sysjobs.name

Answer 2

I tried with this query i didnt work. can i get any other suggestions please.

Answer 3

Try Thomas' script with the the dbo.udf_schedule_description function from this site (http://www.mssqltips.com/tip.asp?tip=1622) installed in MSDB.

Cheers,

-Jim