SQL with Manoj

 
Today while working on one database migration project I wanted to check a particular Stored Procedure is called by what all SQL Jobs.

So, I created following queries to pass the SP name in the where clause and get respective SQL Job name.

The 1st query just gives the SQL Job name, and the 2nd query gives the SQL Job name with Job Step name, SQL Command used in that step, and target Database.

use msdb
go

-- #1. List out all SQL Jobs:

select distinct j.name
from msdb.dbo.sysjobs j
join msdb.dbo.sysjobsteps js on js.job_id = j.job_id
where js.command like '%spStartSystem%'
order by j.name


-- #2. List out all SQL Jobs with SQL command, Step name and target Database:

select 
	j.name, 
	js.step_name, 
	js.command, 
	js.database_name
from msdb.dbo.sysjobs j
join msdb.dbo.sysjobsteps js on js.job_id = j.job_id
where js.command like '%spStartSystem%'
order by j.name