SQL: Search All Stored Procedures and Functions for Certain Text

6/24/2015 2:59:01 PM
DECLARE		@searchTerm NVARCHAR(MAX)
SET			@searchTerm = 'usp'

SELECT		--DISTINCT 
			sys.objects.name AS Object_Name,
			sys.objects.type_desc,
			sys.sql_modules.definition
			--,*

FROM		sys.sql_modules
			INNER JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id

WHERE		sys.sql_modules.definition LIKE '%' + @searchTerm + '%'