Find Text in MSSQL Stored Procedures and Functions with a Custom Function

#mssql #sqlfunctions #textsearch #sqlserver

MSSQL Server Function: Find_Text_In_SP

Purpose

The Find_Text_In_SP function is a table-valued function that searches for a specific text within the stored procedures, functions, and other objects in a SQL Server database. It identifies objects where the provided text pattern exists, making it useful for developers who need to locate text references in their SQL objects.

Parameters

@String1ToSearch: nvarchar(100) - The text pattern to search for within the SQL objects.

Return

The function returns a table with two columns:

Name: The name of the object (stored procedure, function, etc.) containing the search text.
Type: The type of SQL object, such as:
- P for Stored Procedure
- FN for Function
- IF for Inline Table-Valued Function
- TF for Table-Valued Function
- TR for Trigger
- D for Default constraint

Filtering Logic

This function excludes:

System-generated objects that start with MSmerge, dt_, sp_cft, sel_, sp_sel, sp_upd, and sp_ins.
Specific procedure GrantExectoAllProcedures_sp.
Text that matches @String1ToSearch with a -done! suffix.

Example Usage

To search for occurrences of the term "CustomerData" within stored procedures and functions, execute:

SELECT * FROM [dbo].[Find_Text_In_SP]('CustomerData');

Final Thoughts

This function is incredibly useful for quickly locating references within SQL objects, saving time and streamlining database management.

For more details about it and much more similar posts, check this page on my blog where you can find Find_Text_In_SP full implementation: Searching for Functions on the Server.