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.
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;
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