How to find specific text from all stored procedures - MSSQL

Published on Thursday, 19 June 2014

We are currently making some changes to the database and needed a way of finding specific text within all the stored proceedures.

The following SQL achieved the task perfectly.

SELECT DISTINCT o.name AS Object_Name, o.type_desc, m.definition
FROM sys.sql_modules AS m 
INNER JOIN sys.objects AS o 
ON m.object_id=o.object_id
WHERE m.definition Like '%TEXT_HERE%'