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.
| 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.
| Figure 5. Undocumented Function to Return Dependencies: This undocumented function displays both sides of the dependency. |
Previous Page
