Question: Listing jobs that run against a specific database

Question

Question: Listing jobs that run against a specific database

anidem1225

Grasshopper

Points: 16
More actions
September 25, 2007 at 2:27 pm

#70254

Greetings,
I've been trying to come across a script for SQL 2000 (or maybe 2005 too if the syntax will be compatible with both) that will list any jobs on a server that have steps that run against a specific database.
For example, if I have a database called "SERVERCENTRAL" I'd like the script to look at all the jobs on the server, with steps that run against database "SERVERCENTRAL", and return back a listing of the job names that contain those steps.
Does anyone possibly have a script like this?
Thank you!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

gotta watch for any scripts that use sp_MSFOREACHDB and also that selects from master.dbo.sysdatabases as well. any script susing that wouldn't explicitly say the db name, but might be using it in a cursor or loop.

theres a good vbscript to export your jobs here:

http://www.sqlservercentral.com/scripts/Backup+%2F+Restore/30758/

and using the google feature of the new site to "script jobs" or "job steps" finds a lot of examples to query the msdb database for job information.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

You could try querying msdb.dbo.sysjobsteps and search for the database name. Something like this where 'DBName' = your database name:

select j.name from sysjobsteps s join sysjobs j on s.job_id = j.job_id

where command like '%DBName%'

or database_name = 'DBName'

This will run in either SQL 2000 or SQL 2005.

Greg

anidem1225 Grasshopper Points: 16 More actions · Answer 3

Greg,

This worked great for me. Thanks for your help, much appreciated!

Greg Charles (9/25/2007)
You could try querying msdb.dbo.sysjobsteps and search for the database name. Something like this where 'DBName' = your database name:
select j.name from sysjobsteps s join sysjobs j on s.job_id = j.job_id
where command like '%DBName%'
or database_name = 'DBName'
This will run in either SQL 2000 or SQL 2005.
Greg