Friday, May 22, 2009

Determine SQL Server Object Dependencies with sp_Depends and sp_ MSdependencies

The sp_Depends system stored procedure displays information about object dependencies for a table, view, stored procedure, user defined function, or trigger using the information stored in a system table named sysdepends.

Syntax:
EXEC sp_depends @objname = N'dbo.table1';

This will return both the name and type of the objects dependent on 'table1'. However sp_depends doesn't list tables, but does list check constraints.


The sp_MSdependencies can also be used to find dependent objects. In here, depending on the specified parameters the results will be varied. For example;

Syntax: EXEC sp_MSdependencies N'dbo.table1', null, 1315327



Syntax:EXEC sp_MSdependencies N'dbo.table1', null, 1053183



Syntax: EXEC sp_MSdependencies N'dbo.table2', null, 1315327



Syntax:EXEC sp_MSdependencies N'dbo.table2', null, 1053183



Syntax: EXEC sp_MSdependencies N'dbo.table1'



Syntax: EXEC sp_MSdependencies N'dbo.table2'



Syntax: EXEC sp_MSdependencies N'dbo.table3'