Find UDF useage

,

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.

-- create table to hold function names
CREATE TABLE #TempFunctions(
ID INT IDENTITY(1,1),
fnName VARCHAR(256)
);

-- create table to hold stored procedure and function that is in it names
CREATE TABLE #spWithFn (
ID INT IDENTITY(1,1),
spName VARCHAR(256),
fnName VARCHAR(256)
);
 
 -- fill the function name table
 -- FN = SQL_SCALAR_FUNCTION
 -- IF = SQL_INLINE_TABLE_VALUED_FUNCTION
 -- TF = SQL_TABLE_VALUED_FUNCTION
 INSERT #TempFunctions(fnName)
SELECT name
FROM SYS.OBJECTS WHERE TYPE IN ('FN','IF','TF')


DECLARE @fnName VARCHAR(256);
DECLARE @SQLCmd VARCHAR(512);

WHILE ((SELECT COUNT(1) FROM #TempFunctions) > 0)
BEGIN

-- get one function
SELECT TOP 1 @fnName = fnName FROM #TempFunctions ORDER BY fnName

-- search all stored procedures for that function name
SET @SQLCmd = 
'INSERT #spWithfn(spName,fnName)
SELECT Name, ''' + @fnName + ''' FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%' + @fnName + '%'''

EXEC(@SQLCmd);

-- delete function from function table
DELETE #TempFunctions
WHERE fnName = @fnName;

END;

-- report on stored procs and number of UDFs contained
SELECT spName,COUNT(1) NumberUDFs
FROM #spWithFn
GROUP BY  spName 
ORDER BY COUNT(1) DESC;


-- Show stored proc name and function name
SELECT * FROM #spWithfn ORDER BY spName;

DROP TABLE #tempFunctions;
DROP TABLE #spWithfn;

Rate

5 (2)

Share

Share

Rate

5 (2)