4

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.

Thanks

1
  • Are you looking for capturing this at a specific point in time, or over a range of time? Sep 27, 2019 at 19:13

3 Answers 3

2

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

USE msdb
SELECT DISTINCT j.name AS JobName
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.

1
  • Should still work in SQL 2000
    – squillman
    Feb 21, 2012 at 15:18
0

This is what I use:

use master
go

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

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

0
0

you can use below query which is the same as the first reply with just one change to go over all databases:

USE msdb
SELECT DISTINCT j.name AS JobName, database_name
FROM sysjobs j
INNER JOIN sysjobsteps js ON j.job_id=js.job_id
WHERE js.database_name in (select name from sys.databases)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .