Search code examples
sqlsql-serversql-server-2014database-administration

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.

Solution

  • 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