List all SQL Schedule job in SQL Server

How to list all all schedule SQL Jobs that called  up a particular stored procedure or update a table in SQL Server 2008R2 ?

Tks
AXISHKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phil DavidsonCommented:
Try
 exec sp_help_job where @job_name = 'nameOfJobHere' 

Open in new window


Variations of sp_help_job may work for you.

Can you clarify that you want to list the SQL Jobs that invoke a given stored procedure?  I'm not sure about the "update a  table" portion of your request.  Do you want to know the tables that a given stored procedure updates too?

What do you know about the stored procedures? (e.g., name only?)
0
AXISHKAuthor Commented:
The SQL schedule job will can either schedule a SQL proc or put a statement on it. How can I list the SQL procedure that contain the name of SQL proc or table.  Tks
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The sp_help_job will give you the command used by the job, so you can use Phil's solution for that.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

AXISHKAuthor Commented:
exec sp_help_job where @job_name = 'AutoFormData'

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'where'.


Any idea ??
0
Phil DavidsonCommented:
Sorry, I made a small mistake. Try this:

USE msdb ;
GO
EXEC sp_help_job 
@job_name = 'AutoFormData' ;
GO

Open in new window

0
AXISHKAuthor Commented:
The query need to specify the SQL Job Name. But in case I have a table (or a name of the procedure) but I want to know which SQL job has referred to it. How can I do this ?

Tks
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
For that you need to query sysjobssteps:
USE msdb
GO

SELECT j.name
FROM sysjobsteps s
	INNER JOIN sysjobs j ON j.job_id=s.job_id
WHERE s.command LIKE '%TableName%'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.