Finding Jobs

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Tushar Kanti » Finding Jobs

16 posts, Page 2 of 2

««1 2

Finding Jobs

Rate Topic

Display Mode

Topic Options

Author

Message

nabidavid

Posted Wednesday, May 05, 2010 2:02 PM

SSC-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. Wink

It needs some explaining for the boss...

Post #916445

alan-1016944

Posted Wednesday, May 05, 2010 2:14 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 06, 2010 10:30 AM
Points: 1, Visits: 8

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

nberina

Posted Wednesday, May 05, 2010 4:06 PM

Forum 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

Janus Lin

Posted Thursday, May 06, 2010 7:16 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, December 17, 2012 1:19 PM
Points: 6, Visits: 63

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

thomas.briscoe

Posted Thursday, May 06, 2010 10:21 AM

Grasshopper

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

jshailendra

Posted Sunday, May 09, 2010 6:17 AM

Old Hand

Group: General Forum Members
Last Login: Friday, December 21, 2012 5:24 AM
Points: 366, Visits: 436

good stuf..Tushar..keep up the good work Smile

Post #918633

« Prev Topic | Next Topic »

16 posts, Page 2 of 2

««1 2

Permissions