How to search content of a routine/(SP-Trigger-function)

Question

I need to search text within a routine body (Stored Procedure, function, trigger) of all routines within a database.. How do I do that..

Thanks

Answer 1

If you are searching within a single procedure, function or trigger, it may be easiest to script the procedure and do your search on the results.

You can run the following command in a query window

exec sp_helptext myProc

and work with the results. Or, you can use Object Explorer to navigate to the object you want to search and select to script the object.

Answer 2

SELECT 
    OBJECT_NAME(object_id)
FROM
    sys.sql_modules
WHERE
    definition LIKE '%' + 'WhatIWant' + '%'

Do not use INFORMATION_SCHEMA or sys.comments... they use nvarchar(4000) over 1 or more rows which means some searches will fail