Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Finding Jobs Expand / Collapse
Author
Message
Posted Wednesday, May 05, 2010 2:02 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, June 19, 2012 9:31 AM
Points: 421, Visits: 78
I was worried the title of the article would get me fired. It needs some explaining for the boss...
Post #916445
Posted Wednesday, May 05, 2010 2:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 11:39 AM
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
Post #916462
Posted Wednesday, May 05, 2010 4:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 20, 2010 3:13 PM
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'
Post #916552
Posted Thursday, May 06, 2010 7:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 7:07 AM
Points: 6, Visits: 64
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
Post #916924
Posted Thursday, May 06, 2010 10:21 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 27, 2011 4:13 PM
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)
Post #917198
Posted Sunday, May 09, 2010 6:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 7:12 AM
Points: 366, Visits: 455
good stuf..Tushar..keep up the good work
Post #918633
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse