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.