0

Is there a way to list in Sql Server all the jobs which operate on a mentioned Sql Server object, for example a certain table (directly or indirectly through a stored procedure for example)? An example: I am interested in a certain table or view, so if there is a job which invokes (in any step) a procedure, which in its turn does any operation on that table/view (like insert or select or update etc), I'd like to see it listed.

Side note: sp_depends seems to list only the procedures and functions, but not the jobs.

John
  • 2,820
  • 3
  • 30
  • 50
Janine
  • 293
  • 3
  • 6
  • 13

1 Answers1

0

There is no built-in way to find indirect dependencies. You can check which jobs directly call a certain stored procedure, but no way to see which tables or other objects get used by that stored procedure unless you check the direct dependencies of that stored procedure.

So if you are looking for a specific table, you can find objects that reference it directly, but not objects that reference THOSE objects (indirect dependency).

You would either have to write your own script or search around and see if someone else has written one.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • Thank you, Tab Alleman! And how do I find what **which jobs** directly call a certain sproc or operate on a certain table? It doesn't work with `sp_depends` – Janine Aug 06 '15 at 13:20
  • http://stackoverflow.com/questions/18105547/how-to-find-all-sql-agent-jobs-that-call-a-given-stored-proc – Tab Alleman Aug 06 '15 at 13:47