IF OBJECTPROPERTY( OBJECT_ID( 'fn_running_jobs' ) , 'IsInlineFunction' ) IS NOT NULL
        DROP FUNCTION fn_running_jobs
GO
CREATE FUNCTION fn_running_jobs (
)
RETURNS TABLE
AS
/********************************************************************************/
/* Created By   : Umachandar Jayachandran (UC)                                  */
/* Created On   : 01 January 2001                                               */
/* Description  : This function returns the running SQLAgent jobs. This one uses*/
/*                the undocumented fn_varbintohexstr system function.           */
/********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                         */
/********************************************************************************/
RETURN (
    SELECT j1.job_id , j1."Name" , J1."Description"
      FROM msdb..sysjobs AS j1
     WHERE EXISTS( SELECT * FROM master..sysprocesses AS p
                    WHERE p.program_name LIKE 'SQLAgent%JobStep%' +
                          master.dbo.fn_varbintohexstr( CAST( j1.job_id AS varbinary ) ) + '%'
          )
)
GO
IF OBJECTPROPERTY( OBJECT_ID( 'fn_running_jobs2' ) , 'IsInlineFunction' ) IS NOT NULL
        DROP FUNCTION fn_running_jobs2
GO
CREATE FUNCTION fn_running_jobs2 (
)
RETURNS TABLE
AS
/********************************************************************************/
/* Created By   : Umachandar Jayachandran (UC)                                  */
/* Created On   : 01 January 2001                                               */
/* Description  : This function returns the running SQLAgent jobs.              */
/********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                         */
/********************************************************************************/
RETURN (
    SELECT j1.job_id , j1."Name" , J1."Description"
      FROM msdb..sysjobs AS j1
     WHERE j1.job_id IN (
            SELECT CAST(
                           SUBSTRING( j2.job_id , 07 , 2 ) + SUBSTRING( j2.job_id , 05 , 2 ) +
                           SUBSTRING( j2.job_id , 03 , 2 ) + SUBSTRING( j2.job_id , 01 , 2 ) + '-' +
                           SUBSTRING( j2.job_id , 11 , 2 ) + SUBSTRING( j2.job_id , 09 , 2 ) + '-' +
                           SUBSTRING( j2.job_id , 15 , 2 ) + SUBSTRING( j2.job_id , 13 , 2 ) + '-' +
                           SUBSTRING( j2.job_id , 17 , 4 ) + '-' +
                           RIGHT( j2.job_id , 12 ) AS uniqueidentifier )
                 
            FROM ( 
                    SELECT SUBSTRING( p.program_name ,
                            CHARINDEX( '0x' , p.program_name ) + 2 , 
                            CHARINDEX( SPACE( 1) , p.program_name , 
                                       CHARINDEX( '0x' , p.program_name ) ) -
                                    CHARINDEX( '0x' , p.program_name ) - 2 )
                      FROM master..sysprocesses AS p
                     WHERE p.program_name LIKE 'SQLAgent%JobStep%' 
            ) AS j2( job_id )
    )
)
GO
SELECT * FROM fn_running_jobs()
SELECT * FROM fn_running_jobs2()
GO

This page was last updated on May 01, 2006 04:28 PM.