SQL Object dependency identification

Problem:
Is there a way to identify which objects are dependent on other objects within a SQL Server database? I need to modify the table structure to add new columns. However, before making any changes I want to make sure that I understand all the object dependencies. Is there a way in SQL Server 2008 to quickly identify all the object dependencies?

Solution
In the earlier versions of SQL Server object dependencies were tracked using the ID of the object, as a result it was tough to keep track of Object Dependencies. However, in SQL Server 2008 Objects are tracked using the name of the object rather than object IDs. The biggest advantage of this approach is that you can track object dependency even after the object is removed from the database and you can also track even if an object is yet to be created in the database.

In SQL Server 2008 there are two new Dynamic Management Functions and a System View introduced to keep track of Object Dependencies. The newly introduced Dynamic Management Functions in SQL Server 2008 to keep track of object dependencies are sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities.  The newly introduced system view to keep track of object dependency is sys.sql_expression_dependencies.

The sys.sql_expression_dependencies system view holds one record each for a user defined object which has dependency on other object within the current database. A dependency between two objects is created when one object calling the referenced object appears by name in a SQL expression of another object. There are basically two types of dependencies tracked by the database engine in SQL Server 2008 namely Schema-bound Dependency and Non-schema-bound Dependency.

• Schema-bound dependency: - A schema-bound dependency is a relationship that exists between two objects that prevents referenced objects from being dropped or modified as long as the referencing object exists. A quick example of schema-bound dependency will be a view or a user defined function which is created using WITH SCHEMABINDING clause.
• Non-schema-bound dependency: - A non-schema-bound dependency is a relationship which exists between two objects which doesn’t prevent the referenced object from being dropped or modified.

The sys.dm_sql_referenced_entities Dynamic Management Function returns one row for each user defined object which is referenced by name within the definition of a specified referencing object. For example, if a user defined view is the specified referencing object, then by using sys.dm_sql_referenced_entities dynamic management function you can return all the user defined objects that are referred in the view definition such as tables, functions etc.

 

The sys.dm_sql_referencing_entities Dynamic Management Function returns one record for each user defined object within the current database which refers to another user defined object by name. For example, if there is a view which refers to three tables then this function will return three records one for each table reference. This function will also return dependency information for Schema-bound or Non-schema-bound entries, Database level and Server level DDL triggers.

 

 

 

Example:

 

/* Find all object which are referencing to <OBJECT_NAME> OBJECT */

SELECT

referencing_schema_name +'.'+ referencing_entity_name AS ReferencedEntityName,

referencing_class_desc AS ReferencingEntityDescription

FROM sys.dm_sql_referencing_entities (<OBJECT_NAME>, 'OBJECT');

GO

 

/* Find all object which are referenced by <OBJECT_NAME> OBJECT */

SELECT

referenced_schema_name +'.'+ referenced_entity_name AS ReferencedEntityName,

referenced_minor_name AS ReferencedMinorName

FROM sys.dm_sql_referenced_entities (<OBJECT_NAME>, 'OBJECT');

GO

 

/* Identifying Object Dependencies */

SELECT

SCHEMA_NAME(O.SCHEMA_ID) +'.'+ o.name AS ReferencingObject,

SED.referenced_schema_name +'.'+SED.referenced_entity_name AS ReferencedObject

FROM sys.all_objects O INNER JOIN sys.sql_expression_dependencies SED

ON O.OBJECT_ID=SED.REFERENCING_ID

WHERE O.name = <OBJECT_NAME>

GO