Recently, we were working on requirement and need to search a text in list of Stored Procedures. The funny thing, that stored procedure list is more than 2000. So, I started googling, and find following ways.
1)
SELECT
OBJECT_NAME(object_id),
OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%TEXT%'
2)
Select object_name(object_id), definition
From sys.sql_modules
Where definition like '%TEXT%' and objectpropertyex(object_id, 'isProcedure')=1
e.g. Lets take example, you need to search 100 in all stored procedure. Use following query.
1) SELECT
OBJECT_NAME(object_id),
OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%100%'
2)
Select object_name(object_id), definition
From sys.sql_modules
Where definition like '%100%' and objectpropertyex(object_id, 'isProcedure')=1
That's it. You will get results matching criteria. Just Enjoy. Happy coding.
