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 

     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( LIKE N'dt_%') 
      AND (not ( in (N'GrantExectoAllProcedures_sp')))
      AND (left(, 6) <> N'sp_cft')
      AND (left(, 4) <> N'sel_')
      AND (left(, 6) <> N'sp_sel')
      AND (left(, 6) <> N'sp_upd')
      AND (left(, 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.

If you're looking for top-tier software developer to hire, look no further!

What I offer:

  • Website Development: Turn your idea into a fully functional website.
  • Mobile App Creation: Reach your audience on every device.
  • Custom Software Solutions: Software tailored to your business needs.
  • Database Management: Ensure your data is structured, secure, and accessible.
  • Consultations: Not sure where to start? Let's discuss the best tech solutions for your goals.

With 20+ years of experience in the tech industry, I've honed my skills to provide only the best for my clients. Let's turn your vision into reality. Contact me today to kick off your next digital project!