![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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 ;
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 ;