ProgrammerGuide.Net | Step by Step Programmer Guide

Finding all Stored Procedures that calls a Function in SQL Server | SQL Server Tutorial

The below Stored Procedure searches within all the stored procedures in the SQL server database, views, and functions for the given function name. This stored procedure also search for any text written in the stored procedure, not just function names.

CREATE PROCEDURE dbo.Find_Text
    @SearchValue nvarchar(500)
AS

SELECT DISTINCT
    s.name+'.'+o.name AS Object_Name,o.type_desc
    FROM sys.sql_modules        m
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
        INNER JOIN sys.schemas  s ON o.schema_id=s.schema_id
    WHERE m.definition Like '%'+@SearchValue+'%'
        --AND o.Type='P'  --<uncomment if you only want to search procedures
    ORDER BY 1
GO

Please click here for related products on Amazon!

Jayashee

Add comment

Want to Check Celebrities News?