Skip to content
Tags

, ,

Searching All Stored Procedures and SQL Agent Job Steps

by Dan Thompson on October 17th, 2011
Stored Procedures

Every now and again I get asked if any specific business logic is going through any of the steps of our scheduled jobs or stored procs. This script will search through the commands within any job steps and through any stored procs (across all databases).

DECLARE @term varchar(max)
SET @term = 'DELETE FROM Products'

SET @term = '%' + @term + '%'

--All DBs:
DECLARE @sql varchar(max)
SET @sql = 'USE [?]
            SELECT ''?'' [Database], schema_name([schema_id]) [schema], Name
				FROM [?].Sys.procedures
				WHERE OBJECT_DEFINITION(object_id) LIKE ''' + @term + ''''

EXEC sp_msforeachdb @sql

SELECT j.name, js.*
FROM msdb.dbo.sysjobsteps js
INNER JOIN msdb.dbo.sysjobs j ON js.job_id = j.job_id
WHERE js.command LIKE @term

/*
Search text in a stored proc in sql 2000
*/
--SELECT OBJECT_NAME(id),*
--FROM syscomments
--WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1 and text LIKE @term

Bare in mind that sp_msforeachdb is an undocumented stored procedure by Mircosoft.

Hopefully this code will be useful to others.

From → T-SQL

  • Pingback: Homepage

  • http://www.techtree.co.uk Dan Thompson

    Chris Taylor: (Blog Here!) mentioned another interesting way to perform this without using sp_msforeach. I like this approach as you can filter out the system tables easily:

    declare @sql varchar(max), @term varchar(max)
select @sql = '', @term = 'select'
 
SET @term = '%' + @term + '%'

select @sql = @sql + 
'SELECT '''+name+''' AS [Database], schema_name([schema_id]) [schema], Name
FROM ['+name+'].Sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE ''' + @term + '''
' +CHAR(10)
from sys.databases
where database_id > 4

select @sql
exec(@sql)