How to display complete SQL Job details containing a string in MS SQL server

Below code will help you to find the SQL job name and job step containing a particular string. This is useful when you have many jobs in the same server and you wanted to search for a job which uses a particular string.

How to display complete SQL Job details containing a string in MS SQL server

Today I found a post in DotnetSpider, where user wanted to search all jobs which uses a particular string in it. Reason he wanted to know this query is he is having so many jobs in his server and find it hard to open all the jobs and search for the string.

Below SQL query can be used to search all the jobs in the server for a particular string. This query will display all the jobs name and step name.

You just need to change the string you wanted to search in the below query.

SELECT SQLJobName.database_name as DatabaseName,
SQLJobs.Name as SQLJob_Name,
SQLJobName.step_name as SQLJob_StepName,
FROM msdb.dbo.sysjobs as SQLJobs
INNER JOIN msdb.dbo.sysjobsteps as SQLJobName
ON SQLJobs.job_id= SQLJobName.job_id
WHERE SQLJobs.[enabled] = 1 AND SQLJobName.command LIKE '%StringToSearch%'
ORDER BY SQLJobs.Name,SQLJobName.step_id

Article by Asheej T K
Thanks and Regards Asheej T K Dotnet Galaxy

Follow Asheej T K or read 33 articles authored by Asheej T K


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: