Resource icon

SQL Search Microsoft SQL Server Jobs

Kevin

Code Monkey
Staff member
Have you ever needed to find a job in Microsoft SQL server and it took forever because there were so many defined jobs that you had to look at one by one? Next time run a query. ;)

In the "System Databases" database you will find a database named "msdb" that contains several tables. If you drill down to Databases / System Databases / msdb / Tables / System Tables you will spot several tables that start with "sysjobs" as pat of their name. These tables contain the jobs that are defined.

The table "sysjobs" contains the general job definition. The column "description" contains a description of that job. If you wanted to search for jobs that have the word "update" in their description then the following query would work to return the jobs you are looking for.
SQL:
select * from msdb.dbo.sysjobs
where description like '%update%'
The table "sysjobsteps" contains the steps that are defined for each job. The column "command" is the actual command step. If you want to search for jobs that have the text "update" in their command step then the following query would work to return the job command steps you are looking for.
SQL:
select * from msdb.dbo.sysjobsteps
where command like '%udpate%'
To make your life a little bit easier you can then job sysjobsteps to sysjobs on job_id to retrieve the actual name of the job that contains a command step with the text "updated" in it.
SQL:
select sysjobs.name, *
from msdb.dbo.sysjobsteps
inner join msdb.dbo.sysjobs with (nolock) on sysjobsteps.job_id = sysjobs.job_id
where command like '%update%'
No more wasting time by looking at each job one by one! :cool:
 
Back
Top