How to find a string in a procedure in SQL Server

To set the scene, for example, you need to find all the Procedures that access a certain Table in a SQL Server database. Its easy to find all the procedures where a string is referenced in the name, you would just do :-

Select * from sys.procedures where name like '%country%'

Finding the word hidden in the body of the SQL is a little longer and a tiny bit more complex. All you wold do is the following:-

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 '%country%';

When I say procedures, I am of course including Functions and Triggers. The type of procedure is in the second column of the record set.

Tags - SQL

Last Modified : June 2023