RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Determine Object Dependencies Before Changing Your SQL Server Schema Design

Maintaining a database often means schema changes. Before you change or delete anything, be sure to check for dependent objects.

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.

Click to enlarge

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.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date