[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 142
  • Last Modified:

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
0
AXISHK
Asked:
AXISHK
  • 3
  • 2
  • 2
2 Solutions
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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