Identifying object dependencies in SQL Server

Suggested Videos
Part 128 - DateTime2FromParts function in SQL Server 2012
Part 129 - Difference between DateTime and DateTime2 in SQL Server
Part 130 - Offset fetch next in SQL Server 2012



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



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.