Determine Object Dependencies Before Changing Your SQL Server Schema Design : Page 4

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

by Susan Sales Harkins

Nov 13, 2008

Page 4 of 4

A Quick Comparison of the Methods
For a quick comparison of all four supported methods, take a look at Table 1.

Table 1. Method Comparison for Displaying Dependent Object Types

Method	Type	Tables	Views	UDF	Stored Procedures	Triggers	CHECK Constraints
View Dependencies	X	X	X	X	X	X	X
syscomments query			X	X	X	X	X
schema view	X			X	X
sp_depends	X		X	X	X		X

The only truly comprehensive tool is the View Dependencies option in Management Studio. This method displays all dependent object types and the subsequent chain of dependency. All the other methods list some but not all, which can be useful when you don't want a complete list.

Avoid Errors When Changing Schema
To avoid unexpected problems, find objects that will be affected by schema changes before you implement the change. Fortunately, SQL Server makes the process easy. You can use the Management Studio, a query, a schema view, or one of two stored procedures. They all have their pros and cons, and this article should help you choose just the right method for your scenario.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies.

« Previous Page 1 2 34

0 Comments (click to add your comment)

Comment and Contribute