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
DECLARE @SearchString VARCHAR(255)
SET @SearchString = 'TextToFind'

SELECT DISTINCT
	o.name AS ObjectName,
	o.type_desc as TypeDescription
FROM
	sys.sql_modules m
	INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE
	m.[definition] LIKE '%' + @SearchString + '%'
ORDER BY
	2, 1

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

DECLARE @SearchString VARCHAR(255)
SET @SearchString = 'TextToFind'

SELECT
	OBJECT_NAME(id),
	*
FROM
	SYSCOMMENTS
WHERE
	[text] LIKE '%' + @SearchString + '%'

Source: Stack Overflow