What is sp_depends in SQL Server?
sp_depends is a system stored procedure that displays information about all object types (e.g. procedures, tables, etc).
There are several ways to find object dependencies in SQL Server
1. View Dependencies feature in SQL Server Management Studio
2. SQL Server dynamic management functions
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
Stored procedure :
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.
