Solved

List all SQL Schedule job in SQL Server

Posted on 2014-12-02
7
125 Views
Last Modified: 2014-12-07
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
Comment
Question by:AXISHK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 7

Expert Comment

by:Phil Davidson
ID: 40477895
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
 

Author Comment

by:AXISHK
ID: 40477936
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40478126
The sp_help_job will give you the command used by the job, so you can use Phil's solution for that.
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:AXISHK
ID: 40480046
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
 
LVL 7

Assisted Solution

by:Phil Davidson
Phil Davidson earned 200 total points
ID: 40480125
Sorry, I made a small mistake. Try this:

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

Open in new window

0
 

Author Comment

by:AXISHK
ID: 40480347
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
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 300 total points
ID: 40480506
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question