info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



Find a stored procedure call within a SQL Server Job
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2015-08-02







Solution:


--Returns 1 record from MSDB SYSJOBS table while many records with a stored procedure name can exist in SYSJOBSSTEPS table
--Use this if care only about the job name and not how many times the stored procedure is executed in different job steps

SELECT t1.name
FROM msdb.dbo.sysjobs AS t1
WHERE EXISTS
(
SELECT 1
FROM msdb.dbo.sysjobsteps AS t2
WHERE t2.job_id = t1.job_id
AND t2.command LIKE '%%'
)


--OR


--Returns a record for each instance of a stored procedure name existing in SYSJOBSSTEPS table
--Use this if care about each separate job step the stored procedure is called in

SELECT t1.name, t2.step_id, t2.command
FROM MSDB.dbo.sysjobs as t1
INNER JOIN MSDB.dbo.sysjobsteps t2 on t1.job_id=t2.job_id
WHERE t2.command like '%%'