CREATE VIEW Maintenance.MaintainedJobs
  AS
  WITH Jobs AS
  (
    SELECT P.object_id AS SP_ID, EP.Name, EP.Value
      FROM sys.procedures P
        LEFT JOIN sys.extended_properties EP
          ON P.object_id = EP.major_id
            AND EP.class_desc = N'OBJECT_OR_COLUMN'
            AND EP.minor_id = 0
      WHERE P.schema_id = SCHEMA_ID(
        CAST(Configuration.Setting(N'$(Setting_SchedulingSchema)') AS sysname))
  )
  SELECT ISNULL(SJ.Name, DB_NAME() + N'.' + CAST(P.JobName AS sysname) + N'Job') AS JobName,
      CAST(CASE WHEN SJ.name IS NOT NULL THEN 1 ELSE 0 END AS bit) AS JobExists,
      CAST(CASE WHEN P.SP_ID IS NOT NULL THEN 1 ELSE 0 END AS bit) AS JobSPExists,
      CAST(CASE WHEN COALESCE(P.JobName, P.FreqType, P.FreqInterval,
          P.FreqSubdayType, P.FreqSubdayInterval, P.FreqRecurrenceFactor,
          P.ActiveStartTime, P.ActiveEndTime) IS NOT NULL
                THEN 1 ELSE 0 END AS bit) AS AllParamsOK,
      N'Run' + CAST(P.JobName AS sysname) + N'Job' AS JobStepName,
      CAST(P.JobName AS sysname) + N'Schedule' AS JobScheduleName,
      OBJECT_SCHEMA_NAME(P.SP_ID) AS ProcSchemaName,
      OBJECT_NAME(P.SP_ID) AS ProcName,
      CAST(P.FreqType AS int) AS FreqType,
      CAST(P.FreqInterval AS int) AS FreqInterval,
      CAST(P.FreqSubdayType AS int) AS FreqSubdayType,
      CAST(P.FreqSubdayInterval AS int) AS FreqSubdayInterval,
      CAST(P.FreqRecurrenceFactor AS int) AS FreqRecurrenceFactor,
      CAST(P.ActiveStartTime AS int) AS ActiveStartTime,
      CAST(P.ActiveEndTime AS int) AS ActiveEndTime
    FROM Jobs AS J
      PIVOT (
        MIN(J.Value)
        FOR J.Name IN (
        JobName, FreqType, FreqInterval,
        FreqSubdayType, FreqSubdayInterval,
        FreqRecurrenceFactor,
        ActiveEndTime, ActiveStartTime)) AS P
      FULL OUTER JOIN msdb.dbo.sysjobs SJ
        ON DB_NAME() + N'.' + CAST(P.JobName AS sysname) + N'Job' = SJ.name
    WHERE SJ.name IS NULL OR SJ.name LIKE DB_NAME() + N'.%Job';