Identifying object dependencies in SQL Server

sql server object dependencies
ms sql server object dependencies
sql server object dependency tree
sql server management studio object dependencies

In this video we will discuss how to identify object dependencies in SQL Server using SQL Server Management Studio.

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.

The following SQL Script creates 2 tables, 2 stored procedures and a view

Create table Departments
(
Id int primary key identity,
Name nvarchar(50)
)
Go

Create table Employees
(
Id int primary key identity,
Name nvarchar(50),
Gender nvarchar(10),
DeptId int foreign key references Departments(Id)
)
Go

Create procedure sp_GetEmployees
as
Begin
Select * from Employees
End
Go

Create procedure sp_GetEmployeesandDepartments
as
Begin
Select Employees.Name as EmployeeName, Departments.Name as DepartmentName
from Employees
join Departments
on Employees.DeptId = Departments.Id
End
Go

Create view VwDepartments
as
Select * from Departments
Go

How to find dependencies using SQL Server Management Studio
Use View Dependencies option in SQL Server Management studio to find the object dependencies

For example : To find the dependencies on the Employees table, right click on it and select View Dependencies from the context menu

In the Object Dependencies window, depending on the radio button you select, you can find the objects that depend on Employees table and the objects on which Employees table depends on.

Identifying object dependencies is important especially when you intend to modify or delete an object upon which other objects depend. Otherwise you may risk breaking the functionality.

For example, there are 2 stored procedures (sp_GetEmployees and sp_GetEmployeesandDepartments) that depend on the Employees table. If we are not aware of these dependencies and if we delete the Employees table, both stored procedures will fail with the following error.

Msg 208, Level 16, State 1, Procedure sp_GetEmployees, Line 4
Invalid object name 'Employees'.

There are other ways for finding object dependencies in SQL Server which we will discuss in our upcoming videos.

Link for all dot net and sql server video tutorial playlists

Link for slides, code samples and text version of the video

[bhuwanmaharjan]

Thank you Venket sir. You were saving my professional life since long. thx again. from NZ.

[daminikumari]

Thanku sir. U are really master of sql server. Best regards from India.

[krzysztofs]

Thank U Venkat for this tutorial. You are master of SQL Server ! Thank U for educating community. You have to spent a lot of time on it ! God bless U. Best regards from Poland. I watched all your videos and tutorials are superb!

[stevenaleman]

this was very helpful....thank you for sharing the knowledge and explaining it in a very simple/meaningful way.

[soniasadeque]

thanks a lot Venkat..you are a great person..

[sarvarbeksoporboev]

In the object dependencies video, the SQL throws an error if the object is requiring a deleted object.
Could you tell us how we can write a script that says the following?

If no dependencies, delete,
otherwise, warn the user that the intended delete object is important for running other objects.

Thanks