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
, andsp_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.
Top comments (0)