IT-блог Жаконды

SELECT
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName],
	case when d.name is null then 'No Schedule' else d.name end Schedule,
isnull (case d.freq_type
when '1 ' then 'Once'
when '4' then 'Daily'
when '8' then 'Weekly'
when '16' then 'Monthly'
when '32' then 'Monthly relative'
when '64' then 'When SQL Server Agent starts' end, 'None') as Frequency,
     CASE
        WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
        ELSE CAST(
                CAST([sJOBH].[run_date] AS CHAR(8))
                + ' '
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [LastRunDateTime]
    , CASE [sJOBH].[run_status]
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 4 THEN 'Running' -- In Progress
      END AS [LastRunStatus]
    , STUFF(
            STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)),  6)
                , 3, 0, ':')
            , 6, 0, ':')
        AS [LastRunDuration]
    , [sJOBH].[message] AS [LastRunStatusMessage]
    , CASE [sJOBSCH].[NextRunDate]
        WHEN 0 THEN NULL
        ELSE CAST(
                CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
                + ' '
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [NextRunDateTime],
	  isnull (convert (varchar,d.Date_Created), 'None') CreatedDate
FROM
    [msdb].[dbo].[sysjobs] AS [sJOB]

    LEFT JOIN (
                SELECT
                    [job_id]
                    , MIN([next_run_date]) AS [NextRunDate]
                    , MIN([next_run_time]) AS [NextRunTime]
                FROM [msdb].[dbo].[sysjobschedules]
                GROUP BY [job_id]
            ) AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN (
                SELECT
                    [job_id]
                    , [run_date]
                    , [run_time]
                    , [run_status]
                    , [run_duration]
                    , [message]
                    , ROW_NUMBER() OVER (
                                            PARTITION BY [job_id]
                                            ORDER BY [run_date] DESC, [run_time] DESC
                      ) AS RowNumber
                FROM [msdb].[dbo].[sysjobhistory]
                WHERE [step_id] = 0
            ) AS [sJOBH]
        ON [sJOB].[job_id] = [sJOBH].[job_id]
        AND [sJOBH].[RowNumber] = 1
		left outer join msdb.dbo.sysjobschedules e on e.job_id = [sJOB].job_id
		left outer join msdb.dbo.sysschedules d on e.schedule_id = d.schedule_id

USE msdb
GO
SELECT 
    [sJOB].[name] AS [JobName]
    , [sDBP].[name] AS [JobOwner]
    , [sCAT].[name] AS [JobCategory]
    , [sJOB].[description] AS [JobDescription]
    , [sJSTP].[step_id] AS [JobStartStepNo]
    , [sJSTP].[step_name] AS [JobStartStepName]
    , [sJOB].[date_created] AS [JobCreatedOn]
    , [sJOB].[date_modified] AS [JobLastModifiedOn]
    , CASE [sJOB].[enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
        END AS [IsEnabled]
    , CASE
        WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
        ELSE 'Yes'
        END AS [IsScheduled]
    , CASE 
        WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle'
        WHEN [freq_type] IN (4, 8, 16, 32) THEN 'Recurring'
        WHEN [freq_type] = 1 THEN 'One Time'
        END [ScheduleType]
    , CASE [freq_type]
        WHEN 1 THEN 'One Time'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
        WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN 128 THEN 'Start whenever the CPUs become idle'
        END [Occurrence]
    , CASE [freq_type]
        WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
        WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
            + ' week(s) on '
            + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END
            + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END
            + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END
            + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END
            + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END
            + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END
            + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END
        WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) 
            + ' of every '
            + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
        WHEN 32 THEN 'Occurs on '
            + CASE [freq_relative_interval]
                WHEN 1 THEN 'First'
                WHEN 2 THEN 'Second'
                WHEN 4 THEN 'Third'
                WHEN 8 THEN 'Fourth'
                WHEN 16 THEN 'Last'
                END
            + ' ' 
            + CASE [freq_interval]
                WHEN 1 THEN 'Sunday'
                WHEN 2 THEN 'Monday'
                WHEN 3 THEN 'Tuesday'
                WHEN 4 THEN 'Wednesday'
                WHEN 5 THEN 'Thursday'
                WHEN 6 THEN 'Friday'
                WHEN 7 THEN 'Saturday'
                WHEN 8 THEN 'Day'
                WHEN 9 THEN 'Weekday'
                WHEN 10 THEN 'Weekend day'
                END
            + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
            + ' month(s)'
        END AS [Recurrence]
    , CASE [freq_subday_type]
        WHEN 1 THEN 'Occurs once at ' 
            + STUFF(STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
        WHEN 2 THEN 'Occurs every ' 
            + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' 
            + STUFF(STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
            + ' & ' 
            + STUFF(STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
        WHEN 4 THEN 'Occurs every ' 
            + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' 
            + STUFF(STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
            + ' & ' 
            + STUFF(STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
        WHEN 8 THEN 'Occurs every ' 
            + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' 
            + STUFF(STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
            + ' & ' 
            + STUFF(STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
        END [Frequency]
    , [sSCH].[name] AS [JobScheduleName]
    , LastRun = CONVERT(DATETIME, RTRIM(run_date) 
        + ' '
        + STUFF(STUFF(REPLACE(STR(RTRIM(h.run_time),6,0),' ','0'),3,0,':'),6,0,':'))
    , CASE [sJSTP].Last_run_outcome
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 5 THEN 'Unknown'
        END AS LastRunStatus
    , LastRunDuration = STUFF(STUFF(REPLACE(STR([sJSTP].last_run_duration,7,0),' ','0'),4,0,':'),7,0,':')
    , MaxDuration = STUFF(STUFF(REPLACE(STR(l.run_duration,7,0),' ','0'),4,0,':'),7,0,':')
    , NextRun = CONVERT(DATETIME, RTRIM(NULLIF([sJOBSCH].next_run_date, 0)) 
        + ' '
        + STUFF(STUFF(REPLACE(STR(RTRIM([sJOBSCH].next_run_time),6,0),' ','0'),3,0,':'),6,0,':'))
    , CASE [sJOB].[delete_level]
        WHEN 0 THEN 'Never'
        WHEN 1 THEN 'On Success'
        WHEN 2 THEN 'On Failure'
        WHEN 3 THEN 'On Completion'
        END AS [JobDeletionCriterion]
    , [sSVR].[name] AS [OriginatingServerName]
    , [sJSTP].subsystem AS Subsystem
    , [sJSTP].command AS Command
    , h.message AS Message
FROM
    [msdb].[dbo].[sysjobs] AS [sJOB]
    LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
        ON [sJOB].[originating_server_id] = [sSVR].[server_id]
    LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
        ON [sJOB].[category_id] = [sCAT].[category_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
        ON [sJOB].[job_id] = [sJSTP].[job_id] AND [sJOB].[start_step_id] = [sJSTP].[step_id]
    LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
        ON [sJOB].[owner_sid] = [sDBP].[sid]
    LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
        ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
    LEFT JOIN (
            SELECT job_id, instance_id = MAX(instance_id), MAX(run_duration) AS run_duration
            FROM msdb.dbo.sysjobhistory
            GROUP BY job_id
        ) AS l
        ON sJOB.job_id = l.job_id
    LEFT JOIN
        msdb.dbo.sysjobhistory AS h
        ON h.job_id = l.job_id AND h.instance_id = l.instance_id
ORDER BY [JobName]