SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Find UDF useage

By Jim Youmans,

I was trouble shooting a slow running stored procedure and found the main culprit was the use of a scalar user defined function (UDF).  As you know when a scalar UDF is used, it can cause real performance issues.  I was able to solve the issue by removing the affending function but then started wondering if any of my other stored procedures were having the same issue without me knowing it.

So I came up with this script that looks at all the functions in a given database and then searches for them in all the stored procedures.  It will give you a list of the stored procedure names and a count of the UDFs in each.  Then it will give you a list of stored procedure and the names of the UFDs called by that stored procedure.

Beware that if you have a large number of functions and / or stored procedures, it can run for awhile.  I would run it during the off hours on a prduction server just to be safe.

Total article views: 340 | Views in the last 30 days: 7
 
Related Articles
FORUM

Calling Scalar Function within stored Procedure

Calling Scalar Function within stored Procedure

FORUM

Call Stored Procedure from a Function

Call Stored Procedure from a Function

FORUM

Implementing a function within a stored procedure

Implementing a function within a stored procedure

FORUM

Calling an Oracle Stored Procedure and Function

Calling an Oracle Stored Procedure and Function over linkedServer

FORUM

Stored procedure in User defined function?

Can I call Stored procedure in User defined function?

Tags
function    
sql    
stored procedure    
 
Contribute