Tag Archives: sys.dm_sql_referenced_entities

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

DMVS, Sql Server, SQL Server 2008 What's New, Tips/Tricks, , ,

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.

sp_depends results are not reliable

DMVS, Sql Server, SQL Server 2008 What's New, System Stored Procedures, Tips/Tricks, , , , ,

sp_depends System Stored procedure will give the list of referencing entities for a table/view and list of referenced entities by the stored procedure/view/function. As per MSDN this system stored procedure is going to be removed from the future versions of the Sql Server.

sp_depends results are not always reliable/accurate/correct. Good and reliable alternative for sp_depends are the DMV’s: sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities.

Let us see this with an example the incorrect results returned by the sp_depends system stored procedure:

CREATE DATABASE DemoSQLHints
GO
USE DemoSQLHints
GO
CREATE PROCEDURE dbo.GetEmployeeDetails
AS
BEGIN
	SELECT * FROM dbo.Employee
END

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
-- Get the list of objects which are referring Employee table using
sp_depends [dbo.Employee]
GO
-- Get the list of objects referenced by the SP:GetEmployeeDetails
sp_depends [dbo.GetEmployeeDetails]
GO

Result

Object does not reference any object, and no objects reference it.
Object does not reference any object, and no objects reference it.

Both the sp_depends statements in the above script are not returning the referencing/referenced objects list. For example the stored procedure dbo.GetEmployeeDetails is referring the Employee table but sp_dpends is not providing this dependency information. Reason for this is: Stored procedure dbo.GetEmployeeDetails which is refereeing to this Employee table is created first and then the employee table in other words we call it as deferred resolution.

Solution to this problem is to use the DMV’s: sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities. Now let us check whether we are able to get the expected results using these DMV’s:

SPDependsAlternative

Note: These two DMV’s are introduced as a part of Sql Server 2008. So this alternate solution works for Sql Server version 2008 and above.

You may also like to read my other article: 

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