How to find a system job by the contents of a stored procedure in SQL Server 2016

Question

Currently, I find the system job by the date and time of the last run, for example.

The following query below returns the 100 tasks that were last completed on August 1, 2016, both at 8:37 a.m. and 8:44 a.m., and lists them in order from the newest to the oldest.

SELECT TOP 100
        *
FROM    ( SELECT    j.[name] ,
                    jh.run_date ,
                    jh.run_time
          FROM      msdb.dbo.sysjobs j
                    INNER JOIN msdb.dbo.sysjobhistory jh ON jh.job_id = j.job_id
                                                            AND jh.step_id = 0
          GROUP BY  j.[name] ,
                    jh.run_date ,
                    jh.run_time
        ) AS Results
WHERE   
    run_date = '20160801'
    AND run_time IN(
    '083700'
    , '084400'
    )
    ORDER BY 
    [run_time] DESC

The job I am trying to find starts the stored procedure, and I try to find a way to search for each stored procedure for the string "datetime_entered =" and also "user_entered =".

Does anyone know a way to do this?

Note:

It can be assumed that all my software has been updated to the latest versions.

I also use SQL query for SQL Server.

Answer 1

: :

SELECT  
    p.name 
    , c.[definition]
    , [Results].[job_id]
    , [Results].[name]
    , [Results].[srvname]
    , [Results].[step_id]
    , [Results].[command]
FROM    
    ( 
        SELECT 
            j.[job_id]   
            , j.[name] 
            , s.[srvname]
            , jh.run_date 
            , jh.run_time 
            , js.[step_id]
            , js.command 
            , CASE 
                WHEN js.command LIKE 'exec %' THEN 
                    CASE 
                        WHEN CHARINDEX(' ', SUBSTRING(js.command, 5, LEN(js.command) - 4)) > 0 THEN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(SUBSTRING(SUBSTRING(js.command, 5, LEN(js.command) - 4), CHARINDEX(' ', SUBSTRING(js.command, 5, LEN(js.command) - 4)), LEN(js.command) - CHARINDEX(' ', SUBSTRING(js.command, 5, LEN(js.command) - 4))), ']', ''), '[', ''), 'dbo.', '')))
                        ELSE SUBSTRING(js.command, 5, LEN(js.command) - 4)
                    END
                ELSE ''
            END AS StoredProcedure
        FROM      
            msdb.dbo.sysjobs AS j
            INNER JOIN msdb.dbo.sysjobhistory AS jh ON jh.job_id = j.job_id AND jh.step_id = 0
            INNER JOIN msdb.dbo.sysjobsteps AS js ON js.job_id = j.job_id
            INNER JOIN master.dbo.sysservers s ON  s.srvid = j.originating_server_id
        WHERE j.[enabled] = 1 
    ) AS Results
    INNER JOIN sys.objects p ON p.name LIKE '%' + Results.[StoredProcedure] + '%'
    INNER JOIN sys.sql_modules c ON p.object_id = c.object_id
WHERE   
    run_date = '20160801'
    AND run_time IN ( '083700', '084400' )
    AND ( command LIKE N'%datetime_entered = %' OR c.[definition] LIKE N'%datetime_entered = %' )
    AND ( command LIKE N'%user_entered = %' OR c.[definition] LIKE N'%user_entered = %' )
GROUP BY 
    p.name 
    , c.[definition]
    , [Results].[job_id]
    , [Results].[name]
    , [Results].[srvname]
    , [Results].[step_id]
    , [Results].[command];

. .

Answer 2

SYSJobSteps... , ..

USE [msdb]
GO
SELECT  j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled 
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON  js.job_id = j.job_id 
JOIN    master.dbo.sysservers s
    ON  s.srvid = j.originating_server_id
WHERE   js.command LIKE N'%KEYWORD_SEARCH%'