Finding the procedure, text, triggers or text within any SQL Object from SQL SERVER

I am using syscomments table to find any query or text from within the procedures etc. When I try to find out a text value from within the trigger using syscomments system object. It is not returning the required results. After little research I find out another query that will search all objects in your database. I tried it and it is working fine. This query will work from SQL 2005 to all SQL Server versions.

Please try and share if it is helpful.


DECLARE @Search varchar(255)
SET @Search='SET @TypeID = 66'

SELECT DISTINCT
    o.name AS Object_Name,o.type_desc
    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