HI WELCOME TO Sirees

Identifying object dependencies in SQL Server

Leave a Comment

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

Identifying object dependencies in SQL Server

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.

sql server object dependencies

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 .

0 comments:

Post a Comment

Note: only a member of this blog may post a comment.