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 + '%'