List of jobs associated with database in SQL Server

Introduction

As a Database Administrator (DBA), my recent task involved listing all the jobs running in a production SQL Server environment, along with their respective databases. With over 200+ jobs and 20+ online databases, I needed an efficient solution to accomplish this seemingly challenging task. Despite my limited expertise in query writing, I relied on the power of Google to seek assistance. Fortunately, my quest led me to an enlightening blog that offered two exceptional scripts to fulfill my requirement. In this article, I’ll share these invaluable scripts that can help you effortlessly identify jobs associated with specific databases in SQL Server.

Script 01

Below query becomes very handy when you required to list all the jobs residing on a specific database.

-- Declare the variable to hold the database name
DECLARE @db_name VARCHAR(100);

-- Set the database name you want to query
SET @db_name = 'msdb'; -- Change your database name here

-- Retrieve the job information for the specified database
SELECT 
    database_name AS [Database Name],
    name AS [Job Name],
    js.job_id AS [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.

SELECT DISTINCT
    database_name AS [Database Name],
    name AS [Job Name],
    js.job_id AS [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; -- Sort the results by database name

-- Retrieve distinct job information from the sysjobsteps and sysjobs_view tables
-- based on the join condition of the job_id columns

-- Select the database_name column as 'Database Name'
-- Select the name column as 'Job Name'
-- Select the job_id column as 'Job ID'

-- Perform an inner join between sysjobsteps and sysjobs_view tables
-- using the job_id column as the join condition

-- Return the results in ascending order based on the database_name column