Search Text in all stored procedures of database

Hi All,

To those who want to search any text within all stored procedure of particular database then you can utilize the following system tables sys.sql_modules and sys.objects and search for given text.


Here is the MS-SQL query, be noted you need the elevated access to run this query.

SELECT DISTINCT
       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
WHERE m.definition Like '%\[ABC\]%' ESCAPE '\';


the above search the text [ABC] in all stored procedure objects in particular database.

Happy Coding!!!!!