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 : Page 3

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

Execute a System Stored Procedure
SQL Server provides a stored procedure for listing dependencies, and its use is straightforward:

EXEC sp_depends @objname = N'objectname'

The sp_depends stored procedure displays information about object dependencies where objectname is a table, view, stored procedure, user-defined function, or trigger. This information is stored in a system table named sysdepends. As you can see in Figure 4, this statement returns both the name and type of the objects dependent on Employee. Like the query based on the syscomments table, sp_depends doesn't list tables. However, it does list check constraints.

Click to enlarge

Figure 4. Stored Procedure to Return Name and Object Type: Use a system stored procedure to return name and object type.

About an Undocumented Stored Procedure
SQL Server's undocumented sp_MSdependencies is another option. Although, you probably shouldn't use undocumented features in a programmatic solution because you can't depend on them. To find dependent objects, use the following syntax:

EXEC sp_MSdependencies N'objectname', null, 1315327

To display objects on which a particular object depends, use this syntax:

EXEC sp_MSdependencies N'objectname', null, 1053183

Compare the results in Figure 5 with those in previous figures.

Click to enlarge

Figure 5. Undocumented Function to Return Dependencies: This undocumented function displays both sides of the dependency.

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