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 + ''%''); ';