vwJob (dependencies included)

I've been asked to produce the code for vwJob, vwJobStep, vwJobSched, and vwJobHistory with no dependencies.

This is created in tempdb but you can optionally put it into your Admin (or similarly named) database.

Use this for an easy method to retrieve metadata about jobs.

/*    DATE        AUTHOR            REMARKS

      2/21/16     PPaiva            Initial creation.

      USAGE

            -- Jobs

            SELECT *

            FROM vwJob

            ORDER BY Name

            -- Jobs with Steps

            -- Column Command is limited to 300 chars, but you may wish to remove the limit

            SELECT Instance, JobEn, Name, Description, Category, StepID, StepName, Subsystem, Filename, Left(Command, 300) Command, PathFile, JobCreated, JobModified, Job_ID, step_uid

            FROM vwJobStep

            ORDER BY Name, StepID

            -- Jobs with Schedules

            SELECT *

            FROM vwJobSched

            ORDER BY OrderByMe

            -- Job History

            SELECT *

            FROM vwJobHistory

            ORDER BY instance_id desc    

      DEPENDENCIES (contained within)

            udfFormatJobNextRunDate()

            udfGetFilenameOnly()

            vwJob

            

*/

Use tempdb

-- Use Admin      -- Put this in your own database if you have one

go

IF object_id('dbo.udfFormatJobNextRunDate') Is Not Null

      DROP FUNCTION dbo.udfFormatJobNextRunDate

go

CREATE FUNCTION dbo.udfFormatJobNextRunDate(

      @NextRunDateTEXT varchar(8) = '',

      @NextRunTimeTEXT varchar(6)

      )

RETURNS Datetime

AS

/*  DATE        AUTHOR          REMARKS

    2/21/16     PPaiva          Initial creation

      DESCRIPTION

            Formats the text columns Next_Run_Date and Next_Run_Time

                        in sysJobSchedules to a datetime format.

            TIME formatting

                        Input:  4000

                        Output:  00:40:00

      USAGE

            SELECT  *,

                  dbo.udfFormatJobNextRunDate(next_run_date, next_run_time) ProperlyFormattedDatetime

            FROM msdb.dbo.sysJobSchedules

            ORDER BY len(next_run_time)

          

            SELECT dbo.udfFormatJobNextRunDate('', '3')

            SELECT dbo.udfFormatJobNextRunDate('20160401', '3')

            SELECT dbo.udfFormatJobNextRunDate('20160401', '2300')

            SELECT dbo.udfFormatJobNextRunDate('20160401', '12300')

            SELECT dbo.udfFormatJobNextRunDate('20160401', '112300')

*/

BEGIN

      DECLARE @Out datetime,

                  @NextRunTimeWithColons varchar(8),

                  @LenTime int

    

                

      -- Pad left with zeros

      SET @LenTime = Len(@NextRunTimeTEXT)

      SET @NextRunTimeTEXT = REPLICATE('0', 6 - @LenTime) + @NextRunTimeTEXT

      -- Add colons.  Change 005000 to 00:50:00

      SET @NextRunTimeWithColons = Left(@NextRunTimeTEXT, 2)

                        + ':'

                        +     Substring(@NextRunTimeTEXT, 3, 2)

                        + ':'

                        +     Right(@NextRunTimeTEXT, 2)

      IF @NextRunDateTEXT = ''

            SET @Out = @NextRunTimeWithColons

                             

      ELSE

            -- Concatenate date and time

            SET @Out = CASE WHEN IsNull(@NextRunDateTEXT, 0) = 0 THEN Null

                                          ELSE Convert(datetime, @NextRunDateTEXT + ' ' + @NextRunTimeWithColons

                                                                  )

                                                END

RETURN @Out

END

go

IF object_id('dbo.udfGetFilenameOnly') Is Not Null

      DROP FUNCTION dbo.udfGetFilenameOnly

go

CREATE FUNCTION dbo.udfGetFilenameOnly(

      @In varchar(500)

      )

