Find Function

Question

Hello,

How to find a function is associated with how many stored procedures?

Answer 1

Try the below:

Select OBJECT_NAME(Object_id) From sys.sql_modules Where definition like '%<function_Name>%'

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

 Hello Latheesh NK,

I did same earlier but this is displaying Stored procedure which does not have these(Search Text) text also

Answer 3

The following blog has query to list object dependencies:

http://www.sqlusa.com/bestpractices2008/object-dependency/

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 4

Hmm are  you saying that it returns stored procedures that do not have this udf? It should display all procedures that do have (could be commented out) this UDF.

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 5

Hi Sanjay,

you can right click on function name in object explore and click on "View Dependencies"

Answer 6

--for all procedure references
select referenced_entity_name,object_name(A.referencing_id) from sys.sql_expression_dependencies A
inner join sys.objects B on B.object_id=A.referencing_id
where B.type= 'P'

--or
--for a particular one
select referenced_entity_name from sys.sql_expression_dependencies A
where referencing_id= object_id('<<ProcedureName>>')
--to get the list of all procs, function is referencing.
select C.name as [SchemaName],Object_Name(referencing_id) as [ProcedureName]  from sys.sql_expression_dependencies A
INNER JOIN sys.objects B ON  A.referencing_id = B.Object_id 
INNER JOIN sys.schemas C  on C.schema_id=B.schema_id
where  referenced_entity_name= <<YourFunction>>

to do the above using tsql..try this.this just gives you the function name being referred from the store procedure .if you want more details, please refer kalman Toth's link

---

Hope it Helps!!

Answer 7

SELECT 
QUOTENAME(OBJECT_SCHEMA_NAME ( referencing_id )) + '.' +
QUOTENAME(OBJECT_NAME(referencing_id)) AS ProcName
,QUOTENAME(OBJECT_SCHEMA_NAME ( referenced_id )) + '.' +
QUOTENAME(OBJECT_NAME(referenced_id)) AS ReferencedObjectName
,referenced.type_desc
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS referencing ON sed.referencing_id = referencing.object_id
INNER JOIN sys.objects AS referenced ON sed.referenced_id = referenced.object_id
WHERE referencing.type_desc = 'udf_name;

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 8

try below command

sp_depends 'testingfunc'

---

Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

Answer 9

SELECT *
FROM information_schema.routines R WHERE CHARINDEX('dbo.YourFunction', R.ROUTINE_DEFINITION) > 0
GO

---

Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Dia Agha .