学步园

create VIEW view_job_info as
SELECT b.[name] [Job名称],CASE WHEN b.enabled=1 THEN '启用' ELSE '禁用' END [是否启用]
, a.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) 存储过程名, 
d.cached_time 编译时间, d.last_execution_time 最近一次执行时间ms, 
d.total_elapsed_time/1000 总花费时间ms, 
d.total_elapsed_time/d.execution_count*1000 AS 平均执行时间ms,
d.last_elapsed_time 最近一次执行花费时间ms, d.execution_count 执行次数
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>'2008-2-24 17:32:50')
ORDER BY 6 DESC;