At my work, there are a lot SQL agent jobs that look like disabled or not scheduled. However, the reality is that they run every day and are fired up by the other SQL jobs on the same or different servers. On the other hand, they might also fire up other jobs when processing their job steps. When I first time troubleshooted job failures, it was hard to find the dependencies between those ‘disabled’ jobs without any workflow provided. The following T-SQL script helped me to identify job dependencies.
SELECT j.name AS CallingJobName,
s.step_name AS CallingStepName,
s.step_id AS CallingStepID,
s.command AS SQLCommand
FROM msdb..sysjobsteps AS s
INNER JOIN
msdb..sysjobs AS j
ON j.job_id = s.job_id
WHERE s.command LIKE '%sp_start_job%'
ORDER BY CallingJobName
This is just a simple syntax. However you can get more job information by the combination of msdb..sysjobsteps and msdb..sysjobs.
