Find Referencing Entities in SQL Server

Understanding the relationship between different object entities is very crucial in complex databases because changes in any object can affect the overall database. The SQL Server provides a very effective dynamic management function known as sys.dm_sql_referencing_entities which helps the user to track the relationship between various entities in the database.

In this article, we are going to explore sys dm SQL referencing entities in SQL Server. We will learn how we can use various referencing entities in SQL servers to understand the relationship between various objects. We will learn about referencing entities, referenced entities, and how they are related. We will explore various examples for better understanding and learn step by step how we can use sys dm SQL referencing entities in SQL Server.

Introduction to sys.dm SQL Referencing Entities in SQL Server

In the SQL Server, it is very important to understand the relationship between the database entities for effective database management. The sys.dm_sql_referencing_entities is a dynamic management function that helps the users to fetch out these relationships between the database entities. This function allows database administrators to identify dependencies among the various objects in the SQL server which is crucial for debugging, and performance testing and helps to analyze the schema changes within the database. Before we move forward in this article it is important to understand the difference between the referenced entities and referencing entities in the SQL server

A dependency is created between two objects when one object appears by name inside a SQL statement stored in another object. The object which appears inside the SQL expression is known as the Referenced Entity and the object which has the SQL expression is known as the Referencing Entity.

For example, if we are creating a view Vw_Students for a table called Student then the view will be the referencing entity and the table will be the Referenced entity because the view that we have created refers to the table Student.

Syntax:

sys.dm_sql_referencing_entities (  
    ' schema_name.referenced_entity_name ' , ' <referenced_class> ' )  
  
<referenced_class> ::=  
{  
    OBJECT  
  | TYPE  
  | XML_SCHEMA_COLLECTION  
  | PARTITION_FUNCTION  
}

The sys.dm_sql_referencing_entities function mainly consists of 3 parameters:

• schema_name.referenced_entity_name Is the name of the referenced entity.
• schema_name is required except when the referenced class is PARTITION_FUNCTION.
• <referenced class> is the class of the referenced entity. Only one class can be specified per statement.

Why do We Need sys.dm Sql Referencing Entities in SQL Server?

The sys.dm_sql_referencing_entities is a valuable tool in SQL server. Following are some of the functionalities that we can get from this function:

• Dependency Tracking: In the complex databases the dependencies between objects can become confusion sometimes. sys.dm_sql_referencing_entities simplifies the process of tracking dependencies for the database administrator providing a clear picture of which object depends on each other.

• Impact Analysis : Before we make any changes to the database objects, such as changing a table or a stored procedure, it is very much important to understand the potential impact of these changes. sys.dm_sql_referencing_entities helps in identifying all the entities that depend on a specific object.

• Performance Tuning: Understanding the dependencies between database entities is important for performance tuning. It allows administrators and developers to optimize queries, stored procedures, or views that are heavily dependent upon other components of the system.

• Ensures data Integrity: Whenever a database becomes complex and consists of multiple tables there can be situations when a user might delete a table on which other tables are dependent via the foreign keys. So sys.dm_sql_referencing_entities ensures that user is not allowed to make such changes and ensures that the data integrity is maintained in the database.

• Troubleshooting Issues: sys.dm_sql_referencing_entities is a very effective tool used by the database admins for troubleshooting various issues. It provides a graphical representation of dependencies among the objects which makes it easy for the administrators to find the potential issues effectively.

Examples of sys.dm Sql Referencing Entities

For better understanding let us consider three demo tables Departments, Employees and Project who are dependent on each other.

You can create these tables if you want using the following query:

-- Create Departments table
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(50)
);

-- Create Employees table referencing Departments
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(50),
    DepartmentID INT FOREIGN KEY REFERENCES Departments(DepartmentID)
);

-- Create Projects table referencing Employees
CREATE TABLE Projects (
    ProjectID INT PRIMARY KEY,
    ProjectName NVARCHAR(50),
    EmployeeID INT FOREIGN KEY REFERENCES Employees(EmployeeID)
);

Example 1: Check the Dependency of a View that References a Table

In this example we will create a view that references the table Department and then we will try to extract the dependencies between the table and view.

In the following example at first we will create a view which will act as a dependency on the table Departments and then we will see how we can extract the view dependencies of the table Department.

-- Create  a view that references the 'Departments' table
CREATE VIEW vw_Departments AS
SELECT * FROM dbo.Departments;

--query to find out the dependencies

