How to find referenced/dependent objects (like Table, Function etc) of a Stored Procedure/Function in Sql Server?

First of all we shouldn’t use the system stored procedure sp_depends for this as it is not reliable. For more details on sp_depends you can refer to the article sp_depends results are not reliable.

For this in Sql server 2008 new Dynamic Management Function sys.dm_sql_referenced_entities is introduced. This dynamic management function provides all the entities in the current database which are referenced by a stored procedure or function.

Let us understand this with an example. First create a table, then a stored procedure which references the table.

CREATE DATABASE DemoSQLHints
 GO
 USE DemoSQLHints
 GO
CREATE TABLE dbo.Employee
( Id int IDENTITY(1,1), 
  FirstName NVarchar(50), 
  LastName NVarchar(50)
)
GO
 INSERT INTO dbo.Employee(FirstName, LastName)
 VALUES('BASAVARAJ','BIRADAR')
GO
CREATE PROCEDURE dbo.GetEmployeeDetails
 AS
 BEGIN
 SELECT * FROM dbo.Employee
 END
GO

Now we can use a script like below to find all the entities in the current database which are referenced by the stored procedure dbo.GetEmployeeDetails

SELECT referenced_schema_name, referenced_entity_name, 
 referenced_minor_name
FROM sys.dm_sql_referenced_entities('dbo.GetEmployeeDetails', 
          'OBJECT')
GO

Result:
dm_sql_refererenced_entities
Note: 1) This Dynamic Management Function is introduced as a part of Sql Server 2008. So above script works in Sql Server version 2008 and above.
2) While specifying the stored procedure name please include schema name also, otherwise referenced objects list will not be displayed.

You may also like to read my other articles:
How to find all dependencies of a table in Sql Server?

Please correct me if my understanding is not correct. Comments are always welcome.

3 thoughts on “How to find referenced/dependent objects (like Table, Function etc) of a Stored Procedure/Function in Sql Server?

  1. `dm_sql_referenced_entities` has it’s own set of problems — like `sp_depends` it “lies” most of the time.

    Consider this slight modification of your stored procedure
    CREATE PROCEDURE dbo.GetEmployeeDetails
    AS
    BEGIN
    CREATE TABLE #TmpFilter(Id INT)
    INSERT #TmpFilter(Id) VALUES(1)

    SELECT * FROM dbo.Employee
    WHERE Id IN (SELECT Id FROM #TmpFilter)
    END

    Now try the `dm_sql_referenced_entities` view — it does not report column usage at all the moment a temp table is accessed in the same query as the base table. Table vars are ok in this case.

  2. Hi Basavaraj,

    I got a task that i need to move some 70 SPs along with its dependent tables to a New Database from existing database.
    So could you help me how to retrieve all the dependents for 70 Sps at once using sys.dm_sql_referenced_entities ?

    Thanks in advance.

Leave a Reply

Your email address will not be published.