How to search content of a routine/(SP-Trigger-function)
I need to search text within a routine body (Stored Procedure, function, trigg开发者_StackOverflower) of all routines within a database.. How do I do that..
Thanks
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
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.
精彩评论