Finding Jobs

Question

Post reply

Finding Jobs

Tushar Kanti

Ten Centuries

Points: 1145
More actions
May 5, 2010 at 12:02 am

#218543

Comments posted to this topic are about the item Finding Jobs

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

sqlusers SSCrazy Points: 2463 More actions · Answer 1

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

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 2

thx

going to have to try this to see how it works

edit: does this work with DTS packages? we have a lot of jobs that are DTS or SSIS packages and not just running stored procedures

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 3

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

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

Nitya Say Hey Kid Points: 699 More actions · Answer 4

sysdepends is going to be deprecated in future releases.

While it runs perfectly on older editions, do not get addicted to it

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Nitya (5/5/2010)
sysdepends is going to be deprecated in future releases.
While it runs perfectly on older editions, do not get addicted to it

As will sysobjects be deprecated.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Trey Staker (5/5/2010)
Thanks for the article. I almost did read it because I have a job and am not looking. :w00t: 😀 :hehe: :laugh:

I thought the same thing when I first saw the title.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

tushkieeeee

Nice article.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Francis Apel SSC Eights! Points: 918 More actions · Answer 8

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... 😉

Francis
-----------------
SQLRanger.com

steven.malone Say Hey Kid Points: 688 More actions · Answer 9

The problem is not just the sysdepends is deprecated, it is not accurate.

Example:

create a table

create a procedure that selects from the table

drop the table (which deletes records in sysdepends)

create a table with the same name

The procedure still runs, still depends on the table, but no record in sysdepends

Even 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.

nabidavid SSC Eights! Points: 815 More actions · Answer 10

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

alan-1016944 Grasshopper Points: 11 More actions · Answer 11

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 Valued Member Points: 52 More actions · Answer 12

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 SSC Enthusiast Points: 100 More actions · Answer 13

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 SSC-Addicted Points: 488 More actions · Answer 14

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)