VIEW systasks

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


ALTER   VIEW systasks
AS
  SELECT 'id'                     = sti.task_id,
         'name'                   = sj.name,
         'subsystem'              = sjst.subsystem,
         'server'                 = sjst.server,
         'username'               = sjst.database_user_name,
         'ownerloginid'           = 1, -- Always default to SA since suid is no longer available
         'databasename'           = sjst.database_name,
         'enabled'                = sj.enabled,
         'freqtype'               = ISNULL(sjsch.freq_type, 2), -- On Demand
         'freqinterval'           = ISNULL(sjsch.freq_interval, 0),
         'freqsubtype'            = ISNULL(sjsch.freq_subday_type, 0),
         'freqsubinterval'        = ISNULL(sjsch.freq_subday_interval, 0),
         'freqrelativeinterval'   = ISNULL(sjsch.freq_relative_interval, 0),
         'freqrecurrencefactor'   = ISNULL(sjsch.freq_recurrence_factor, 0),
         'activestartdate'        = ISNULL(sjsch.active_start_date, 19900101),
         'activeenddate'          = ISNULL(sjsch.active_end_date, 99991231),
         'activestarttimeofday'   = ISNULL(sjsch.active_start_time, 0),
         'activeendtimeofday'     = ISNULL(sjsch.active_end_time, 235959),
         'lastrundate'            = sjs.last_run_date,
         'lastruntime'            = sjs.last_run_time,
         'nextrundate'            = ISNULL(sjsch.next_run_date, 0),
         'nextruntime'            = ISNULL(sjsch.next_run_time, 0),
         'runpriority'            = sjst.os_run_priority,
         'emailoperatorid'        = sj.notify_email_operator_id,
         'retryattempts'          = sjst.retry_attempts,
         'retrydelay'             = sjst.retry_interval,
         'datecreated'            = sj.date_created,
         'datemodified'           = sj.date_modified,
         'command'                = sjst.command,
         'lastruncompletionlevel' = sjs.last_run_outcome,
         'lastrunduration'        = sjst.last_run_duration,
         'lastrunretries'         = sjst.last_run_retries,
         'loghistcompletionlevel' = sj.notify_level_eventlog,
         'emailcompletionlevel'   = sj.notify_level_email,
         'description'            = sj.description,
         'tagadditionalinfo'      = 0,
         'tagobjectid'            = 0,
         'tagobjecttype'          = 0,
         'parameters'             = CONVERT(TEXT, sjst.additional_parameters),
         'cmdexecsuccesscode'     = sjst.cmdexec_success_code
    FROM msdb.dbo.sysjobs                         sj
         LEFT OUTER JOIN msdb.dbo.sysjobschedules sjsch ON (sj.job_id = sjsch.job_id),
         msdb.dbo.systaskids                      sti,
         msdb.dbo.sysjobsteps                     sjst,
         msdb.dbo.sysjobservers                   sjs
    WHERE (sj.job_id = sti.job_id)
      AND (sj.job_id = sjst.job_id)
      AND (sjst.step_id = 1)
      AND (sj.job_id = sjs.job_id)
      AND (sjs.server_id = 0)
      AND ((sjsch.name = N'6.x schedule') OR (sjsch.name IS NULL)) -- NULL handles the case of the job not having a schedule
  UNION ALL -- NOTE: We do this just to make the view non-updatable
  SELECT 0, '', '', '', '', 0, '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, GETDATE(), GETDATE(), '', 0, 0, 0, 0, 0, '', 0, 0, 0, '', 0
  WHERE (1 = 2)


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO





As msdb in SQL2005 is no longer the same as in SQL2000, what would be the 2005 version for

the following SQL2000 query:

SELECT b.name as job_name,c.Schedule,c.next_run_time
      ,step_name,subsystem,command,database_name
 FROM msdb..sysjobsteps a
INNER JOIN (SELECT name,job_id
                FROM msdb..sysjobs
                WHERE enabled = 1)b
      ON a.job_id =b.job_id
INNER JOIN (SELECT job_id
                  ,(CASE WHEN freq_type = 4 THEN 'Daily'
                         WHEN freq_type =16 THEN 'Monthly'
                         WHEN freq_type =8  THEN 'Weekly'
                         END )as Schedule
                  ,left(right('000000'+convert(varchar,next_run_time),6),4) as

next_run_time
              FROM msdb..sysjobschedules ) c
     ON a.job_id =c.job_id


You will need to join with both sysjobschedules and sysschedules.

Or just be lazy and use sp_help_schedule