RETURNS varchar(300)

AS

/*    DATE        AUTHOR            REMARKS

      8/19/15     PPaiva            Initial creation.

      USAGE

            SELECT dbo.udfGetFilenameOnly('D:\Program Files\Microsoft SQL Server\Logs\Admin_Log.LDF')

            SELECT dbo.udfGetFilenameOnly('Admin_Log.ldf')

*/

BEGIN

      DECLARE @Out varchar(300),

                  @Pos smallint

           

      -- Get position of last backslash

      SET @Pos = CharIndex('\', Reverse(@In))

      IF @Pos > 1

            BEGIN

                  SET @Pos = Len(@In) - @Pos               

                  SET @Out = Substring(@In, @Pos + 2, 500)

            END

     

      ELSE

            SET @Out = @In

      RETURN @Out

END

go

IF object_id('dbo.vwJob') Is Not Null

      DROP VIEW dbo.vwJob

go

CREATE VIEW dbo.vwJob

AS

/*  DATE        AUTHOR      REMARKS

    2/21/16     PPaiva      Initial creation.

     

         

      DESCRIPTION

            Returns list of jobs.

            Column SchedEn looks at all schedules for a job.  If any one is

                  enabled, then SchedEn = 1.

      USAGE

            SELECT *

            FROM vwJob

            ORDER BY Instance, JobEn desc, Name

*/

WITH cteJobNextRunSchedEn

AS (

      SELECT  job_id,

                  Min(Convert(varchar(16), dbo.udfFormatJobNextRunDate(next_run_date, next_run_time), 120)) NextRunDatetime,

                  CASE WHEN Sum(Convert(int, sched.enabled)) > 0

                              THEN 1

                         ELSE 0

                         END SchedEn,

                  Count(*) NumSched

      FROM msdb.dbo.sysjobschedules jobsched

      JOIN msdb.dbo.sysschedules sched

            ON sched.schedule_id = jobsched.schedule_id

      GROUP BY job_id

      )

SELECT  Convert(varchar(50), ServerProperty('ServerName')) Instance,

        J.Enabled JobEn,

            SchedEn.SchedEn,

            IsNull(SchedEn.NumSched, 0) NumSched,

        J.Name,

        J.Description,  

            SchedEn.NextRunDateTime,

        CASE WHEN C.Name = '[Uncategorized (Local)]' THEN ''

                        ELSE c.Name END  Category,

           

--          Originating_Server OrigServer,

            msdb.dbo.SQLAGENT_SUSER_SNAME(owner_sid) Owner,

            J.Category_ID,

        J.Date_Created JobCreated,

        J.Date_Modified JobModified,

        J.Job_ID

FROM msdb.dbo.sysJobs J

LEFT JOIN msdb.dbo.syscategories C

      ON C.Category_ID = J.Category_ID

LEFT JOIN cteJobNextRunSchedEn SchedEn

      ON SchedEn.job_id = j.job_id

go

IF object_id('dbo.vwJobStep') Is Not Null

      DROP VIEW dbo.vwJobStep

go

CREATE VIEW dbo.vwJobStep

AS

/*    DATE        AUTHOR            REMARKS

      2/21/16     PPaiva            Initial creation.

      SELECT *

      FROM vwJob

      SELECT *

      FROM vwJobStep

      WHERE Name like '%%'

      ORDER BY Name, StepID

      SELECT *

      FROM vwJobStep

      WHERE Name = '%%'

      ORDER BY Name, StepID

      SELECT *

      FROM msdb.dbo.sysjobsteps s

*/

WITH cte AS (    

      SELECT  j.Instance,

                  j.JobEn,

                  j.Name,

                  j.Category,

                  j.Description,

                  s.step_id StepID,

                  s.step_name StepName,

                  s.Subsystem,

                  s.Command,

                  CharIndex('dtexec.exe', Command) PosDTExec,

                  CharIndex('"', Command) PosDoubleQuote1,

                  CharIndex('"', Command, CharIndex('"', Command) + 1) PosDoubleQuote2,

                  CharIndex('php.exe', Command) PosPHP,

                  CharIndex(' ', Command) PosSpace1,

                  j.JobCreated,

                  j.JobModified,

                  j.Job_ID,

                  s.step_uid

      FROM vwJob j

      LEFT JOIN msdb.dbo.sysjobsteps s

            ON s.job_id = j.job_id

      ),

cte2 AS (

      SELECT  Instance, JobEn, Name, Category, Description, StepID, StepName,

                  Subsystem,

                  PosPHP,

                  CASE WHEN PosDoubleQuote1 > 0 AND Subsystem In( 'CmdExec', 'SSIS')

                        THEN Substring(Command, PosDoubleQuote1 + 1, PosDoubleQuote2 - PosDoubleQuote1 - 1)

                        ELSE '' END PathFileSSIS,

                  CASE WHEN PosPHP > 0

                        THEN Substring(Command, PosSpace1, 1000)

                        ELSE '' END PathFilePHP,

                  Command,

                  JobCreated, JobModified, Job_ID, step_uid

      FROM cte

      ),

cte3 AS (

      SELECT  Instance, JobEn, Name, Category, Description, StepID, StepName,

                  CASE WHEN Len(PathFileSSIS) > 0 AND PosPHP = 0 AND SubSystem = 'CmdExec'

                              THEN Subsystem + ' SSIS'

                         WHEN Len(PathFileSSIS) > 0 AND PosPHP = 0 AND SubSystem = 'SSIS'

                              THEN Subsystem

                         WHEN Len(PathFilePHP) > 0 AND PosPHP > 0

                              THEN Subsystem + ' PHP'

                        ELSE Subsystem END Subsystem,

                  Command,

                  PathFileSSIS,

                  PathFilePHP,

                  JobCreated, JobModified, Job_ID, step_uid

      FROM cte2

      ),

cte4 AS (

      SELECT  Instance, JobEn, Name, Category, Description, StepID, StepName,

                  Subsystem,

                  Command,

                  CASE WHEN Len(PathFileSSIS) > 0

                              THEN PathFileSSIS

                         WHEN Len(PathFilePHP) > 0

                              THEN PathFilePHP

                         WHEN Subsystem = 'CmdExec'

                              THEN Command

                        ELSE '' END PathFile,

                  JobCreated, JobModified, Job_ID, step_uid

      FROM cte3

      )

SELECT  Instance, JobEn, Name, Description, Category, StepID, StepName,

            Subsystem,

            dbo.udfGetFilenameOnly(PathFile) Filename,

            Command,

            PathFile,

            JobCreated, JobModified, Job_ID,

            step_uid

FROM cte4

go

IF object_id('dbo.vwJobSched') Is Not Null

      DROP VIEW dbo.vwJobSched

go

CREATE VIEW dbo.vwJobSched

AS

/*  DATE        AUTHOR          REMARKS

    2/21/16     PPaiva          Initial creation.

     

   

      DESCRIPTION

            NOTE that since this JOINs to sysJobSchedules, this view

            does not necessarliy show a distinct list of Job Name.

      DEPENDENCIES

            dbo.udfFormatJobNextRunDate()

      USAGE

            -- The next job to be executed is shown at the top

            SELECT *

            FROM vwJobSched

            WHERE Name Like '%%'

            ORDER BY Instance, JobEn desc, SchedEn desc, NextRunDateTime

            SELECT *

            FROM vwJobSched

            ORDER BY Instance, JobEn desc, SchedEn desc, OrderByMe, NextRunDateTime

      DEBUG

            SELECT *

            FROM msdb.dbo.sysJobSchedules

*/

WITH cte

AS

(

SELECT  Convert(varchar(50), ServerProperty('ServerName')) Instance,

            J.Enabled JobEn,

            ss.Enabled SchedEn,

            Convert(varchar(16), dbo.udfFormatJobNextRunDate(js.next_run_date, js.next_run_time), 120) NextRunDatetime,

                  J.Name,

            J.Description,  

            CASE WHEN C.Name = '[Uncategorized (Local)]' THEN ''

                              ELSE c.Name END  Category,

            ss.Name SchedName,

--          Originating_Server OrigServer,

            JS.Next_Run_Date,

            JS.Next_Run_Time,

            J.Date_Created JobCreated,

            J.Date_Modified JobModified,

                  js.schedule_id,

            J.Job_ID

FROM msdb.dbo.sysJobs J

LEFT JOIN msdb.dbo.sysJobSchedules JS

      ON JS.Job_ID = J.Job_ID

LEFT JOIN msdb.dbo.syscategories C

      ON C.Category_ID = J.Category_ID

LEFT JOIN msdb.dbo.sysschedules ss

      ON ss.schedule_ID = js.schedule_ID

)

SELECT CASE WHEN NextRunDateTime Is Null

                        THEN 'Null'

                   WHEN NextRunDateTime < GetDate()

                        THEN 'Past'

                  ELSE 'Future'

            END NextRun,

            *,

            CASE WHEN NextRunDateTime Is Null

                        THEN '2200-01-01'

                   WHEN NextRunDateTime < GetDate()

                        THEN '2100-01-01'

                  ELSE NextRunDateTime

            END OrderByMe

FROM cte

go

IF object_id('dbo.vwJobHistory') Is Not Null

      DROP VIEW dbo.vwJobHistory

go

CREATE VIEW dbo.vwJobHistory

AS

/*  DATE        AUTHOR          REMARKS

    2/21/16     PPaiva          Initial creation.

      DEPENDENCY

            dbo.udfFormatJobNextRunDate()

           

      -- Oldest job         

      SELECT Min(RunDatetime)

      FROM vwJobHistory

      SELECT *

      FROM vwJobHistory

      ORDER BY RunDateTime desc

      -- Most recent failed jobs

      SELECT *

      FROM vwJobHistory

--    WHERE Status <> 'Succeeded'

      ORDER BY RunDateTime desc

*/

SELECT  Convert(varchar(50), ServerProperty('ServerName')) Instance,

            CASE WHEN jh.Run_Status = 0 THEN 'Failed'

                  WHEN Run_Status = 1 THEN 'Succeeded'

                  WHEN Run_Status = 2 THEN 'Retry'

                  WHEN Run_Status = 3 THEN 'Canceled'

                  WHEN Run_Status = 4 THEN 'In Progress'  

                  ELSE 'Undefined Status in View'

            END Status,

            j.Name,

                  CASE WHEN c.name = '[Uncategorized (Local)]' THEN ''

                              ELSE c.name END Category,

            --Run_Date,

            --Run_Time,

                  Convert(varchar(16), dbo.udfFormatJobNextRunDate(run_date, run_time), 120) RunDatetime,      -- Use this for HH:MM,

                  -- dbo.udfFormatJobNextRunDate(run_date, run_time) RunDatetime,            -- Use this for HH:MM:SS.NNN

                  --dbo.udfFormatJobNextRunDate('', CASE WHEN run_duration > 0 then run_duration ELSE '0' END) RunDuration,  Use this for HH:MM:SS.NNN

                  -- This is protected against negative numbers which can happen when dayligh savings ends

                  Convert(varchar(19), dbo.udfFormatJobNextRunDate('', CASE WHEN run_duration > 0 then run_duration ELSE '0' END), 120) RunDuration,   -- Use this for HH:MM:SS,

            step_id, step_name, server,

            message,

            jh.job_id,

            instance_id

            --sql_message_id, sql_severity, run_status, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted

FROM msdb.dbo.sysJobHistory jh

JOIN msdb.dbo.sysJobs j

      ON j.job_id = jh.job_id

LEFT JOIN msdb.dbo.syscategories C

      ON C.Category_ID = j.Category_ID

go

SELECT *

FROM vwJob

ORDER BY Name