searching text inside sql server sp/function/table

-- this works for SQL 2000 and better
SELECT DISTINCT
    o.xtype
    ,o.name
FROM    syscomments c
        INNER JOIN sysobjects o ON c.id=o.id
WHERE    c.TEXT LIKE '%transaction%'
order by 1,2

for sql server 2005+, use sys.procedures and OBJECT_DEFINITION:
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'