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.

Answer 2

  declare @SearchValue as varchar(50)   set @SearchValue = 'GETUTCDATE'   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