List All Jobs & Their Steps

• jarid.lawson

  SSCrazy

  Points: 2953

  More actions

  July 31, 2013 at 12:21 pm

  #281299

  This is another of my quick scripts that I want to store where I can find easily. If you want to take it, please help yourself.

  This script will return all SQL Agent jobs, and give a step by step break down of what the job does. I included a column called Date_List_Generated to ensure I was looking at the latest version.

  Select sJob.Name As Job_Name

  ,sJob.Description

  ,sJob.Originating_Server

  ,sJob.Start_Step_ID As Start_At_Step

  ,Case

  When sJob.Enabled = 1

  Then 'Enabled'

  When sJob.Enabled = 0

  Then 'Not Enabled'

  Else 'Unknown Status'

  End As Job_Status

  ,Replace(Replace(sCat.Name,'[',''),']','') As Category

  ,sJStp.Step_ID As Step_No

  ,sJStp.step_name AS StepName

  ,Case sJStp.SubSystem

  When 'ActiveScripting'

  Then 'ActiveX Script'

  When 'CmdExec'

  Then 'Operating system (CmdExec)'

  When 'PowerShell'

  Then 'PowerShell'

  When 'Distribution'

  Then 'Replication Distributor'

  When 'Merge'

  Then 'Replication Merge'

  When 'QueueReader'

  Then 'Replication Queue Reader'

  When 'Snapshot'

  Then 'Replication Snapshot'

  When 'LogReader'

  Then 'Replication Transaction-Log Reader'

  When 'ANALYSISCOMMAND'

  Then 'SQL Server Analysis Services Command'

  When 'ANALYSISQUERY'

  Then 'SQL Server Analysis Services Query'

  When 'SSIS'

  Then 'SQL Server Integration Services Package'

  When 'TSQL'

  Then 'Transact-SQL script (T-SQL)'

  Else sJStp.SubSystem

  End As Step_Type

  ,sJStp.database_name AS Database_Name

  ,sJStp.command AS ExecutableCommand

  ,Case sJStp.on_success_action

  When 1

  Then 'Quit the job reporting success'

  When 2

  Then 'Quit the job reporting failure'

  When 3

  Then 'Go to the next step'

  When 4

  Then 'Go to Step: '

  + QuoteName(Cast(sJStp.On_Success_Step_ID As Varchar(3)))

  + ' '

  + sOSSTP.Step_Name

  End As On_Success_Action

  ,sJStp.retry_attempts AS RetryAttempts

  ,sJStp.retry_interval AS RetryInterval_Minutes

  ,Case sJStp.on_fail_action

  When 1

  Then 'Quit the job reporting success'

  When 2

  Then 'Quit the job reporting failure'

  When 3

  Then 'Go to the next step'

  When 4

  Then 'Go to Step: '

  + QuoteName(Cast(sJStp.On_Fail_Step_ID As Varchar(3)))

  + ' '

  + sOFSTP.step_name

  End As On_Failure_Action

  ,GetDate() As Date_List_Generated

  From MSDB.dbo.SysJobSteps As sJStp

  Inner Join MSDB.dbo.SysJobs As sJob

  On sJStp.Job_ID = sJob.Job_ID

  Left Join MSDB.dbo.SysJobSteps As sOSSTP

  On sJStp.Job_ID = sOSSTP.Job_ID

  And sJStp.On_Success_Step_ID = sOSSTP.Step_ID

  Left Join MSDB.dbo.SysJobSteps As sOFSTP

  On sJStp.Job_ID = sOFSTP.Job_ID

  And sJStp.On_Fail_Step_ID = sOFSTP.Step_ID

  Inner Join MSDB..SysCategories sCat

  On sJob.Category_ID = sCat.Category_ID

  Order By Job_Status

  ,Job_Name;

  [font="Arial"]“Any fool can know. The point is to understand.”
  - Albert Einstein

  "DOH!"
  - Homer Simpson[/font]

• Lowell

  SSC Guru

  Points: 323518

  More actions

  July 31, 2013 at 12:33 pm

  #1637518

  Jared i tried it on my 2012/2008 and 2005 instances,a nd all three returned an error referencing the column sJob.Originating_Server

  Msg 207, Level 16, State 1, Line 3

  Invalid column name 'Originating_Server'.

  do you know offhand what the issue is? i can change it to sJob.originating_server_id, and it works; maybe it's a so12 verison higher than my develper version that has a new column?

  Microsoft SQL Server 2012 - 11.0.2100.60 (X64)

  Feb 10 2012 19:39:15

  Copyright (c) Microsoft Corporation

  Developer Edition (64-bit)

  on Windows NT 6.1 <X64>

  (Build 7601: Service Pack 1)

  11.0.2100.60

  RTM

  Developer Edition (64-bit)

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• jarid.lawson

  SSCrazy

  Points: 2953

  More actions

  July 31, 2013 at 12:36 pm

  #1637521

  It might be the version issue. We are running SS2k here (we get to migrate to 08 r2 soon :-D), so there may need to be some small tweaks. Let me know what you come up with.

  [font="Arial"]“Any fool can know. The point is to understand.”
  - Albert Einstein

  "DOH!"
  - Homer Simpson[/font]