CODEWITHSUNDEEP
sqlsql-serversql-server-2014database-administration
Iswar
Iswar

Reputation: 2301

SQL Server 2014 - List all functions and sub-functions used in the stored procedure

I am trying to find the stored procedure from my application and finding function and sub function along with views for redesigning the application in SQL Server 2014. What I am trying is taking lots of time and I want to find with the help of SQL query like this:

 SELECT 
     OBJECT_NAME(object_id), 
     OBJECT_DEFINITION(object_id)
 FROM 
     sys.procedures
 WHERE 
     OBJECT_DEFINITION(object_id) LIKE '%fn%'

This is retrieving all the stored procedures which is using functions. My requirement is as follows:

  1. SQL query to find functions and sub functions used in particular stored procedure.
  2. Find all the views used either in functions or in stored procedure.

My naming convention are as follows:

  1. P_* for stored procedure
  2. FN_* for function
  3. VW_* for views

My application is huge so manually finding stored procedure are easy but finding functions and views are really takes time.

Upvotes: 2

Views: 2690

Answers (2)

bmsqldev
bmsqldev

Reputation: 2735

You can use sp_depends to return all the objects depends on the particular sp. Syntax : sp_depends "your procedure name".

If you want only particular types of objects (eg: views only) depends on a stored procedure, then you can try as below,

SELECT ReferencingObjectType = o1.type,
       ReferencingObject = SCHEMA_NAME(o1.schema_id)+'.'+o1.name,
       ReferencedObject = SCHEMA_NAME(o2.schema_id)+'.'+ed.referenced_entity_name,
       ReferencedObjectType = o2.type
FROM   AdventureWorks2008.sys.sql_expression_dependencies ed
       INNER JOIN  AdventureWorks2008.sys.objects o1
         ON ed.referencing_id = o1.object_id
       INNER JOIN AdventureWorks2008.sys.objects o2
         ON ed.referenced_id = o2.object_id
WHERE o1.type in ('P','TR','V', 'TF', 'FN')
ORDER BY ReferencingObjectType, ReferencingObject

(Note : P - stored procedures, TR -Triggers , V - Views, TF - Table valued functions, FN - Scalar functions)

Upvotes: 1

Vladimir Baranov
Vladimir Baranov

Reputation: 32703

You can use sp_depends to get a list of dependent objects (including functions) for a particular stored procedure using T-SQL.

In SSMS you can right-click on your particular stored procedure in the Object Explorer and choose View Dependencies item in the context menu:

depend1

This will open a window with the tree of dependent objects, including functions:

depend2

Upvotes: 1

Related Questions