sp_depends in SQL Server

Suggested Videos
Part 130 - Offset fetch next in SQL Server 2012
Part 131 - Identifying object dependencies in SQL Server
Part 132 - sys.dm_sql_referencing_entities in SQL Server



In this video we will discuss sp_depends system stored procedure.



There are several ways to find object dependencies in SQL Server

1. View Dependencies feature in SQL Server Management Studio - Part 131
2. SQL Server dynamic management functions - Part 132
     sys.dm_sql_referencing_entities
     sys.dm_sql_referenced_entities
3. sp_depends system stored procedure - This video

sp_depends
A system stored procedure that returns object dependencies
For example,

• If you specify a table name as the argument, then the views and procedures that depend on the specified table are displayed
• If you specify a view or a procedure name as the argument, then the tables and views on which the specified view or procedure depends are displayed.

Syntax :Execute sp_depends 'ObjectName'

The following SQL Script creates a table and a stored procedure

Create table Employees

(

    Id int primary key identity,

    Name nvarchar(50),

    Gender nvarchar(10)

)

Go

Create procedure sp_GetEmployees

as

Begin

    Select * from Employees

End

Go

Returns the stored procedure that depends on table Employees

sp_depends 'Employees'

Ouptut :


Returns the name of the table and the respective column names on which the stored procedure sp_GetEmployees depends

sp_depends 'sp_GetEmployees'

Output : 

 
Sometime sp_depends does not report dependencies correctly. For example, at the moment we have Employees table and a stored procedure sp_GetEmployees. 

Now drop the table Employees

Drop table Employees

and then recreate the table again

Create table Employees

(

    Id int primary key identity,

    Name nvarchar(50),

    Gender nvarchar(10)

)

Go

Now execute the following, to find the objects that depend on Employees table

sp_depends 'Employees'

We know that stored procedure sp_GetEmployees still depends on Employees table. But sp_depends does not report this dependency, as the Employees table is dropped and recreated.
Object does not reference any object, and no objects reference it.

sp_depends is on the deprecation path. This might be removed from the future versions of SQL server.