Saturday, August 28, 2010

Getting Job Category for the SQL Server Agent Jobs

Getting Job Category

There are Job categories associated with SQL Server Agent Jobs. As you know you can get the job information by querying sysjobs system table.

SELECT J.name as jobName,C.name Category FROM sysjobs J

INNER JOIN syscategories C ON J.category_id = C.category_id

WHERE C.category_class = 1

Now you can see this is not huge query. But the problem is Microsoft documentation, If you go the sysjobs documentation as shown in the following image, id does not say from which table you should get the category from. Since job category table does not have job prefix it is bit difficult to find this out.

clip_image002

But the documentation not that bad since you have http://msdn.microsoft.com/en-us/library/ms181367.aspx page which will tell you all the related tables for the Agent Jobs. However, it would have been much better if this information is mention at the sysjobs documentation itself.

No comments:

Post a Comment