Hello, I would like to find all the stored procedures that has reference of Functions or called Functions from Store procedures. I found some slow performance issue from one of stored procedures that is calling functions. I would like to get whole list if I can find all the functions that been called from Stored procedures. Thank you!

Select Schema_Name(p.schema_id) As ProcedureSchema, p.name As ProcedureName, Schema_Name(f.schema_id) As FunctionSchema, f.name as FunctionName From sys.sql_expression_dependencies d Inner Join sys.objects p On p.object_id = d.referencing_id And p.type_desc = 'SQL_STORED_PROCEDURE' Inner Join sys.objects f On f.object_id = d.referenced_id And f.type In ('AF', 'FN', 'FS', 'IF', 'TF'); Tom

finding all stored procedures that is calling functions

Accepted answer

Tom Cooper 8,466 Reputation points

2020-10-19T18:20:51.473+00:00
Select Schema_Name(p.schema_id) As ProcedureSchema, p.name As ProcedureName, Schema_Name(f.schema_id) As FunctionSchema, f.name as FunctionName From sys.sql_expression_dependencies d Inner Join sys.objects p On p.object_id = d.referencing_id And p.type_desc = 'SQL_STORED_PROCEDURE' Inner Join sys.objects f On f.object_id = d.referenced_id And f.type In ('AF', 'FN', 'FS', 'IF', 'TF');

Tom
Please sign in to rate this answer.
Jinal Contractor 121 Reputation points

2020-10-19T19:47:49.443+00:00

Is there way I can find details on how frequently these all procedure are called and how long they take?

Erland Sommarskog 109.6K Reputation points MVP

2020-10-19T21:28:13.383+00:00

Which version of SQL Server are you on? If you are on SQL 2016 or later, you can enable Query Store to collect information. On older versions you would have to resort to traces.

Although you can collect some data from sys.dm_exec_procedure_stats, but keep in mind that it holds data about procedures that is in the cache right now.

Jinal Contractor 121 Reputation points

2020-10-19T21:42:30.75+00:00

I'm using SQL Server 2012.
Is there any script or something that I can pull the information? Because when I start trace using Profiler our system respond gets slower.

Erland Sommarskog 109.6K Reputation points MVP

2020-10-19T21:52:12.923+00:00

By all means, don't use Profiler! But you can use Profiler to set up a server-side trace. You will still need to spend time on aggregating the information.

But you could start with sys,dm_exec_procedure_stats. Maybe that is good enough for you.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

finding all stored procedures that is calling functions

0 additional answers

Your answer