mssqltips logo

Identifying Object Dependencies in SQL Server

By:   |   Updated: 2009-06-09   |   Comments (3)   |   Related: 1 | 2 | 3 | 4 | 5 | More > Database Design

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


Last Updated: 2009-06-09


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, June 16, 2016 - 5:56:16 AM - Saurabh Sharma Back To Top

--Added ISNULL so that if referenced_schema_name then result append is not null

 

SELECT 

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

ISNULL(SED.referenced_schema_name,'') +'.'+SED.referenced_entity_name AS ReferencedObject,SED.Referencing_Class_Desc,*

FROM sys.all_objects O INNER JOIN sys.sql_expression_dependencies SED 

ON O.OBJECT_ID=SED.REFERENCING_ID 

WHERE O.name = 'ABC'


Friday, June 26, 2009 - 2:41:18 PM - spineiro Back To Top

Does this mean that any reference to entities in dynamic SQL will be picked up?

 Eg. EXEC( 'SELECT * FROM foobar' )

Will table foobar be picked up as being referenced? If so, this would be very handy even though dynamic SQL should not be standard practice. Sometimes, you get that with inherited systems.


Tuesday, June 09, 2009 - 10:20:35 PM - @tif Back To Top

 Could you suggest any way to get list of objects that are being accessed in scheduled jobs?

Regards



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools