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
Follow