Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding Jobs


Finding Jobs

Author
Message
nabidavid
nabidavid
SSC-Addicted
SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)

Group: General Forum Members
Points: 437 Visits: 78
I was worried the title of the article would get me fired. ;-) It needs some explaining for the boss...
alan-1016944
alan-1016944
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 9
From a super newbie. It would be sooo helpful if someone would update this article for SQL2008.

Thanks, I know this is need comes up all the time.

Alan
nberina
nberina
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 5
What you need to do is to to use sys.sql_dependencies instead of SYSDEPENDS.
Just replace the field in the join and you'll be able to retrieve the info the article refers to. Also, it is better to use xtype instead of type field, type is there for backward compatibility only.
-------------------
SELECT obj.NAME FROM SYSOBJECTS obj
WHERE obj.ID IN
(SELECT DISTINCT DEP.referenced_major_id FROM sys.sql_dependencies DEP
INNER JOIN SYSOBJECTS OBJ
ON OBJ.ID=DEP.referenced_major_id
WHERE obj.NAME = 'Department')
AND obj.XTYPE = 'U'
Janus Lin
Janus Lin
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 66
Helpful article!

I am using the query in Phase 1 as a starting point for trying to put together a visual calendar of scheduled jobs. I still need to find the tables that will show the actual schedule (frequency, times, etc.)

In looking at the other columns in these two tables, perhaps something to consider is filtering or displaying the Job.Enabled column.

Thanks!

Janus
thomas.briscoe
thomas.briscoe
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 87
I was unable to make use of the Phase II code, but had very good success using the code from Phase I. I am using SQL Server 2005.
In fact, I modified the code to list the jobs, steps, whether each job was enabled, and the command text for a project on which I am working.

SELECT
akTables.name AS 'Table Name',
JOB.name AS 'Job Name',
STEP.step_name AS 'Step Name', -- include Step Name
CASE JOB.enabled WHEN 1 THEN 'Yes' ELSE 'No' END AS 'Job Enabled', -- Job enabled?
STEP.command AS 'Step Command'
FROM
msdb.dbo.sysjobs AS JOB
INNER JOIN msdb.dbo.sysjobsteps AS STEP
ON JOB.job_id = STEP.job_id
RIGHT OUTER JOIN -- right join on each table name in the defined schema; NULL results indicate no jobs refer to table.
(
SELECT
name
FROM
sys.objects
WHERE
(type = 'U') -- table
AND
(schema_id = schema_id('ak')) -- ak schema
) akTables
ON STEP.command LIKE '%' + akTables.name + '%'

In the project, my tables are separate in a schema I defined "ak". You can, of course, add sysjobs or sysjobsteps columns for more information.

(58.30115757480578, -134.4143772125244)
jshailendra
jshailendra
Old Hand
Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)

Group: General Forum Members
Points: 384 Visits: 455
good stuf..Tushar..keep up the good work :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search