Object Dependency in SQL Server

April 23, 2010

Object Dependency Described

A dependency is created when one SQL Server object, the referencing entity, refers to another SQL Server object, the referenced entity. An example of this is a view on a table. The view is the referencing entity and the table is the referenced entity.

SQL Server records two types of dependency: schema-bound and non-schema-bound dependencies. Schema-bound dependencies are those dependencies that prevent the referenced object from being altered or dropped without first removing the dependency. An example of a schema-bound reference would be a view created on a table using the WITH SCHEMABINDING option. Below is an illustration. We create a view WITH SCHEMABINDING on table Orders. When we attempt to drop the table Orders, SQL Server alerts you to the dependency.

CREATE VIEW RecentOrdersV WITH SCHEMABINDING
AS 
SELECT OrderID,
       CustID,
       OrderStatus,
       OrderDTM
  FROM dbo.Orders
 WHERE DATEADD( DAY, 7, OrderDTM) >= GETDATE()
GO
DROP TABLE Orders
Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'orders' because it is being referenced by object 'RecentOrdersV'.

Non-schema-bound dependencies are those dependencies that do not prevent the referenced object from being altered or dropped. An example of this is a stored procedure that selects from a table. The table can be dropped without first dropping the stored procedure or removing the reference to the table from that stored procedure. Consider the following.

CREATE TABLE TestTable
( Col1      INT NOT NULL)
GO
 
CREATE PROCEDURE spTestProc
AS
BEGIN 
SELECT Col1 
  FROM TestTable
END
GO
 
EXEC spTestProc
GO
 
(0 row(s) affected)
 
DROP TABLE TestTable
GO
 
EXEC spTestProc
 
Msg 208, Level 16, State 1, Procedure spTestProc, Line 4
Invalid object name 'TestTable'.

The stored procedure was created with a dependency on the table TestTable and was able to execute. Because the dependency was non-schema-binding, we were able to drop the table without first dropping the stored procedure, but the stored procedure could no longer execute without error.

One important observation to note here is that SQL Server did not need the table object to exist to still maintain the dependency from the stored procedure. This is because the dependency is recorded in the system catalog by name and also by Object ID if the ID is available because the object has been created. In this case, the table did exist when the stored procedure was created, so SQL Server stored both the name and ID of the referenced and referencing entities for the dependency. When the table was dropped, the dependency information was updated to remove the object ID for the table, but the object name remained.

Dependency Investigation

If you work in an environment like mine where people are expected to give estimates on not only the hours required to perform a change but also on the impact of that change, you might also find it quite useful to gather information on these object dependencies before embarking on changes to your schema. Fortunately, SQL Server provides tools that allow us to investigate dependencies. There is a catalog view and a dynamic management function we will utilize here to give us a look into the dependency data stored by SQL Server.

sys.sql_expression_dependencies

With the catalog view sys.sql_expression_dependencies you can find out referencing and referenced entity names, servers, databases, class information as well as whether the reference is schema-bound or not . However, you cannot find column-level references for non-schema-bound dependencies with this view.

This dependency investigation tool is available to database owners and administrators. If they choose to allow others to see the dependencies, they would need to grant them VIEW DEFINITION and select permission on the catalog view.

Let’s take a look at a sample of what this catalog view can show. Here is a continuation of the example above where a stored procedure references a table. We’ll create the table, reference it in a stored procedure we create, and then see what this catalog view returns for the dependency.

CREATE TABLE TestTable
( Col1      INT NOT NULL)
GO
 
CREATE PROCEDURE spTestProc
AS
BEGIN 
SELECT Col1 
  FROM TestTable
END
GO
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name,
       referenced_server_name       AS server_name,
       referenced_database_name     AS database_name,
       referenced_schema_name       AS schema_name,
       referenced_entity_name
  FROM sys.sql_expression_dependencies 
 WHERE referencing_id = OBJECT_ID(N'dbo.spTestProc')
GO

Similarly, we can find entities that contain references to an entity we are concerned with. If we take the table above and modify the query on the catalog view slightly, we see the same information from a different perspective.

SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name,
       referenced_server_name       AS server_name,
       referenced_database_name     AS database_name,
       referenced_schema_name       AS schema_name,
      referenced_entity_name
  FROM sys.sql_expression_dependencies 
 WHERE referenced_entity_name = N'TestTable'
GO

sys.dm_sql_referencing_entities

The dynamic management function sys.dm_sql_referencing_entities can be used to find dependencies within the same database. It requires CONTROL permission on the entity in question and select permission on the dynamic management function.

Using the same example as above where we are looking for objects dependent on TestTable, we can write the query below utilizing the dynamic management function instead and see the same reference appear.

SELECT referencing_schema_name, referencing_entity_name, referencing_id,   referencing_class_desc, is_caller_dependent
  FROM sys.dm_sql_referencing_entities ('dbo.TestTable', 'OBJECT')
GO

View Dependencies in SQL Server Management Studio

From within SQL Server Management Studio there is an option to view the objects that are dependent on an object of interest and also those on which it depends. For instance, we can examine dependencies for our example procedure from above, spTestProc. To do this, open SQL Server Management Studio, navigate to your database, then Programmability, and the stored procedure of interest. Right click on the procedure and select View Dependencies from the menu. The Object Dependencies window opens. From within that window you can view either the objects that depend on this object by selecting the “Objects that depend on [Object Name]” radio button or view the objects it depends on by selecting the “Objects on which [Object Name] depends” radio button.

From the screen shot below, you can see the stored procedure, spTestProc, has a dependency on the table TestTable. Looking towards the bottom of the screen, you can find out the dependency type. In this case the dependency type is non-schema-bound.

Conclusion

In this article we took a look at two different types of SQL Server object dependency and how we can utilize a catalog view and a dynamic management function to investigate those dependencies. Additionally, the View Dependencies option within SQL Server Management Studio gives a hierarchical view of the dependencies in a nice user interface.

For More Information

Determine Object Dependencies Before Changing Your SQL Server Schema Design

MSDN: Understanding SQL Dependencies

MSDN: Reporting SQL Dependencies

MSDN: sys.sql_expression_dependencies (Transact-SQL)

MSDN: sys.dm_sql_referencing_entities (Transact-SQL)

» See All Articles by Columnist Deanna Dicken