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.
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
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 );