SQL Server Agent Job Steps Setup and Configuration Information
Definition Description
https://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/ The following query gives us the Job Step level Setup and Configuration information, which can also be found in the Job Step Properties window in SSMS.
SELECT [sJOB].[job_id] AS [JobID] , [sJOB].[name] AS [JobName] , [sJSTP].[step_uid] AS [StepID] , [sJSTP].[step_id] AS [StepNo] , [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 [StepType] , [sPROX].[name] AS [RunAs] , [sJSTP].[database_name] AS [Database] , [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 [OnSuccessAction] , [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 [OnFailureAction] 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] LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX] ON [sJSTP].[proxy_id] = [sPROX].[proxy_id] ORDER BY [JobName], [StepNo]
