View Code create view view_job_info
Select B. [name] [job name], case when B. Enabled = 1 then' enable 'else' disable 'end [Enable]
, A. step_name [step name], A. Command
From MSDB. DBO. sysjobsteps a inner join
MSDB. DBO. sysjobs B on A. job_id = B. job_id
Select B .*,
Object_name (object_id, database_id) stored procedure name,
D. cached_time: the last execution time (ms) of D. last_execution_time,
D. total_elapsed_time/1000 total time spent in MS,
D. total_elapsed_time/d.exe cution_count * 1000 as average execution time ms,
D. last_elapsed_time: last execution time (MS), d.exe cution_count: number of executions
From SYS. dm_exec_procedure_stats as D inner join view_job_info B on B. Command like
'%' + Object_name (object_id, database_id) + '%'
Where object_name (object_id, database_id) in (Select name from SYS. objects where
Type = 'P'
And modify_date> '2017-2-24 17:32:50 ')
Order by 6 DESC;