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.
3 thoughts on “Shortcut: Dependency tracking in SQL Server Management Studio”
Found this in the Database Weekly round-up from SQLServerCentral – nicely done! I use the dependency viewer a lot too, and recently dived just a bit into the underlying system views/functions that help make it work. @ https://natethedba.wordpress.com/finding-trigger-dependencies/ PS: Love the SDU podcast & tools! <3 =)
Thank you for writing about this. I was aware that this function existed in SSMS, but like others, kind of wrote it off as not working. It's nice to know it's been fixed to be a functional and useful feature.
You are most welcome