TSQL: SQLServerAgent job detail summary

Get job details and check if input files to OSQL steps exist

/****CommentStart
Author: Kevin Foreman
Date: 17-11-2006
Description:
Summary SQL Server Agent Job information.

Input:
@job_name_string >> part of the job name (used in 'like' comparison)
Output:
Job name, description, step id, subsystem, success/fail actions,
step name, command, output file, OSQL input file specified and
if that file exists

HEALTH WARNING:
Check for OSQL input files is shakey.
xp_fileexists not tested with UNC files

Looks for cmdexec steps with strings
osql >> call to osql
-i >>input file
.sql / .prc >> assumes input files are .sql or .prc
Input file comes between first occurance of -i and .sql or .prc

Maintained:
17-11-2006 KF Added .prc as well as .sql for file ext to look for
05-12-2006 KF deal with doubl quotes in input file names

CommentEnd****/

--set up variables
DECLARE
@job_name_string varchar(200) ,
@filename varchar(400) ,
@i int

--populate here with job name part
SELECT
@job_name_string = 'purge'
SELECT --just in case
@job_name_string = '%' + @job_name_string + '%'

--get job into a temp table var
DECLARE @Table_Info TABLE
(
Job_Name varchar(256),
Description varchar (1024),
Step_ID int,
Subsystem varchar(80),
Success_Action tinyint,
Fail_Action tinyint,
Step_Name varchar(256),
Command varchar(4000),
Output_File varchar(400),
OSQL_Input_File varchar(400),
OSQL_Input_File_Exists bit
)

INSERT
@Table_Info
SELECT
sj.name,
sj.Description,
sjs.step_id,
sjs.subsystem,
sjs.on_success_action,
sjs.on_fail_action,
sjs.step_name,
sjs.command,
sjs.output_file_name,
NULL,
NULL
FROM
msdb.dbo.sysjobs AS sj
INNER JOIN
msdb.dbo.sysjobsteps AS sjs
ON
sj.job_id = sjs.job_id
WHERE
UPPER(sj.name) like UPPER(@job_name_string)

--get file name/paths ending .sql or .prc and remove leading/training spaces
UPDATE @Table_Info
SET OSQL_Input_File = LTRIM(RTRIM(SUBSTRING( Command, CHARINDEX('-I',UPPER(Command))+2 , CHARINDEX('.SQL',UPPER(Command))-CHARINDEX('-I',UPPER(Command))+2 )))
WHERE Subsystem = 'CmdExec'
AND UPPER(Command) LIKE '%OSQL%'
AND UPPER (Command) LIKE '%.SQL%'

UPDATE @Table_Info
SET OSQL_Input_File = LTRIM(RTRIM(SUBSTRING( Command, CHARINDEX('-I',UPPER(Command))+2 , CHARINDEX('.PRC',UPPER(Command))-CHARINDEX('-I',UPPER(Command))+2 )))
WHERE Subsystem = 'CmdExec'
AND UPPER(Command) LIKE '%OSQL%'
AND UPPER (Command) LIKE '%.PRC%'

--deal with quotes in input file
UPDATE @Table_Info
SET OSQL_Input_File = REPLACE( OSQL_Input_File , '"' , '')


--check for existence of o/s files
UPDATE @Table_Info
SET OSQL_Input_File_Exists = 0
WHERE OSQL_Input_File IS NULL

WHILE EXISTS (SELECT 1 FROM @Table_Info WHERE OSQL_Input_File_Exists IS NULL)
BEGIN
SELECT @filename = (SELECT TOP 1 OSQL_Input_File FROM @Table_Info WHERE OSQL_Input_File_Exists IS NULL)
EXEC master..xp_fileexist @filename, @i output
UPDATE @Table_Info SET OSQL_Input_File_Exists = @i WHERE @filename = OSQL_Input_File
END

SELECT
Job_Name AS 'Job_Name',
Description AS 'Description',
Step_ID AS 'Step_ID',
Subsystem AS 'Subsystem',
CASE Success_Action
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Fail'
WHEN 3 THEN 'Next'
END AS 'Success_Action',
CASE Fail_Action
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Fail'
WHEN 3 THEN 'Next'
END AS 'Fail_Action',
Step_Name AS 'Step_Name',
Command AS 'Command',
Output_File AS 'Output_File',
OSQL_Input_File AS 'OSQL_Input_File',
OSQL_Input_File_Exists AS 'OSQL_Input_File_Exists'
FROM
@Table_Info
ORDER BY
Job_Name ,
Step_ID