Sharpen your SQL Skills

DISCLAIMER: The product links on this page are affiliate links which means I get a commission if you purchase anything through them,
but it will not cost you anything extra. For more information see my Affiliate Disclaimer page.

For more great resources, browse the Pineapple Place Store.

Search SQL Server Agent Job Steps for Specific Keyword Text or Stored Procedure

Tag: search, sysjobs, sysjobsteps, T-SQL, Inventory

If you have many SQL Server Agent Jobs running on a system, you may not recall which commands or stored procedures are being executed. Here is an easy way to query across all your jobs for a specific text within the job step.

SQL Server 2005/2008 Job Step Search

USE [msdb]
GO
SELECT	j.job_id,
	s.srvname,
	j.name,
	js.step_id,
	js.command,
	j.enabled 
FROM	dbo.sysjobs j
JOIN	dbo.sysjobsteps js
	ON	js.job_id = j.job_id 
JOIN	master.dbo.sysservers s
	ON	s.srvid = j.originating_server_id
WHERE	js.command LIKE N'%KEYWORD_SEARCH%'
GO

SQL Server 2000 Job Step Search

USE [msdb]
GO
SELECT	j.job_id,
	j.originating_server,
	j.name,
	js.step_id,
	js.command,
	j.enabled 
FROM	dbo.sysjobs j
JOIN	dbo.sysjobsteps js
	ON	js.job_id = j.job_id 
WHERE	js.command LIKE N'%KEYWORD_SEARCH%'
GO

Share this Tidbit
Copyright © 2008-2013 SQL Tidbits™