Finding Jobs

  • Tushar Kanti

    Ten Centuries

    Points: 1142

    Comments posted to this topic are about the item Finding Jobs

  • sqlusers

    SSCrazy

    Points: 2463

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

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    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: 4736

    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

    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: 281243

    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: 281243

    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: 281243

    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

    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

    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

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

  • alan-1016944

    Grasshopper

    Points: 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

    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

    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

    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)

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

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