SELECT referencing_schema_name, referencing_entity_name, referencing_class_desc
FROM sys.dm_sql_referencing_entities('dbo.Departments', 'OBJECT');

Output:

Output

Explanation: The following query uses the sys.dm_sql_referencing_entities dynamic management function to fetch all the dependencies which references to the Department table. referencing_schema_name defines the name of the schema of the object that refers the Department table. referencing_entity_name defines the name of the object that references the Department table and referencing_class_desc defines the type of the entity which refers to the department table. Here the view vw_Departments is the only dependency present here which is returned in the result set.

Example 2: Check the Dependency of a Procedure that References a Table

In the following example at first we will create a Procedure which will act as a dependency on the table Employees and then we will see how we can extract the view dependencies of the table Employees.

-- Create a Procedure that references the Department Table

CREATE PROCEDURE usp_GetEmployeeList
AS
BEGIN
    SELECT * FROM dbo.Employees;
END;

-- --query to find out the dependencies

SELECT referencing_schema_name, referencing_entity_name, referencing_class_desc
FROM sys.dm_sql_referencing_entities('dbo.Employees', 'OBJECT');

Output:

Output

Explanation: The following query uses the sys.dm_sql_referencing_entities dynamic management function to fetch all the dependencies which references to the Department table. referencing_schema_name defines the name of the schema of the object that refers the Department table. referencing_entity_name defines the name of the object that references the Department table and referencing_class_desc defines the type of the entity which refers to the department table. Here the procedure usp_GetEmployeeList is the only dependency present here which is returned in the result set.

Example 3: Non Schema Bound Dependencies

In this example we will create a non schema bound dependency on the table Department. Then we will see how non schema bound dependencies are not suitable when we have references in our database.

Schema bound dependencies prevents referenced objects from being deleted or modified as long as the referencing object exists.

CREATE VIEW view1_Departments AS
SELECT * FROM dbo.Departments;

Output:

Table Dropped Successfully

Explanation: In the following example we created a view which acts as a non schema bound dependency on the table Department. But if we drop the table Department then it is allowed but it shouldn’t be because after the table is dropped the dependency view view1_Department still exists in the database which will create a lot of confusion. So to avoid this error we should use schema bound dependencies which you will see in the next example.

Example 4: Check for Schema Bound Dependencies

In this example we will create a schema bound dependency on the table Department. Then we will see how schema bound dependencies can resolve the drawbacks of the non schema bound dependencies. To create schema bound dependency just use the WITH SCHEMABINDING clause .

CREATE VIEW vw_Departments
WITH SCHEMABINDING
 AS
SELECT DepartmentName FROM dbo.Departments;

Output:

Can’t drop the table

Explanation: In the following example we have created a schema bound dependency view vw_Departments on the table Departments. Now if we try to drop the table Departments we won’t be allowed because schema bound dependencies prevents referenced objects from being deleted or modified as long as the referencing object exists. We can drop the table if we want when all the dependencies on the table are deleted.

Example 5: Check all the Dependencies in your Database

In the following example we will see how we can extract all the dependencies of multiple tables present in a single database.

-- Combine dependencies for multiple tables

SELECT referencing_schema_name, referencing_entity_name, referencing_class_desc
FROM sys.dm_sql_referencing_entities('dbo.Employees', 'OBJECT')
UNION
SELECT referencing_schema_name, referencing_entity_name, referencing_class_desc
FROM sys.dm_sql_referencing_entities('dbo.Departments', 'OBJECT')
UNION
SELECT referencing_schema_name, referencing_entity_name, referencing_class_desc
FROM sys.dm_sql_referencing_entities('dbo.Projects', 'OBJECT');

Output:

Output

Explanation: The following query provides a broader view of the database by combining the dependencies of the multiple tables present in the database. This query can be used when you want to extract all the dependencies present in a single database. Here all the dependencies of our three tables Employees, Departments and Projects is extracted as a result set.

Exceptions

The dynamic management function sys.dm_sql_referencing_entitites will return an empty result under the following conditions:

• The mentioned entity does not exists in the current database

• The specified entity does not reference any entities

• A system object is specified.

• An invalid parameter is passed.

So be careful while executing your queries if any of the above condition is true then you will not get the specified results.

Conclusion

In this article we have learned about sys dm sql referencing entities in SQL Server. We have learned how we can use this dynamic management function to extract all the relations between the object dependencies in our database. We have learned about the difference between referenced entities and referencing entities. We also learnt about the schema bound and non-schema bound dependencies. So we hope that this article has helped you to learn about sys dm sql referencing entities in SQL Server.