Finding Text in Procedures, Functions and Triggers on SQL Server

The original function from 2017

I needed an option like this: to be able to find text in functions, triggers and stored procedures on an MSSQL server. The result was this function, Find_Text_In_SP, which joins the system tables sysobjects and syscomments and returns the name and type of every object whose code contains the search text.

CREATE FUNCTION [dbo].[Find_Text_In_SP]
(
  @String1ToSearch nvarchar(100)
)
returns table 

AS
   
  RETURN
  (
     SELECT Distinct SO.Name, so.type
     FROM sysobjects SO (NOLOCK)
      INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
      AND NOT(SO.Name LIKE N'MSmerge%')
      AND NOT(SO.name LIKE N'dt_%') 
      AND (not (SO.name in (N'GrantExectoAllProcedures_sp')))
      AND (left(SO.name, 6) <> N'sp_cft')
      AND (left(SO.name, 4) <> N'sel_')
      AND (left(SO.name, 6) <> N'sp_sel')
      AND (left(SO.name, 6) <> N'sp_upd')
      AND (left(SO.name, 6) <> N'sp_ins')
      AND SO.Type IN (N'P',N'FN',N'IF',N'TF',N'TR',N'D')
    WHERE (SC.Text LIKE N'%' + @String1ToSearch+ N'%')
      and NOT(SC.Text LIKE N'%' + @String1ToSearch+ N'-done!%')
  )  

What do the filters do?

The conditions in the query exclude objects that only get in the way of the search:

• MSmerge%: objects SQL Server creates for merge replication.
• dt_%: old system procedures for database diagrams.
• sp_cft, sel_, sp_sel, sp_upd, sp_ins: prefixes of automatically generated procedures in the project the function was written for. In another database, replace these prefixes with your own or remove them.
• -done!: a marker used in the code to flag places that have already been handled, so they are not listed again.
• SO.Type: the search covers procedures (P), scalar functions (FN), inline and table-valued functions (IF, TF), triggers (TR) and defaults (D).

Limitations of the original version

• sysobjects and syscomments exist only for backward compatibility with SQL Server 2000, and Microsoft does not recommend them for new code.
• syscomments stores object code in chunks of 4000 characters. Text that falls across the boundary of two chunks will not be found.
• Views (V) are not included in the search.
• NOLOCK on system tables brings no noticeable speed-up; the search is fast because the catalog is small compared to the data in the database.

Modern version

Since SQL Server 2005, the code of every programmable object is stored in the sys.sql_modules view, in the definition column of type nvarchar(max). The code is not split into chunks, so the text is always found, and the search covers procedures, functions, triggers and views.

CREATE FUNCTION [dbo].[Find_Text_In_Modules]
(
  @SearchText nvarchar(200)
)
RETURNS TABLE
AS
RETURN
(
  SELECT
    s.name        AS SchemaName,
    o.name        AS ObjectName,
    o.type_desc   AS ObjectType,
    o.modify_date AS ModifiedDate
  FROM sys.sql_modules AS m
  INNER JOIN sys.objects AS o ON o.object_id = m.object_id
  INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
  WHERE o.is_ms_shipped = 0
    AND CHARINDEX(@SearchText, m.definition) > 0
);

CHARINDEX is used instead of LIKE, so the characters _, % and [ in the search text have no special meaning. Whether upper and lower case are treated as different depends on the database collation. The condition is_ms_shipped = 0 leaves out objects installed by SQL Server itself, and the result also includes the schema and the date the object was last modified.

Usage example

Both functions are called like a table, with the search text as the parameter:

-- original version
SELECT * FROM dbo.Find_Text_In_SP(N'Customers');

-- modern version
SELECT *
FROM dbo.Find_Text_In_Modules(N'Customers')
ORDER BY ObjectType, ObjectName;

This kind of search is most useful before changing the database structure: before a column or table is renamed or dropped, you immediately see which procedures, functions and views use it. How to plan a database so that such changes are needed as rarely as possible is described in the article on database development.