Identify SQL Agent Jobs Use of Certain Function or Stored Procedure

In an environment that utilize large number of SQL agent jobs, sometimes we want to identify which agent job is using a certain set of functions or stored procedures. One way of achieving this is by using SQL Server agent tables to quickly identify them.

There are two useful SQL Server agent tables, dbo.sysjobsteps and dbo.sysjobs could be used to achieved this purpose. These tables are located in msdb database. dbo.sysjobs contains information of each scheduled job and sysjobsteps contains information of each step. The script below take advantage of the command field which includes the T-SQL and filter the particular function or stored procedure if it is used.

DECLARE @func_proc nvarchar(max) = 'usp_testsproc';

SELECT
 j.name AS jobname,
 js.step_name,
 js.subsystem,
 js.command
FROM msdb.dbo.sysjobsteps js
INNER JOIN msdb.dbo.sysjobs j ON
 j.job_id = js.job_id
WHERE js.command LIKE ('%' + @func_proc + '%');

If you want to map all stored procedure/functions with the job, one way of doing is obtaining the stored procedures/functions from INFORMATION_SCHEMA.ROUTINE schema table and filter them with the list of jobs. Since the schema is database bound, one quick way is looping through all database with undocumented sp_MSforeachdb procedure. Be careful when using this procedure as it sometimes may miss database. The script below include a print statement particular to verify the looping database.

Run this on test environment first as it may take quite sometimes if your environment have large number of stored procedures/functions and database.

EXEC sp_MSforeachdb N'

USE [?]
PRINT ''?'';

SELECT
 r.ROUTINE_CATALOG,
 r.ROUTINE_SCHEMA,
 r.ROUTINE_NAME,
 r.ROUTINE_TYPE,
 j.name AS jobname,
 js.step_name,
 js.subsystem,
 js.command
FROM INFORMATION_SCHEMA.ROUTINES r
CROSS JOIN msdb.dbo.sysjobsteps js
INNER JOIN msdb.dbo.sysjobs j ON
 j.job_id = js.job_id
WHERE js.command LIKE (''%'' + r.ROUTINE_NAME + ''%'');

';