Shortcut: Dependency tracking in SQL Server Management Studio

In early versions of SQL Server, the only way to try to track dependencies between tables, procedures, functions, etc. was to use the sp_depends stored procedure. And everyone thought it lied. The real problem was that it didn’t understand partial dependencies and deferred resolution of objects. For example, it got confused if you created a procedure that mentioned a table, then later created the table.

SQL Server 2012 introduced far superior dependency views, and SQL Server Management Studio (SSMS) now shows dependencies using those views under the covers.

Here’s an example. If I right-click the Application.DeliveryMethods table in the WideWorldImporters database, I can choose to View Dependencies:

By default, you are shown the objects that depend upon the selected object (in this case the table that we right-clicked):

Note that this is a multi-layer dependency tree. We can see that the Customers table depends upon this table, as does the AddCustomers stored procedure. The Orders table also depends upon the DeliveryMethods table, and through that, the CustomerTransactions table, and from there, onto the GetTransactionUpdates procedure.

We can also see objects that the DeliveryMethods table depends upon:

In this case, we can see that the table depends upon its own primary key (ie: DeliveryMethodID), and on the People table because there is a foreign key to the PersonID column in that table, for the last person who modified the rows in the table.

We can also see dependencies for other types of objects. Here is the tree for the InsertCustomerOrders stored procedure:

It depends upon the OrderLines table, the OrderList and OrderLineList table types, and the CalculateCustomerPrice function.

I’m really pleased that such a good dependency system is available within SSMS.

2018-05-04