Object Dependency in SQL Server

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