List all active/inactive SQL Agent Jobs (disabled/enabled)

How to retrieve a complete list of all SQL Agent Jobs that includes whether the jobs are active/inactive (enabled/disabled)?

You might want to use this T-SQL statement:





   ,steps.Database_name AS DatabaseName
   ,jobs.Name AS JobName
   ,JobStatus = CASE
      WHEN jobs.Enabled = 1 THEN 'Active'
      ELSE 'InActive'
   ,steps.Step_name AS StepName
   ,steps.Command AS SQLCommandUsed
   ,steps.Server AS ServerName
FROM msdb.dbo.sysjobs jobs
   INNER JOIN msdb.dbo.sysjobsteps steps
      ON jobs.job_id = steps.job_id

More info about these system tables can be found on MSDN: click here.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s