Finding all Stored procedures calling a function

Question

How can I find out all the stored procedures that are calling a particular user defined function in SQL Server 2005.

Or how to assign a defult value to a parameter in a user defined function so that when a stored procedure calls that function and does not pass any value to that parameter function assumes the default value.

Regards, Abhishek jain

Answer 1

QUERY sys.sql_modules
use this procedure, where you pass in the function name:

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

This procedure searches within procedures, views, and functions for the given string. You can search for any string, not just function names. You can also include wild cards in the middle of the given search term.

FUNCTION DEFAULTS
you can specify default values for function parameters. However, When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function to retrieve the default value. This behavior is different from using parameters with default values in stored procedures in which omitting the parameter also implies the default value.

try it out:

CREATE FUNCTION dbo.Just_Testing
(
    @Param1 int
    ,@Param2 int=0
)
RETURNS varchar(100)
BEGIN
    RETURN CONVERT(varchar(10),@Param1)+'-'+CONVERT(varchar(10),@Param2)
END
GO

PRINT 'hello world '+dbo.Just_Testing(2,default)+', '+dbo.Just_Testing(5,2)
GO
PRINT 'hello world '+dbo.Just_Testing(2        )+', '+dbo.Just_Testing(5,2)

OUTPUT:

hello world 2-0, 5-2
Msg 313, Level 16, State 2, Line 1
An insufficient number of arguments were supplied for the procedure or function dbo.Just_Testing.

But I'm guessing that you need to change a function, adding as parameter, and now need to fix it everywhere. This DEFAULT would still be as much work, since you need to touch every call made to it.

SP_DEPENDS
you can also use sp_depends (Transact-SQL) to find every usage of the function.