Finding Out Object Dependencies in SQL Server

Lets see how to find the object Dependencies in SQL Server using SSMS.
We have a simple inbuilt option in SSMS to view the object dependencies in SQL Server.
Before that, Why we need to find out the object dependencies in SQL Server.

Suppose we have a table called Table A and if we are going to delete some rows
out of that table, then we may be having the foreign key constraint associated with Child Tables.
It will throw you an error related to foreign key constraint and you cant be able to proceed further.

For Example I am using AdventureWorks database and I am trying to delete the data from the Person Table.

DELETE FROM [AdventureWorks2008R2].[Person].[Person]

You will be getting the following error from the Sql Server

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_EmailAddress_Person_BusinessEntityID". 
The conflict occurred in database "AdventureWorks2008R2", table "Person.EmailAddress", column 'BusinessEntityID'.
The statement has been terminated.

Now from this message it is clear that there is foreign key reference with table Person.EmailAddress.

So you can’t be able to delete data from Person.Person without deleting the data from Person.EmailAddress
But now we are not sure that how many objects [tables/views] has a dependency on the table Person.Person

So to find out this, we have simple provision in Sql Server.

Open the Object Explorer in SSMS.
Go to the Database that you want and select the Table you want to view dependencies.
Right Click on that Table [Person].[Person] and Select “View Dependencies”

Now this will open a window which will show you the Objects that depends on the table Person.Person.

As you see the Tables BusinessEntityContact, Email Address, Employee and IUPerson Trigger and some other Views and Tables depends on the object “Person”.

So you can’t be able to delete the rows in Person without Deleting the data in the dependent Objects.
Now Select the Option, “Objects on which [Person] Depends”

This will list out the Objects that “Person” depends on.

Advertisement