Deleting or changing objects may affect other database objects like views or procedures that depends on them and in certain instances, can “break” the depending object. An example can be that if a View queries a table and the name of that table changes. The View will no longer function.
To understand the interdependencies of our database it is very helpful to see and analyze these dependencies in a SQL Server dependency tree, and ultimately to even create a SQL dependency diagram visually displaying the hierarchical relationships
In SQL Server there are several ways to find object dependencies and create a SQL dependency tracker.
- The sp_depends system stored procedure
-
SQL Server dynamic management functions including
- sys.dm_sql_referencing_entities
- sys.dm_sql_referenced_entities
- The View Dependencies feature in SQL Server Management Studio (SSMS)
sp_depends
sp_depends is a system stored procedure that displays information about all object types (e.g. procedures, tables, etc) that depend on the object specified in the input parameter as well as all objects that the specified object depends on.
The sp_depends procedure accepts one parameter, the name of a database object. E.g. EXECUTE sp_depends ‘ObjectName’
Below are examples, which will be used in this article:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
-- New database CREATE DATABASE TestDB; GO USE TestDB GO CREATE TABLE UserAddress ( AddresID INT PRIMARY KEY IDENTITY(1, 1) ,FirstName VARCHAR(100) ,Lastname VARCHAR(150) ,Address VARCHAR(250) ) GO -- New procedure CREATE PROCEDURE sp_GetUserAddress AS BEGIN SELECT FirstName ,Lastname ,Address FROM UserAddress END GO CREATE TABLE Address ( ID INT NOT NULL IDENTITY(1, 1) ,City VARCHAR(120) ,PostalCode INT ,UserAddressID INT FOREIGN KEY REFERENCES UserAddress(AddresID) ) GO -- New View CREATE VIEW v_Address AS SELECT ID ,City ,PostalCode ,UserAddressID FROM dbo.Address GO CREATE PROCEDURE sp_GetUserCity AS BEGIN SELECT UserAddress.FirstName ,UserAddress.Lastname ,Address.City FROM UserAddress INNER JOIN Address ON UserAddress.AddresID = Address.UserAddressID END GO -- New Trigger CREATE TRIGGER trgAfterInsert ON [dbo].[UserAddress] FOR INSERT AS PRINT 'Data entered successfully' GO |
Let’s run these scripts above to create the test objects then execute the following SQL.
|
1 |
EXECUTE sp_depends 'UserAddress' |
The following result will be:
| name | type | |
| 1 | dbo.sp_GetUserAddress | stored procedure |
| 2 | dbo.sp_GetUserCity | stored procedure |
- name – name of dependent object
- type – type of dependent object (e.g. table)
If a stored procedure is specified as an argument value in sp_depends, then a name of the table and the column names on which the procedure depends will be shown.
Let’s see how this looks with sp_GetUserAddress
|
1 |
EXECUTE sp_depends 'sp_GetUserAddress' |
The following result will be:
| name | type | updated | selected | column | |
| 1 | dbo.UserAddress | user table | no | yes | FirstName |
| 2 | dbo.UserAddress | user table | no | yes | LastName |
| 3 | dbo.UserAddress | user table | no | yes | Addresss |
- name – name of dependent object
- type – type of dependet object (e.g. table)
- updated – whether the object is updated or not
- selected – object is used in the SELECT statement
- column – column on which the dependency exists
sp_depends does not display triggers.
To illustrate this, execute the following code in the query window:
|
1 2 3 4 5 |
CREATE TRIGGER trgAfterInsert ON [dbo].[UserAddress] FOR INSERT AS PRINT 'Data entered successfully' GO |
Now execute the sp_depends over the UserAddress table, the trgAfterInsert will not appear in the Results table:
| name | type | |
| 1 | dbo.sp_GetUserAddress | stored procedure |
| 2 | dbo.sp_GetUserCity | stored procedure |
sp_dependes in some case does not report dependencies correctly. Let’s look at the situation when an object (e.g. UserAddress) on which another object depends (e.g. sp_GetUserAddress) is deleted and recreated. When sp_dependes is executed using EXECUTE sp_depends ‘sp_GetUserAddress’ or EXECUTE sp_depends ‘UserAddress’ the following message will appear:
“Object does not reference any object, and no objects reference it.”
Sadly, sp_dependes is on the path to deprecation and will be removed from future versions of the SQL Server. But you can use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead.
sys.dm_sql_referencing_entities
This function returns all objects from the current database which depend on the object that is specified as an argument.
|
1 2 3 |
SELECT referencing_schema_name ,referencing_entity_name FROM sys.dm_sql_referencing_entities('dbo.UserAddress', 'Object') |
The result will be:
| referencing_schema_name | referencing_entity_name | |
| 1 | dbo | sp_GetUserAddress |
| 2 | dbo | sp_GetUserCity |
referencing_schema_name – schema of the referencing entity
referencing_entity_name – name of the referencing object
More information about result sets can be found on this link.
sys.dm_sql_referenced_entities
This system function returns all objects from the current database on which specified object depends on.
Enter the following code in the query window:
|
1 2 3 |
SELECT referenced_entity_name ,referenced_minor_name FROM sys.dm_sql_referenced_entities('dbo.sp_GetUserAddress', 'Object') |
The following result will be shown:
| referenced_entity_name | referenced_minor_name | |
| 1 | UserAddress | NULL |
| 2 | UserAddress | FirstName |
| 3 | UserAddress | Lastname |
| 4 | UserAddress | Address |
referenced_entity_name – Name of the referenced object
referenced_minor_name – Name of the column of the referenced entity
For detailed information about result sets, please visit page on this link.
Referencing vs referenced
The objects that are appears inside the SQL expression are called the referenced entity and the objects which contain expressions are called referencing entity:
When using these two function the schema name (e.g. dbo) must be specified as part of the object name:
|
1 2 3 |
SELECT referencing_schema_name ,referencing_entity_name FROM sys.dm_sql_referencing_entities('dbo.UserAddress', 'Object') |
Otherwise no results will be displayed. Run the query without shema nema (dbo):
|
1 2 3 |
SELECT referencing_schema_name ,referencing_entity_name FROM sys.dm_sql_referencing_entities('UserAddress', 'Object') |
The result will be empty set:
| referencing_schema_name | referencing_entity_name | |
An empty result set will be shown under these situations:
- When is an invalid parameter passed (e.g. ‘dbo.UserAddress’,’NN’ instead dbo.UserAddress’,’Object’)
- When a system object is specified as argument (e.g. sys.all_columns)
- When the specified object does not reference any objects
- The specified object does not exist in the current database
The message 2020
Typically, the message 2020 occurs when a referencing object e.g. procedure, calls a referenced object e.g. table or a column from the table that does not exist. For example, if in the Address table change name of the column City to name Town and execute the SELECT * FROM sys.dm_sql_referenced_entities (‘[dbo].[v_Address]’,’Object’) query, the message 2020 will appear.
Execute the following code:
|
1 2 3 4 5 6 |
EXEC sys.sp_rename 'dbo.Address.City' ,'Town' ,'COLUMN' SELECT * FROM sys.dm_sql_referenced_entities('dbo.v_Address', 'OBJECT') |
The following message will appear:
Msg 207, Level 16, State 1, Procedure v_Address, Line 6
Invalid column name ‘City’.
Msg 2020, Level 16, State 1, Line 3
The dependencies reported for entity “dbo.v_Address” might not include references to all columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity. Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.
Troubleshooting
In order to prevent dropping or modifying objects, which depends on another object, the v_Address view should be altered and added the WITH SCHEMABINDING option:
|
1 2 3 4 5 6 7 8 |
ALTER VIEW v_Address WITH SCHEMABINDING AS SELECT ID ,City ,PostalCode ,UserAddressID FROM dbo.Address |
Now, when changing the name of the column in the Address table, the following message will appear, which proactively provides information that the object, the table “City” in this example, is a part of another object.
Code:
|
1 2 3 |
EXEC sys.sp_rename 'dbo.Address.City' ,'Town' ,'COLUMN' |
Message:
Msg 15336, Level 16, State 1, Procedure sp_rename, Line 501
Object ‘dbo.Address.City’ cannot be renamed because the object participates in enforced dependencies.
Schema-bound vs Non-schema-bound
There are two types of dependencies: Schema-bound and Non-schema-bound dependencies.
A Schema-bound dependency (SCHEMABINDING) prevents referenced objects from being altered or dropped as long as the referencing object exists
A Non-schema-bound dependency: does not prevent the referenced object from being altered or dropped.
For sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities dependency information will not be displayed for temporary tables, temporary stored procedures or system objects.
Below is an example of a temporary procedure:
|
1 2 3 4 5 6 7 8 9 |
CREATE PROCEDURE #sp_tempData AS BEGIN SELECT AddresID ,FirstName ,Lastname ,Address FROM UserAddress END |
Now, when executing sys.dm_sql_referencing_entities for the table UserAddress the information about the #sp_tempData procedure that depends on the UserAddress will not be shown in the list.
Code:
|
1 2 3 |
SELECT referencing_schema_name ,referencing_entity_name FROM sys.dm_sql_referencing_entities('dbo.UserAddress', 'Object') |
Result:
| referencing_schema_name | referencing_entity_name | |
| 1 | dbo | sp_GetUserAddress |
| 2 | dbo | sp_GetUserCity |
Viewing Dependencies
Another way to view dependencies between objects, but to create a visual SQL dependency tracker, is by using the View Dependencies option from SSMS. From the Object Explorer pane, right click on the object and from the context menu, select the View Dependencies option:
This will open the Object Dependencies window. By default, the Object that depend on radio button is selected. This radio button will list in the Dependencies section all objects that depends on the selected object (e.g. Address):
If selected the Object on which radio button, will display in the Dependencies section all objects on which selected object (e.g. Address) depends:
The Selected object section consists of three fields:
- Name – name of the selected object from the Dependencies list
- Type – type of the selected object (e.g.table)
- Dependency type – dependency between two objects (Schema-bound, Non-schema-bound).
Under the Type field the Unresolved Entity type for the object can be appear. This happens when the objects refer to an object that don’t exist in the database. This is equivalent to the Msg 2020 message that appears when using sys.dm_sql_referencing_entities or sys.dm_sql_referenced_entities functions:
This SQL dependency tracker is kind of a poor man’s SQL dependency diagram, in that it doesn’t show cross relationships or offer many value added features, but it will give you a quick preview of the dependences in the hierarchy that contains a particular object
Alternatives
ApexSQL Analyze is a 3rd party tool that can analyzes graphical SQL Server database object dependencies and the impact of potential deletions on your SQL database and create a SQL dependency diagram. It determines object interrelationships within the database, and allows customization of the resulting SQL dependency diagram appearance.
The tool can be downloaded from SQL tools downloads.
To see object dependencies, on the Home tab, click the New button, under the Connection to SQL Server window, choose the SQL Server instance, pick the type of authentication, and after selecting a desired database in the Database drop-down box, click the Connect button:
The Dependency viewer window will appear:
with the Dependencies pane, which shows all objects that depends on the selected object (e.g. UserAddress), by default this pane appears on the right side of the Dependency viewer window:
Dependency viewer provides graphical view of all dependencies between objects in the middle of the Dependency viewer window:
Visual dependencies
The Dependency viewer offers various options for filtering, appearance and manipulating objects.
In the Object browser pane, the object types (e.g. view) that will be displayed in the dependency graph can be specified:
Also, in the Object browser pane, specific objects can be selected that will be shown or omitted from the dependency graph:
The Dependencies pane, the complete dependency chain for the selected object in the dependency graph (e.g. Address) can be shown. Referencing indicate the object that depend on the selected object (aka referencing) and Referenced by shows the objects from which selected object depends on (aka referenced):
Also, the dependency chain can be reviewed by selecting an object in the dependency graph (e.g. Address), right click and from the context menu under the Select sub-menu, choose Referencing objects or Referenced objects command:
The Layout option under the Display ribbon offers different options for visual organization and display:
For example, the Orthogonal option attempts to organize objects in the diagrams so that they are at right angles to each other. It is useful for quick identification of all objects related to a given object (i.e. both those that depend on it and those it depends on):
Using this option, it can easily be determined how many objects depend on a specific object and a determination can be made as to whether it is safe to delete it without breaking relationships
The Show columns option shows columns with datatypes of the tables and views objects:
Additionally, the definition of an object can be easily reviewed by selecting the desired object (e.g. Address table), right click and from the context menu, select the Show script command:
The script window will appear with definition of the selected object:
In this article we discussed SQL Server object interdependencies, how to find them using SQL, SSMS and a 3rd party solution, and finally how to create a SQL dependency diagram from the results. Happy diagramming!
He is a prolific author of authoritative content related to SQL Server including a number of “platinum” articles (top 1% in terms of popularity and engagement). His writing covers a range of topics on MySQL and SQL Server including remote/linked servers, import/export, LocalDB, SSMS, and more.
In his part-time, Zivko likes basketball, foosball (table-soccer), and rock music.
See more about Marko at LinkedIn
View all posts by Marko Zivkovic
- How to connect to a remote MySQL server using SSL on Ubuntu - April 28, 2020
- How to install MySQL on Ubuntu - March 10, 2020
- Using SSH keys to connect to a remote MySQL Server - November 28, 2019