sp depends in SQL Server

sql server sp depends stored procedure

In this video we will discuss sp_depends system stored procedure.

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

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’

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

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.

Text version of the video


All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

Get Paid Taking Pictures