SQL Server Agent System Stored Procedures

A followup post to Sql Server Agent Tables.

In addition to the tables, the are already system stored procedures that will get the information for you.  I've included some of the man arguments.  More information on the stored procedures can be seen at the Microsoft msdn link.

• sp_help_job
• if you run this by itself it will show all your jobs
• Arguments:Or you can Pass in the @job_id or @job_name 
  
• sp_help_jobActivity
• shows the status of the job run.  
• Arguments:  @job_ID or @job_Name
  
•  sp_help_jobHistory
• shows all the history information for all of the job runs
• Arguments:  @job_ID or @job_Name
  
• sp_help_jobCount
  
• Arguments: @schedule_id or @Schedule_name 
• it will return a count o how many jobs a schedule is tied to.

• sp_help_jobs_in_schedule
• Arguments: @schedule_id or @Schedule_name 
• it will return a a list of all jobs tied to that schedule.sp_help_job_schedule
  
• sp_help_jobSchedule
• Shows jobs that are linked to a schedule
• Arguments: @job_id or @job_name
  
•  sp_help_jobServer
• Shows information about server tied to a job
• Arguments: @job_id or @job_name
  
• sp_help_jobStep
• Shows information about steps tied to a job
• Arguments: @job_id or @job_name
  
• sp_help_jobStepLog
• Shows information about a specific job steplog
• Arguments: @job_id or @job_name 
•                  @step_id or @step_name
  
• sp_help_schedule
•  Shows information for schedule
• Pass in the @schedule_id or @Schedule_name or no parameters for all

Sql Server Agent Tables

SQL Server stores the SQL Server Agent Jobs in various tables in the msdb.  This provides you an easy way to view jobs, their steps as well as their run history.

• sysJobs
• Stores the name, job_ID and related information
  
• sysJobSchedules
• Shows the schedules for the jobs
• Arguments: Uses Job_ID to link to sysJobs
• sysJobSteps
• Shows each step in a job.  Includes the command (actual code used), database and related information
• Arguments: Uses job_id to link to sysJobs
•  sysJobHistory
• Shows the  run history for that job, status, date, run time and duration to complete
• Arguments: Uses Job_ID and step_ID
• sysJobServers
• Stores server related information for jobs
• sysJobActivity
• Stores data about the job activity
• sysJobStepsLogs
• If it is enabled, it will show specific job step information

Code used to get various information:

use msdb

DECLARE @jobID varchar(50)

-- Get the jobID for a specific job

SET @jobID = ( select job_ID FROM sysJobs where name = 'ETL_Test' )

-- Get the job Information

SELECT * FROM sysJobs     where job_id = @jobID

-- Get the steps for that job

Select * from sysJobSteps where job_id = @jobID order by step_id

-- Show all jobs that use the storedProcedure xs_myTest

SELECT * FROM sysJobSteps where command like '%xs_myTest%'

In addition to the tables there are also System Stored Procedures.