SELECT
[Sjob]. [job_id] As [JobID]
, [Sjob]. [Name] As [JobName]
, [SDBP]. [Name] As [Jobowner]
, [SCAT]. [Name] As [Jobcategory]
, [Sjob]. [Description] As [jobdescription]
, case [Sjob]. [Enabled]
When 1 Then ' Yes '
When 0 Then ' No '
END as [isenabled]
, [Sjob]. [Date_created] As [Jobcreatedon]
, [Sjob]. [Date_modified] As [Joblastmodifiedon]
, [Ssvr]. [Name] As [Originatingservername]
, [SJSTP]. [STEP_ID] As [Jobstartstepno]
, [SJSTP]. [Step_name] As [Jobstartstepname]
, case
When [Ssch]. [Schedule_uid] is NULL and then ' No '
ELSE ' Yes '
END as [isscheduled]
, [Ssch]. [Schedule_uid] As [Jobscheduleid]
, [Ssch]. [Name] As [Jobschedulename]
, case [Sjob]. [Delete_level]
When 0 Then ' never '
When 1 Then ' on Success '
When 2 Then ' on Failure '
When 3 then ' on completion '
END as [jobdeletioncriterion]
From
[msdb]. [dbo]. [Sysjobs] As [Sjob]
Left JOIN [msdb]. [SYS]. [Servers] As [Ssvr]
On [Sjob]. [originating_server_id] = [SSVR]. [SERVER_ID]
Left JOIN [msdb]. [dbo]. [Syscategories] As [SCAT]
On [Sjob]. [category_id] = [SCAT]. [category_id]
Left JOIN [msdb]. [dbo]. [Sysjobsteps] As [SJSTP]
On [Sjob]. [job_id] = [SJSTP]. [job_id]
and [Sjob]. [start_step_id] = [SJSTP]. [STEP_ID]
Left JOIN [msdb]. [SYS]. [Database_principals] As [SDBP]
On [Sjob]. [Owner_sid] = [SDBP]. [SID]
Left JOIN [msdb]. [dbo]. [Sysjobschedules] As [Sjobsch]
On [Sjob]. [job_id] = [Sjobsch]. [job_id]
Left JOIN [msdb]. [dbo]. [Sysschedules] As [Ssch]
On [Sjobsch]. [schedule_id] = [Ssch]. [SCHEDULE_ID]
WHERE [Sjob]. [Name] Like '%c9% '
ORDER by [JobName]
View SQL SERVER Job details