use master
go
CREATE PROCEDURE [dbo].[sp_ViewJobListing]
(
@JobName VARCHAR(255)=NULL
)
--exec master..sp_ViewJobListing 'Nombre de mi Job'
AS
BEGIN
IF OBJECT_ID('tempdb..#Results')>0
DROP TABLE #Results
CREATE TABLE #Results
(
job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id SYSNAME COLLATE DATABASE_DEFAULT NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL
)
DECLARE @JobID VARCHAR(100)
SELECT TOP 1 @JobID = job_ID FROM msdb.dbo.sysjobs
INSERT INTO #Results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @JobID
SELECT
s.Name,
CASE WHEN s.enabled = 0 THEN 'No' ELSE 'Yes' END AS Enabled,
CASE WHEN next_run_date > 0 THEN 'Yes' ELSE 'No' END AS Scheduled,
sc.name AS Category,
current_step AS CurrentExecutionStep,
last_run_date,
next_run_date,
CASE WHEN xp.running = 0 THEN 'Not Running' ELSE 'Executing...' END AS Status,
ISNULL((
SELECT CASE WHEN run_status = 1 THEN 'Succeeded' WHEN run_status = 3 THEN 'Cancelled' WHEN run_status = 0 THEN 'Failed' WHEN run_status IS NULL THEN 'Unknown' END AS LastRunStatus
FROM
msdb..sysjobhistory sho
WHERE
sho.job_id = xp.job_id AND
sho.instance_id =
(
SELECT MAX(instance_id)
FROM msdb..sysjobhistory sj (NOLOCK)
WHERE sj.job_id = sho.job_id
)
) ,'Unknown') AS LastRunStatus
FROM #Results xp
INNER JOIN msdb..sysjobs s on xp.job_id = s.job_id
INNER JOIN msdb..syscategories sc on s.category_id = sc.category_id
WHERE
s.Name = ISNULL(@JobName, s.Name)
ORDER BY s.Name
IF @JobName IS NOT NULL
BEGIN
CREATE TABLE #JobHistory
(
StepID INT,
StepName SYSNAME,
Message NVARCHAR(4000),
RunStatus INT,
RunDate INT,
RunTime INT,
RunDuration INT,
operator_emailed NVARCHAR(100),
operator_netsent NVARCHAR(100),
operator_paged NVARCHAR(100)
)
INSERT INTO #JobHistory
SELECT
sjh.step_id,
sjh.step_name,
sjh.message,
sjh.run_status,
sjh.run_date,
sjh.run_time,
sjh.run_duration,
operator_emailed = so1.name,
operator_netsent = so2.name,
operator_paged = so3.name
FROM
msdb.dbo.sysjobhistory sjh
JOIN msdb.dbo.sysjobs sjj ON sjh.job_id = sjj.job_id
LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjh.operator_id_emailed = so1.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjh.operator_id_netsent = so2.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjh.operator_id_paged = so3.id),
msdb.dbo.sysjobs sj
WHERE
sjj.Name = @JobName and
(sj.job_id = sjh.job_id)
SELECT
StepID, StepName, Message, RunDate AS LastRunTime,
CASE RunStatus
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry (step only)'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In-progress message'
WHEN 5 THEN 'Unknown'
END AS RunStatus
FROM #JobHistory
ORDER BY LastRunTime DESC, StepID ASC
END
END
GO
EXECUTE sp_ms_marksystemobject 'sp_ViewJobListing'
