sp depends in SQL Server
sql server sp depends stored procedure
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’
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)
)
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.
Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/10/spdepends-in-sql-server.html
Slides
http://csharp-video-tutorials.blogspot.com/2015/10/spdepends-in-sql-server_25.html
All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html
All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html
All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd
All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists
Social