Finding all Stored procedures calling a function

Question

Hi,

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

A 'low-tech' way to find all the stored procedures using a function is to use management studio to "generate scripts" for all the procs into a single file, and then use the editor window to search on the keywords you want to find.

Answer 2

Just use this procedure to find any text in your stored procedures.

   CREATE PROCEDURE [dbo].[Find_Text_In_SP]
@StringToSearch varchar(100) 
AS 
   SET @StringToSearch = '%' +@StringToSearch + '%'
   SELECT Distinct SO.Name
   FROM sysobjects SO (NOLOCK)
   INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
   AND SO.Type = 'P'
   AND SC.Text LIKE @stringtosearch
   ORDER BY SO.Name

Answer 3

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 4

You need to be careful when using the SYSCOMMENTS table as suggested by hgulyan... That table has the definition of the object broken into multiple rows and could cause your search criteria to be missed if it was broken apart across two entries. In SQL 2005 and later you can use the SYSMODULES table instead. Run the following code to see the differences and look for occurrences where keywords (i.e. - possibly your search phrase) have been split across multiple lines when using the syscomments method...

SELECT  TOP 1000 SO.NAME, SC.TEXT
FROM        SYS.SYSOBJECTS  SO
JOIN        SYS.SYSCOMMENTS SC
    ON      SO.ID = SC.ID
WHERE       SO.TYPE = 'P'
ORDER BY SO.NAME, SC.COLID


SELECT  TOP 1000 SO.NAME, SM.DEFINITION
FROM        SYS.SYSOBJECTS  SO
JOIN        SYS.SQL_MODULES SM
    ON      SO.ID = SM.[OBJECT_ID]
WHERE       SO.TYPE = 'P'
ORDER BY SO.NAME