Find a text inside stored procedure, trigger, view, etc.

Sometimes, you need to find a specific text in your stored procedures, this script may be useful to help you.

SELECT DISTINCT t2.name [Object Name],
    CASE t2.type
        WHEN'U' THEN 'User Table'
        WHEN'S' THEN 'System Table'
        WHEN 'V' THEN 'View'
        WHEN 'TR' THEN 'Trigger'
        WHEN 'P' THEN 'Stored Procedure'
        WHEN 'C' THEN 'Constraint - Check'
        WHEN 'D' THEN 'Default'
        WHEN 'K' THEN 'Primary Key'
        WHEN 'F' THEN 'Foreign Key'
        WHEN 'L' THEN 'Log'
        WHEN 'R' THEN 'Rule'
        WHEN 'RF' THEN 'Replication Filter stp'
        ELSE 'Other: ''' + t2.type+ ''''
    END [Object Type],
 t2.crdate [Created Date]
FROM syscomments t1
    INNER JOIN sysobjects t2 ON t1.id = t2.id
WHERE t1.[text] LIKE '%Your text need searching%'