A Table Summarizing All Agent Jobs with Steps...

on April 19, 2009

Each time I work with a new system, it can take a while to familiarize myself with what all the SQL Server Agent jobs do. Often there are quite a few jobs, and sometimes they have legacy names that either don’t describe what the job does very well anymore, or is just hard to understand.

Plus, I don’t like opening jobs in the SQL Agent itself very much, since it only opens in an ‘edit’ view. I very much prefer selecting job details out of the tables in msdb, it’s just safer.

Because of this, a while back I wrote a SQL script that takes a lot of descriptive information about a job in MSDB and pivots it out into a table. The table will automatically have as many columns as are required– I have a server where a job has 41 steps, so it’s got 41 columns for step, each in order.

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