A Table Summarizing All Agent Jobs with Steps…

What’s this good for?

This is good to get an overview of jobs on a server– how many steps they have, how complex the steps are, The job description and category, and the basic last run date. I usually paste the results into a spreadsheet, do some minor formatting, and browse through the jobs.

To get a very high level overview, set the @showfullcommands parameter to 0, and it will only show step names, not the commands in the steps.

I also often reference this script when I’m creating other scripts where I want to pivot and dynamically create the number of columns I need.

What is this missing?

This doesn’t describe flow control in jobs. If jobs have lots of complicated “on Fail” and “on Success” actions, you won’t see that in this view. Also, this doesn’t represent start steps or end steps. I worked some of that into initial drafts, but it was just too much information to absorb in this format.

This also is not intended to back up jobs in any way.

The code

-- Display a chart of all jobs with jobsteps
use msdb;
set nocount on;

declare
    @query nvarchar(max)
    , @selectsql nvarchar(max)
    , @pivotsql nvarchar(max)
    , @showfullcommands bit

select
    @showfullcommands=1 --- change this to 1 to show the full command
            --a step is running (not just the step type/db and step name)
    , @selectsql=''
    , @pivotsql=''

select
@selectsql= @selectsql + '
    , Step'+ cast(step_id as nvarchar) + '= coalesce(['+ cast(step_id as nvarchar) + '], '''')'
, @pivotsql=@pivotsql
+  case step_id
    when 1
        then ''
    else ','
    end
+ '[' + cast(step_id as nvarchar) + ']'
from (select distinct step_id from msdb.dbo.sysjobsteps) sjs

--print @selectsql
--print @pivotsql

select @query='
select
    [Job Name]
    , [Enabled]
    , [Category Name]
    , [Desc]
    , [Last Run]
    , [Last Outcome]
    , Created
    , [Last Mod] '
+ @selectsql + '
from (
    select
        jb.job_id
        , [Job Name]=jb.name
        , jb.enabled
        , [Category Name]=sc.name
        , [Desc] = case jb.description
            when ''No description available.''
                then ''''
            else jb.description
            end
        , created=convert(char(8), jb.date_created, 1)
        , [Last Mod]=convert(char(8),jb.date_modified,1)
        , [Last Run]= convert (char(8),
                (select max(cast(cast(run_date as nvarchar)as datetime))
                from msdb.dbo.sysjobhistory jh  with (nolock)
                where jh.job_id=jb.job_id
                and step_id=0)
                , 1)
        , [Last Outcome]=
                (select case run_status
                    when 0 then ''Failed''
                    when 1 then ''Success''
                    when 2 then ''Retry''
                    when 3 then ''Canceled''
                    when 4 then ''In progress''
                    else cast(run_status as nvarchar)
                    end
                from msdb.dbo.sysjobhistory jh with (nolock)
                where jh.job_id=jb.job_id
                and instance_id =
                    (select max(instance_id)
                    from msdb.dbo.sysjobhistory jh2  with (nolock)
                    where jh2.job_id=jh.job_id
                    and jh2.step_id=0)
                )
        , js.step_id
        , stepDetail=case js.subsystem
            when ''CmdExec'' then ''CmdExec''
            when ''SSIS'' then ''SSIS''
            when ''TSQL'' then  js.database_name
            else ''?''
            end
            + '': ''
            + js.step_name '
            + case @showfullcommands
                when 1
                    then '+'' = '' + char(10) + js.command '
                else ''
            end
            + '
    from msdb.dbo.sysjobs jb with (nolock)
    left join msdb.dbo.syscategories sc with (nolock) on
        jb.category_id=sc.category_id
    left join msdb.dbo.sysjobsteps js with (nolock) on
        jb.job_id=js.job_id
) p
PIVOT (
    max(stepDetail)
    for step_id in (' + @pivotsql + ')
) as pvt
order by [Job Name]
'
--print @query
exec sp_executesql @query

Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.