Finding Jobs

  • Comments posted to this topic are about the item Finding Jobs

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

  • 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

  • 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

  • sysdepends is going to be deprecated in future releases.

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

  • 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

  • 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

  • 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

  • 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

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

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

  • 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

  • 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'

  • 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

  • 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 15 total)

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