Finding Text in Functions and Stored Procedures on SQL Server

I needed an option like this – to be able to find text in functions, triggers, and stored procedures on an MSSQL server.

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!%')
  )  

This function, Find_Text_In_SP, allows searching for text within functions, triggers, and stored procedures on an MSSQL server. It is useful for database administrators and developers who need to quickly find specific strings in their SQL objects. The function searches through various object types such as procedures, functions, and triggers, filtering the results to avoid system objects and those starting with certain prefixes.

The function uses the NOLOCK keyword in queries to avoid blocking and allow for fast searching. Various filters are also applied to ensure the search does not execute on unwanted objects, further speeding up the process.

A simple way to use this function is to input the string you are searching for as a parameter. For example, you can enter a part of the code or the name of a variable, and the function will return all objects that contain that string. This is especially useful when refactoring code or looking for specific implementations within large databases.

This function can save significant time and effort by allowing quick identification and analysis of SQL objects. It is particularly useful in environments where there are a large number of procedures and functions, as it enables fast finding of relevant objects and their modifications.