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
sp_depends
A system stored procedure that returns object dependencies
For example,
The following SQL Script creates a table and a stored procedure
Returns the stored procedure that depends on table Employees
Ouptut :
![sp_depends in sql server](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOgnOVpxpnP9o8LgirAudF1OFro7KgSR1QONKgzZg8deMEOGZ11XQQL4n1lhptP9fLXmToMSJEwFeqGb0yO6V-HwF-p0UJbQe1Oo4tAvPajCgxSwO0Xppgavrl9-VtodBaK223YhPHyAzv/s1600/sp_depends+in+sql+server.png)
Returns the name of the table and the respective column names on which the stored procedure sp_GetEmployees depends
Output :
![sql server sp depends stored procedure](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgFlFASjtXESlTSC00EBhhEOQ3PSouLFkxgyp3jTwbVGE_NEisu0UxljMEUyg03QglsAZrvIIXE1vfVIz13csEJ7t99VNxkvcpIieTXsuyH72jv-BlMvr4-PGuJEqfQi1T-LtZyWAZwwsaA/s1600/sql+server+sp+depends+stored+procedure.png)
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
Now execute the following, to find the objects that depend on Employees table
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.
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
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.
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
sp_depends 'Employees'
Ouptut :
![sp_depends in sql server](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOgnOVpxpnP9o8LgirAudF1OFro7KgSR1QONKgzZg8deMEOGZ11XQQL4n1lhptP9fLXmToMSJEwFeqGb0yO6V-HwF-p0UJbQe1Oo4tAvPajCgxSwO0Xppgavrl9-VtodBaK223YhPHyAzv/s1600/sp_depends+in+sql+server.png)
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](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgFlFASjtXESlTSC00EBhhEOQ3PSouLFkxgyp3jTwbVGE_NEisu0UxljMEUyg03QglsAZrvIIXE1vfVIz13csEJ7t99VNxkvcpIieTXsuyH72jv-BlMvr4-PGuJEqfQi1T-LtZyWAZwwsaA/s1600/sql+server+sp+depends+stored+procedure.png)
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)
)
GoNow 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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCgUXVtzkqGbIyKr6tjLAA10w43LqpENiYCOndEUbgLU00GzdJF6rG_QjH-eAiL0asdM8O_h6T6UIWYMhMV0Vl7G5ONW9nOUWqTDmI_esaGYvcMMe_QbH2lFjkv3HCI_buweSMNrU3zm54/s1600/sql+server+tutorial.png)
No comments:
Post a Comment
It would be great if you can help share these free resources