Finding SQL Jobs associated with stored procs

[mgm]

I have sql client tools installed on my desktop; I do not have access to see
SQL jobs on a certain database. Is there a way I can see if there is a job
associated with a specific stored proc? Perhaps another stored procedure? (I
was thinking along the lines of sys_depends, something like that).

Thank you,
Geniene

[Aaron Bertrand [SQL Server MVP]]

No, nothing "finds" stored procedures that are mentioned in job steps. What
do you mean you "do not have access to see SQL jobs"? Are you not
permitted, or are you running SSMS Express? If the latter, you can look at
the text of job steps via

SELECT command FROM msdb..sysjobsteps;

Post by mgm
I have sql client tools installed on my desktop; I do not have access to see
SQL jobs on a certain database. Is there a way I can see if there is a job
associated with a specific stored proc? Perhaps another stored procedure? (I
was thinking along the lines of sys_depends, something like that).
Thank you,
Geniene

[mgm]

I know the stored procedure, what I want to find is the job name - if it
exists. I was asked to find out if a certain stored proc is running weekly
deleting rows in a table in our QA environment. I do not have permissions to
see the jobs, I can't see the SQL Server Agent folder in SQL Server
Management Studio and the person that can give me permissions comes in late.
I just wanted to verify if a job runs weekly that is using this stored
procedure.

Thanks.

Post by Aaron Bertrand [SQL Server MVP]
No, nothing "finds" stored procedures that are mentioned in job steps. What
do you mean you "do not have access to see SQL jobs"? Are you not
permitted, or are you running SSMS Express? If the latter, you can look at
the text of job steps via
SELECT command FROM msdb..sysjobsteps;

Post by mgm
I have sql client tools installed on my desktop; I do not have access to see
SQL jobs on a certain database. Is there a way I can see if there is a job
associated with a specific stored proc? Perhaps another stored procedure? (I
was thinking along the lines of sys_depends, something like that).
Thank you,
Geniene

[Aaron Bertrand [SQL Server MVP]]

Post by mgm
I know the stored procedure, what I want to find is the job name

Untested:


SELECT j.name, s.command
FROM msdb..sysjobs j
INNER JOIN msdb..sysjobsteps s
ON j.job_id = s.job_id
WHERE s.command LIKE '%procedure_name%';

[mgm]

"SELECT permission was denied ..."

Thanks for your help - I at least learned something! :)

Post by Aaron Bertrand [SQL Server MVP]

Post by mgm
I know the stored procedure, what I want to find is the job name

SELECT j.name, s.command
FROM msdb..sysjobs j
INNER JOIN msdb..sysjobsteps s
ON j.job_id = s.job_id
WHERE s.command LIKE '%procedure_name%';

[Anith Sen]

Look up msdb.dbo.sysjobsteps table. It should have a column namely command
that lists the stored procedures used in SQL jobs.

[mgm]

I still dont have permissions "SELECT permission was denied"
Thank you anyway for your help...

Post by Anith Sen
Look up msdb.dbo.sysjobsteps table. It should have a column namely command
that lists the stored procedures used in SQL jobs.
--
Anith

[Aaron Bertrand [SQL Server MVP]]

Well, they can't expect you to do something they've explicitly prevented you
from doing. So you have two choices... have them grant you access, or pass
your questions to someone who already has access...

A

Post by mgm
I still dont have permissions "SELECT permission was denied"
Thank you anyway for your help...