search all stored procs for a literal or string

Is there a way开发者_高级运维 to search for a string or literal in all stored procedures for a particular database in Microsoft SQL Server 2008?

Grab yourself a copy of the free Red-Gate SQL Search tool and start enjoying searching in SQL Server! :-)

It's a great and very useful tool, and YES! it's totally, absolutely FREE for any kind of use.

select quotename(s.name)+'.'+quotename(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
                and o.type = 'P' /* stored procuedure */
        inner join sys.schemas s 
            on o.schema_id = s.schema_id
    where m.definition like '%YourSearchText%'

I think this will work for you

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%lq_Campaign%' 
AND ROUTINE_TYPE='PROCEDURE'

Here is the a link I found on it.