Comment avoir des informations sur les jobs SQL Server ?

• msdb.dbo.sysjobs
• msdb.dbo.sysjobsteps
• msdb.dbo.sysjobschedules
• Job_id has a uniqueidentifier data type that is a unique id field value for a job on a SQL Server Agent; this field is especially useful for joining msdb tables with different information about the jobs for SQL Server Agent.
• Name is a string of Unicode characters designating a job. The field has a sysname data type, which is equivalent to a nvarchar(128) data type that does not allow NULL values. Consequently, every SQL Server Agent Job must have a name field value, and the value cannot exceed 128 Unicode characters.
• Enabled is a tinyint field denoting whether a job can be invoked by a schedule. A value of 1 allows the job to be started on a schedule. A value of 0 means the job cannot be invoked by a schedule, but a SQL Server Agent user can manually start the job. Even if a job is enabled, it is not necessary for the job to have a schedule, and the job can be started manually.
• Date_created and date_modified are two fields with datetime data types that indicate, respectively, when a job was first created and when it was last modified.

-- schedule_id, job_ids showing next run date/time with conversion for display
SELECT 
schedule_id
,job_id
,LEFT(CAST(next_run_date AS VARCHAR),4)+ '-'
+SUBSTRING(CAST(next_run_date AS VARCHAR),5,2)+'-'
+SUBSTRING(CAST(next_run_date AS VARCHAR),7,2) next_run_date
,
CASE 
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 6  
  THEN SUBSTRING(CAST(next_run_time AS VARCHAR),1,2) 
    +':' + SUBSTRING(CAST(next_run_time AS VARCHAR),3,2)
    +':' + SUBSTRING(CAST(next_run_time AS VARCHAR),5,2)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 5
  THEN '0' + SUBSTRING(CAST(next_run_time AS VARCHAR),1,1) 
    +':'+SUBSTRING(CAST(next_run_time AS VARCHAR),2,2)
    +':'+SUBSTRING(CAST(next_run_time AS VARCHAR),4,2)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 4
  THEN '00:' 
    + SUBSTRING(CAST(next_run_time AS VARCHAR),1,2)
    +':' + SUBSTRING(CAST(next_run_time AS VARCHAR),3,2)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 3
  THEN '00:' 
    +'0' + SUBSTRING(CAST(next_run_time AS VARCHAR),1,1)
    +':' + SUBSTRING(CAST(next_run_time AS VARCHAR),2,2)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 2  THEN '00:00:' + CAST(next_run_time AS VARCHAR)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 1  THEN '00:00:' + '0'+ CAST(next_run_time AS VARCHAR)
END next_run_time
FROM msdb.dbo.sysjobschedules
ORDER BY job_id

.

-- list of jobs with name, steps, last run date/time, next_run_date/time
SELECT 
name
,CONVERT(VARCHAR(16), date_created, 120) date_created
,sysjobsteps.step_id
,sysjobsteps.step_name
,LEFT(CAST(sysjobsteps.last_run_date AS VARCHAR),4)+ '-'
+SUBSTRING(CAST(sysjobsteps.last_run_date AS VARCHAR),5,2)+'-'
+SUBSTRING(CAST(sysjobsteps.last_run_date AS VARCHAR),7,2) last_run_date
,
CASE 
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 6  
  THEN SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,2) 
    +':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),3,2)
    +':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),5,2)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 5
  THEN '0' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,1) 
    +':'+SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),2,2)
    +':'+SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),4,2)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 4
  THEN '00:' 
    + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,2)
    +':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),3,2)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 3
  THEN '00:' 
    +'0' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,1)
    +':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),2,2)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 2  THEN '00:00:' + CAST(sysjobsteps.last_run_time AS VARCHAR)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 1  THEN '00:00:' + '0'+ CAST(sysjobsteps.last_run_time AS VARCHAR)
END last_run_time
,LEFT(CAST(sysjobschedules.next_run_date AS VARCHAR),4)+ '-'
+SUBSTRING(CAST(sysjobschedules.next_run_date AS VARCHAR),5,2)+'-'
+SUBSTRING(CAST(sysjobschedules.next_run_date AS VARCHAR),7,2) next_run_date
,
CASE 
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 6  
  THEN SUBSTRING(CAST(next_run_time AS VARCHAR),1,2) 
    +':' + SUBSTRING(CAST(next_run_time AS VARCHAR),3,2)
    +':' + SUBSTRING(CAST(next_run_time AS VARCHAR),5,2)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 5
  THEN '0' + SUBSTRING(CAST(next_run_time AS VARCHAR),1,1) 
    +':'+SUBSTRING(CAST(next_run_time AS VARCHAR),2,2)
    +':'+SUBSTRING(CAST(next_run_time AS VARCHAR),4,2)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 4
  THEN '00:' 
    + SUBSTRING(CAST(next_run_time AS VARCHAR),1,2)
    +':' + SUBSTRING(CAST(next_run_time AS VARCHAR),3,2)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 3
  THEN '00:' 
    +'0' + SUBSTRING(CAST(next_run_time AS VARCHAR),1,1)
    +':' + SUBSTRING(CAST(next_run_time AS VARCHAR),2,2)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 2  THEN '00:00:' + CAST(next_run_time AS VARCHAR)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 1  THEN '00:00:' + '0'+ CAST(next_run_time AS VARCHAR)
END next_run_time
FROM msdb.dbo.sysjobs
LEFT JOIN msdb.dbo.sysjobschedules
ON sysjobs.job_id = sysjobschedules.job_id
INNER JOIN msdb.dbo.sysjobsteps
ON sysjobs.job_id = sysjobsteps.job_id
ORDER BY sysjobs.job_id, sysjobsteps.step_id

-- list of jobs; selected info about jobs
SELECT 
 job_id
,name
,enabled
,date_created
,date_modified
FROM msdb.dbo.sysjobs
ORDER BY date_created