List jobs residing on a particular database

On October 4, 2012 By sqlpathyIn SQL Server, SQL Server agent, SQL Server Job, T-SQL

Below query become very handy when it is required to list all the jobs residing on a specific database.

DECLARE @db_name VARCHAR(100);

SET @db_name = 'My_Database'; -- 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;

If it is required to get the complete list of jobs on a server along with the database they reside on, below query helpful.

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;