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.
-- 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) + '], '''')' , @[email protected] + 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