Find text in Stored Procedures & then find JOB associate with Stored Procedure

Introduction

Few days before I was asked to disable some ‘JOB’ in a production SQL Server. But the task was a little tricky!! I was given a subject line and asked to stop that particular daily auto report email. Server IP & Database name was only provided. Now the challenge was to find that stored procedure where that SUBJECT LINE was used & the JOB by which that particular stored procedure is executing.

Script to find TEXT in the Stored Procedure

First, we google & found two suitable SQL script to find TEXT in the stored procedure within a database.

Seript 01

USE [AdventureWorks2014]; -- Specify the database name
GO

-- Retrieve the schema name, object name, and object type
SELECT [Schema] = SCHEMA_NAME(o.schema_id), o.Name, o.type

-- From the sys.sql_modules table and inner join with sys.objects table
FROM sys.sql_modules m
INNER JOIN sys.objects o ON o.object_id = m.object_id

-- Filter the objects where the definition contains the specified text
WHERE m.definition LIKE '%Sanjay Humania%'
GO

Seript 02

USE [AdventureWorks2014]; -- Specify the database name

GO

DECLARE @SEARCHSTRING VARCHAR(255), @notcontain VARCHAR(255)
SELECT @SEARCHSTRING = 'Sanjay Humania', @notcontain = '' -- Specify the search string and the text to exclude

-- Select distinct object name and object type based on search criteria
SELECT DISTINCT sysobjects.name AS [Object Name],
    CASE
        WHEN sysobjects.xtype = 'P' THEN 'Stored Proc'
        WHEN sysobjects.xtype = 'TF' THEN 'Function'
        WHEN sysobjects.xtype = 'TR' THEN 'Trigger'
        WHEN sysobjects.xtype = 'V' THEN 'View'
    END AS [Object Type]
FROM sysobjects, syscomments
WHERE sysobjects.id = syscomments.id
    AND sysobjects.type IN ('P', 'TF', 'TR', 'V')
    AND sysobjects.category = 0
    AND CHARINDEX(@SEARCHSTRING, syscomments.text) > 0
    AND (CHARINDEX(@notcontain, syscomments.text) = 0
        OR CHARINDEX(@notcontain, syscomments.text) <> 0)

Script to find JOB associate with Stored Procedure

So we found the stored procedure name finally. Now we need to find the associated JOB, by which the stored procedure execution took place & disable that JOB.

Below script can be used to find ‘Job’ by providing stored procedure name

-- Retrieve the job name as "Job Name"
SELECT j.name AS [Job Name]
FROM msdb.dbo.sysjobs AS j
WHERE EXISTS 
(
  -- Check if there is at least one job step that matches the condition
  SELECT 1 FROM msdb.dbo.sysjobsteps AS s
  WHERE s.job_id = j.job_id
    AND s.command LIKE '%ess_Ex_Send_BirthdayWishesMail%' -- Specify the stored procedure name
);