Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is it possible to get a list of all jobs that access a database in SQL? I can't figure a way to do this. I can get a list of all jobs but not what dbs they access.


share|improve this question

This is what I use:

use master

convert(sysname, rtrim(loginame)) as loginname, db_name(dbid) as dbname, 
hostname, program_name
sys.sysprocesses with (nolock)

This will return the database name in the 2nd column.

share|improve this answer
Thanks, I forgot to state that I am using sql2000 – Paul Feb 21 '12 at 13:42

This will work for Agent jobs that have TSQL job steps pointing to a database.

USE msdb
FROM sysjobs j
INNER JOIN sysjobsteps js ON j.job_id=js.job_id
WHERE js.database_name='MyDatabase'

This, however, will not catch things where databases are accessed from fully qualified name references in code. Nor will it catch other things like SSIS job steps where the package references your database.

share|improve this answer
Thanks, I forgot to state that I am using sql2000, Paul – Paul Feb 21 '12 at 13:43
Should still work in SQL 2000 – squillman Feb 21 '12 at 15:18

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.