SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Tushar Kanti / Finding Jobs / Latest Posts

RE: Finding Jobs

jshailendra — Sun, 09 May 2010 06:17:31 GMT

good stuf..Tushar..keep up the good work :-)

RE: Finding Jobs

thomas.briscoe — Thu, 06 May 2010 10:21:30 GMT

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 JOBINNER JOIN msdb.dbo.sysjobsteps AS STEP ON JOB.job_id = STEP.job_idRIGHT 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.

RE: Finding Jobs

Janus Lin — Thu, 06 May 2010 07:16:05 GMT

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

RE: Finding Jobs

nberina — Wed, 05 May 2010 16:06:07 GMT

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 DEPINNER JOIN SYSOBJECTS OBJON OBJ.ID=DEP.referenced_major_idWHERE obj.NAME = 'Department')AND obj.XTYPE = 'U'

RE: Finding Jobs

alan-1016944 — Wed, 05 May 2010 14:14:46 GMT

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

RE: Finding Jobs

nabidavid — Wed, 05 May 2010 14:02:41 GMT

I was worried the title of the article would get me fired. ;-) It needs some explaining for the boss...

RE: Finding Jobs

steven.malone — Wed, 05 May 2010 10:48:26 GMT

The problem is not just the sysdepends is deprecated, it is not accurate.Example: create a tablecreate a procedure that selects from the tabledrop the table (which deletes records in sysdepends)create a table with the same nameThe procedure still runs, still depends on the table, but no record in sysdependsEven if they fixed that problem in 2005/2008 you still won't have tables referenced in dynamic SQL, etc..It is not that hard to write some code to search syscomments (or sys.sql_modules) (like the code in the article searching the job command field)Of course it helps if your naming convention is such that it reduces false positives.

RE: Finding Jobs

Francis Apel — Wed, 05 May 2010 10:03:42 GMT

Try sys.dm_sql_referenced_entities or sys.dm_sql_referencing_entities instead. Otherwise it's a nice idea.Oh wait, I just re-read and saw you were doing this for 2000. Didn't know anyone was still using 2000... ;-)

RE: Finding Jobs

SQLRNNR — Wed, 05 May 2010 10:03:13 GMT

tushkieeeeeNice article.

RE: Finding Jobs

SQLRNNR — Wed, 05 May 2010 10:02:36 GMT

[quote][b]Trey Staker (5/5/2010)[/b][hr]Thanks for the article. I almost did read it because I have a job and am not looking. :w00t: :-D :hehe: :laugh:[/quote]I thought the same thing when I first saw the title.

RE: Finding Jobs

SQLRNNR — Wed, 05 May 2010 10:01:56 GMT

[quote][b]Nitya (5/5/2010)[/b][hr]sysdepends is going to be deprecated in future releases.While it runs perfectly on older editions, do not get addicted to it[/quote]As will sysobjects be deprecated.

RE: Finding Jobs

Nitya — Wed, 05 May 2010 09:54:15 GMT

sysdepends is going to be deprecated in future releases.While it runs perfectly on older editions, do not get addicted to it

RE: Finding Jobs

Trey Staker — Wed, 05 May 2010 09:02:05 GMT

Thanks for the article. I almost did read it because I have a job and am not looking. :w00t: :-D :hehe: :laugh:

RE: Finding Jobs

alen teplitsky — Wed, 05 May 2010 08:10:01 GMT

thxgoing to have to try this to see how it worksedit: does this work with DTS packages? we have a lot of jobs that are DTS or SSIS packages and not just running stored procedures

RE: Finding Jobs

sqlusers — Wed, 05 May 2010 06:20:22 GMT

Nice Article! It's good to learn for new DBA's like me. Thanks for sharing.

Finding Jobs

tushkieeeee — Wed, 05 May 2010 00:02:55 GMT

Comments posted to this topic are about the item [B]Finding Jobs[/B]