hange is never simple. Even changing one object just a little bit can impact users in a big way. For instance, you might think deleting a legacy table is efficient, but only until a view or report no longer works. By the time the error occurs, you might not remember that you deleted a table two months earlier. Your users will complain and you'll spend unnecessary time troubleshooting the problem. That's why you should always run a thorough impact assessment before
implementing change, even a small change. The good news is that listing object dependencies isn't as hard as it might sound. You can use SQL Server's management tools or execute one or more code snippets.
Management Studio Displays a Comprehensive Dependency Chain
Viewing object dependencies in SQL Server Management Studio is easy, and it offers the most comprehensive list of dependencies, including dependent tables, views, stored procedures, functions, and so on. Simply right-click the object and choose View Dependencies from the resulting submenu. For instance, to see a list of all objects that are dependent on the Employee table (in AdventureWorks, the demo database that you can download), right-click the table and choose View Dependencies. (It might take a minute or two for Management Studio to build the list.)
Figure 1 shows objects that are dependent on the Employee table. The icons indicate object type. To follow the chain of dependency, click the plus sign (+), which can include views, stored procedures, and so on. As you can see, the expanded chain for the SalesPerson table is several objects deep. Changing or deleting Employee could have far-reaching consequences.
|Figure 1. Objects That Are Dependent on the Employee Table: Management Studio provides the only built-in method for viewing the entire chain of dependent objects.|
To see the objects on which Employee depends, click "Objects On Which [Employee] Depends" at the top of the window. Management Studio will let you view dependencies on tables, views, stored procedures, and user-defined functions.