• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

Identify Agent jobs which target a specific DB

We have a SQL Server 2012 with multiple databases and several SQL Server Agent jobs.  Is there a way for me to quickly identify jobs which contain steps affecting a specific DB?
0
canuckconsulting
Asked:
canuckconsulting
  • 2
2 Solutions
 
Anthony PerkinsCommented:
1.  Check all the steps where the following is true:
SELECT  *
FROM    dbo.sysjobs j
        INNER JOIN sysjobsteps s ON j.job_id = s.job_id
WHERE   s.database_name = 'YourDatabaseName'

2. Check the contents of the command column for the database name as in:
SELECT  *
FROM    dbo.sysjobs j
        INNER JOIN sysjobsteps s ON j.job_id = s.job_id
WHERE   CHARINDEX('YourDatabaseName.', command) > 0
0
 
Scott PletcherSenior DBACommented:
Anthony's code looks good, except:

I wouldn't include the "." after the database name, because rather than multi-part object name -- db_name.schema.table_name -- the code might contain "use db_name select ... from schema.table_name".


Also, be aware that of course a proc called from a job may reference any number of other dbs within that proc, which the above code cannot identity.

For example, say you are looking for references to dbA.

An existing job might have code that runs:
EXEC dbB.dbo.proc1
and the proc could then reference objects in dbA.

There's really not a good way to programmatically trace all that down.
0
 
Anthony PerkinsCommented:
Anthony's code looks good, except:
I did think of that but I wanted to avoid any false positives.  But you make a good point.
0
 
canuckconsultingAuthor Commented:
Thanks guys!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now