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
