Search a string in SQL Stored Procedure

DECLARE @Search varchar(255)

SET @Search='COMPLETE'

 

SELECT DISTINCT

    o.name AS Object_Name,o.type_desc,m.definition

    FROM sys.sql_modules        m

        INNER JOIN sys.objects  o ON m.object_id=o.object_id

    WHERE m.definition Like '%'+@Search+'%'

    ORDER BY 2,1