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.
SQL Server 2008 database engine automatically keeps track of object dependency information whenever referencing entities are created, dropped or altered and it records this information in SQL Server system catalogs. 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.
Overview of sys.sql_expression_dependencies System View
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.
Overview of sys.dm_sql_referenced_entities Dynamic Management Function
- 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.
Overview of sys.dm_sql_referencing_entities Dynamic Management Function
- 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 to Identify Object Dependencies
Let us go through an example to understand how the SQL Server 2008 database engine tracks object dependencies.
Use the below T-SQL script to create a table named Employee.
Use SampleDB
GO
/* Create Employee Table */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]’) AND type in (N’U’))
DROP TABLE [dbo].[Employee]
GO
CREATE TABLE [dbo].[Employee]
(
[Emp_ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[Last_Name] [nvarchar](50) NULL,
[First_Name] [nvarchar](50) NULL,
[Age] [int] NULL,
)
GO
Execute the below T-SQL script to create usp_GetEmployeeName stored procedure. This procedure refers to Employee table which was created using the above script.
/* Create Stored Procedure “usp_GetEmployeeName” to return Employee Name */
CREATE PROCEDURE dbo.usp_GetEmployeeName
AS
BEGIN
SELECT Last_Name + ‘ ‘ + First_Name AS EmployeeName
FROM dbo.Employee
END
GO
Now that we have a sample table and stored procedure setup, the following queries can be used to find the referenced and referencing information.
Execute the below scripts which queries “sys.dm_sql_referencing_entities” dynamic management function to find out all objects which are referencing to “Employee” table.
/* Find all object which are referencing to “Employee” table */
SELECT
referencing_schema_name +’.’+ referencing_entity_name AS ReferencedEntityName,
referencing_class_desc AS ReferencingEntityDescription
FROM sys.dm_sql_referencing_entities (‘dbo.Employee’, ‘OBJECT’);
GO
Execute the below scripts which queries “sys.dm_sql_referenced_entities” dynamic management function to find out all objects which are referenced to “usp_GetEmployeeName” stored procedure.
/* Find all object which are referenced by “usp_GetEmployeeName” stored procedure */
SELECT
referenced_schema_name +’.’+ referenced_entity_name AS ReferencedEntityName,
referenced_minor_name AS ReferencedMinorName
FROM sys.dm_sql_referenced_entities (‘dbo.usp_GetEmployeeName’, ‘OBJECT’);
GO
Execute the below scripts which queries “sys.sql_expression_dependencies” system view to find out all the objects which are referenced by “usp_GetEmployeeName” stored procedure.
/* 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 = ‘usp_GetEmployeeName’
GO
Here is an example output of these queries for my test.
Next Steps
- If you do not have SQL 2008 already installed you can download SQL Server 2008 Enterprise 180 day evaluation from this site
- To know more about Dynamic Management Views refer to this tip Dynamic Management Views and Functions in SQL Server 2005
