Advertisements
Home > SQL Tips > SQL Tips – Search and list out SQL Jobs containing specific word, text or SQL Query

SQL Tips – Search and list out SQL Jobs containing specific word, text or SQL Query


 
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


Advertisements
Categories: SQL Tips Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: