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:

SQL-Agent-Jobs-Status-Code

Results:

SQL-Agent-Jobs-Status-Results

Code:

SELECT
   jobs.Job_id
   ,steps.Database_name AS DatabaseName
   ,jobs.Name AS JobName
   ,JobStatus = CASE
      WHEN jobs.Enabled = 1 THEN 'Active'
      ELSE 'InActive'
   END
   ,jobs.Description
   ,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.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s