Finding Jobs

There are times when we must find out which SQL Server jobs will be affected by the change in a procedure or a table. If there is a situation that we have a schema change in a table and we need to check whether the critical SQL jobs are affected by the change. We need to identify all the procedures dependent on the table and then confirm which critical SQL jobs reference the procedures affected. It can be really painful at times.

In this article I will try to explain how to find the jobs related to

1. a procedure
2. a table

I have divided the article in two parts so that it'll be easier to understand.

Note: These queries are tested for SQL Server 2000 and they might not work properly in other versions. You will need sysadmin level privileges on the instance to run these queries.

Phase I

Now in this phase of the article I would like to show you the how to find the jobs dependent on a particular procedure.

SELECT JOB.NAME, STEP.COMMAND
FROM MSDB..SYSJOBS JOB
INNER JOIN MSDB..SYSJOBSTEPS STEP
ON JOB.JOB_ID = STEP.JOB_ID
WHERE STEP.COMMAND LIKE  '%s_upd_CustomerPtoc%'

In this query we have used two systables: sysjobs and sysjobsteps. The SYSJOBS table in MSDB database contains the entries of all jobs in the instance and the SYSJOBSTEPS table contains the entries of all the steps related to a job in the instance.

Every job has a job_id associated with it and all the job steps associated to that job have the same job_id. We have exploited this using a inner join, and we have retrieved all the job steps related to a job. There is a COMMAND column in the SYSJOBSTEPS table that contains up to 6400 chars of the actual command in the job step. We need to find the procedure that is being called in the job steps for that we need to exploit the like query.

This is the output you will get of the code executed. The NAME column tells us the name of the job that has been identified as the dependent of the procedure and the COMMAND column is the actual job step of the job.

Phase II

Now in the next phase of this article we will see how to list all the Jobs related to a table. For this we will use a cursor and some system tables.

DECLARE  @TEM TABLE (NAME VARCHAR(256),
JOB_STEP INT,
COMMAND VARCHAR(6400))
DECLARE @NAME VARCHAR(256)
DECLARE JOBS_CURSOR CURSOR FOR
SELECT NAME FROM SYSOBJECTS WHERE ID IN (
SELECT DISTINCT DEP.ID FROM SYSDEPENDS DEP
INNER JOIN SYSOBJECTS OBJ
ON OBJ.ID=DEP.DEPID
WHERE NAME = 'tv_shoppers')
AND TYPE = 'P'
ORDER BY NAME
OPEN JOBS_CURSOR
FETCH NEXT FROM JOBS_CURSOR INTO @NAME  
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO @TEM
SELECT J.NAME, S.STEP_ID, S.COMMAND
FROM MSDB..SYSJOBS J
INNER JOIN MSDB..SYSJOBSTEPS S
ON J.JOB_ID = S.JOB_ID
WHERE S.COMMAND LIKE  '%'+@NAME+'%'
FETCH NEXT FROM JOBS_CURSOR INTO @NAME
END
CLOSE JOBS_CURSOR
DEALLOCATE JOBS_CURSOR
SELECT * FROM @TEM

We first declare all the variables we will need. The @tem table variable will store the result data in a table that will have the same output as the above query. The cursor (jobs_cursor in this case ) will be used to extract the jobs related to the procedures dependent on the table. The @NAME variable is used to fetch the names of the procedures dependent on the table.

The SYSOBJECTS and SYSDEPENDS table are contained in every database. The SYSOJECTS table contains entry of every object and SYSDEPENDS table has entry of all dependent objects on an objects. The cursors query exploits this relationship between these tables and extracts all the dependent procedures on a table.

In the fetch next part of the cursor we use similar query that we have used in the earlier phase of this article. In each fetch next we store the result in @tem table variable, which is used to generate the output at the end.

The output would be similar to the output shown above. Since the same job may have the same procedure called in different steps, it is advisable to capture the jobstep.

I have also attached the SQL queries (in the Resources section below) so that you can use them directly. The same queries can be used to find dependency on different objects with small tweaks.

Conclusion

These queries can be handy when you have a new project in hand and you have to analyze the dependency on different objects. You can use these queries to generate the report of the jobs dependent on certain procedures or tables. However the results of the code executed may not always be accurate. The sysdepends table does not always give accurate results. Also if you are searching jobs related to procedure named 's_upd_Customer' you may also get results for a procedure named 's_upd_CustomerPTOC'. Therefore you need to look into the results in the COMMAND column of the output and verify that the names of the procedures mentioned are correct and if not you have to skip those results. We do need to cross check the results but the query does reduce a lot of manual effort.