List of jobs associated with database in SQL Server
Table of Contents
Introduction
As a DBA I was asked to list out all job in a production SQL Server with respect to the database. Approx 200+ jobes were running and 20+ databases were online. Task seems not so hard, but as I am not so good in query writing. Google was my first and last hope. After a couple of searches, I found two wonderful scripts from a blog. Below both the script will list out jobs associated with the database in a SQL Server.
Script 01
Below query becomes very handy when it is required to list all the jobs residing on a specific database.
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @db_name VARCHAR(100); SET @db_name = 'OurTechIdeas.com'; -- Change your database name here SELECT database_name [Database Name], name [Job Name], js.job_id [Job ID] FROM msdb.dbo.sysjobsteps js INNER JOIN msdb.dbo.sysjobs_view jv ON js.job_id = jv.job_id WHERE database_name = @db_name; |
Result
Script 02
If it is required to get the complete list of jobs on a server along with the database they reside on, below query helpful.
|
1 2 3 4 5 6 7 |
SELECT DISTINCT database_name [Database Name], name [Job Name], js.job_id [Job ID] FROM msdb.dbo.sysjobsteps js INNER JOIN msdb.dbo.sysjobs_view jv ON js.job_id = jv.job_id ORDER BY database_name; |
Result
Source: http://j.gs/DGar