Search Database Object Definitions for Text

If you need to find some text within a database object definition, this script can be helpful.

It should allow you to find text within the definitions of the following types of database objects:

  • Functions (scalar and table-valued)
  • Stored Procedures
  • Triggers
  • Views
 1DECLARE @SearchString VARCHAR(255)
 2SET @SearchString = 'TextToFind'
 3
 4SELECT DISTINCT
 5	o.name AS ObjectName,
 6	o.type_desc as TypeDescription
 7FROM
 8	sys.sql_modules m
 9	INNER JOIN sys.objects o ON m.object_id = o.object_id
10WHERE
11	m.[definition] LIKE '%' + @SearchString + '%'
12ORDER BY
13	2, 1

An alternative script that I’ve used for a long time is as follows:

 1DECLARE @SearchString VARCHAR(255)
 2SET @SearchString = 'TextToFind'
 3
 4SELECT
 5	OBJECT_NAME(id),
 6	*
 7FROM
 8	SYSCOMMENTS
 9WHERE
10	[text] LIKE '%' + @SearchString + '%'

Source: Stack Overflow