sys dm sql referencing entities in SQL Server
sql server referencing entities
sql server referenced objects
sql server schema bound dependency
sql server non schema bound dependency
In this video we will discuss
How to find object dependencies using the following dynamic management functions
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
Referencing entity and Referenced entity
Schema-bound dependency and Non-schema-bound dependency
This is continuation to Part 131, in which we discussed how to find object dependencies using SQL Server Management Studio. Please watch Part 131 from SQL Server tutorial before proceeding.
The following example returns all the objects that depend on Employees table.
Select * from sys.dm_sql_referencing_entities(‘dbo.Employees’,’Object’)
Difference between referencing entity and referenced entity
A dependency is created between two objects when one object appears by name inside a SQL statement stored in another object. The object which is appearing inside the SQL expression is known as referenced entity and the object which has the SQL expression is known as a referencing entity.
To get the referencing entities use use sys.dm_sql_referencing_entities dynamic management function
To get the referenced entities use use sys.dm_sql_referenced_entities dynamic management function
Now, let us say we have a stored procedure and we want to find the all objects that this stored procedure depends on. This can be very achieved using another dynamic management function, sys.dm_sql_referenced_entities.
The following query returns all the referenced entities of the stored procedure sp_GetEmployeesandDepartments
Select * from sys.dm_sql_referenced_entities(‘dbo.sp_GetEmployeesandDepartments’,’Object’)
Please note : For both these dynamic management functions to work we need to specify the schema name as well. Without the schema name you may not get any results.
Difference between Schema-bound dependency and Non-schema-bound dependency
Schema-bound dependency : Schema-bound dependency prevents referenced objects from being dropped or modified as long as the referencing object exists
Example : A view created with SCHEMABINDING, or a table created with foreign key constraint.
Non-schema-bound dependency : A non-schema-bound dependency doesn’t prevent the referenced object from being dropped or modified.
Link for all dot net and sql server video tutorial playlists
Link for slides, code samples and text version of the video