none
Find Function

    Question

  • Hello,

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

    Wednesday, February 13, 2013 11:02 AM

Answers

  • --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!!




    • Edited by Stan210 Thursday, February 14, 2013 12:51 AM
    • Marked as answer by Iric WenModerator Thursday, February 21, 2013 9:39 AM
    Wednesday, February 13, 2013 1:25 PM

All replies

  • 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.

    Wednesday, February 13, 2013 11:05 AM
  •  Hello Latheesh NK,

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



    • Edited by Sanjay_S Wednesday, February 13, 2013 11:12 AM
    Wednesday, February 13, 2013 11:11 AM
  • 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

    Wednesday, February 13, 2013 12:36 PM
    Moderator
  • 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

    Wednesday, February 13, 2013 12:46 PM
    Answerer
  • Hi Sanjay,

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

    Wednesday, February 13, 2013 1:05 PM
  • --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!!




    • Edited by Stan210 Thursday, February 14, 2013 12:51 AM
    • Marked as answer by Iric WenModerator Thursday, February 21, 2013 9:39 AM
    Wednesday, February 13, 2013 1:25 PM
  • 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

    Wednesday, February 13, 2013 1:27 PM
    Answerer
  • try below command

    sp_depends 'testingfunc'



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

    Wednesday, February 13, 2013 1:41 PM
  • 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 .

    Wednesday, February 13, 2013 2:09 PM