Tuesday, February 12, 2019

Interview Question : How to get a list of SQL Server Agent Jobs in SQL Server ?

Here i am writing  a simple query to find the list of all Jobs currently running on SQL Server. We can write below simple query to find the "sysjobs" on each server.


Example:-
   SELECT job_id, [name] FROM msdb.dbo.sysjobs;
Below query for list of all the jobs currently running on server:

Query 1: For Return All Jobs:

SELECT job.job_id, notify_level_email, name, enabled, description, step_name, command, server, database_name FROM msdb.dbo.sysjobs job INNER JOIN msdb.dbo.sysjobsteps steps ON job.job_id = steps.job_id;


Query 2: For Return All Enabled Jobs:

SELECT job.job_id, notify_level_email, name, enabled, description, step_name, command, server, database_name FROM msdb.dbo.sysjobs job INNER JOIN msdb.dbo.sysjobsteps steps ON job.job_id = steps.job_id WHERE job.enabled = 1;

Query 3: Now the query to gets the category name and filters out the report server jobs:

       SELECT sysjobs.name 'Job Name', syscategories.name 'Category', CASE [description] WHEN 'No Description available.' THEN '' ELSE [description] END AS 'Description' FROM msdb.dbo.sysjobs INNER JOIN msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id WHERE syscategories.name <> 'Report Server' ORDER BY sysjobs.name;


No comments:

Post a Comment