sp_depends in SQL Server

Leave a Comment

 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
3. sp_depends system stored procedure - This video

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)

Create procedure sp_GetEmployees
    Select * from Employees

Returns the stored procedure that depends on table Employees
sp_depends 'Employees'

Ouptut :
sp_depends in sql server

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

Output : 
sql server sp depends stored procedure
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)

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.


Post a Comment

Note: only a member of this blog may post a comment.