Here, we will see use of Object Explorer to identify dependencies before changing or deleting an object. One object may be depend on another; in such type of a situation, you often face a problem whenever you change or delete an object, it shows an error. There are several methods of getting the dependencies of an object in SQL Server. So let's take a look at a practical example of how to identify the dependencies in SQL Server 2012 using the Management Studio.
Creating two table in SQL Server
First we create two tables in SQL. One contains the primary key and another table contains the reference key. For example a view or stored procedure relies upon an underlying table to provide the data.
View the Dependencies in SQL Server (Visually)
Now Press F8 to open the Object Browser in SQL Server Management Studio and expand it.
Database ->Table -> right-click -> select View Dependencies.
Now click on the View Dependencies. The Object Dependency window will be opened.
It contains two options:
Objects that depend on <object name>
Objects on which <object name> depends
In the Object Dependencies <object name> dialog box, select Objects that depend on <object name> or it is the default option. This option shows the type of object (such as "Trigger" or "Stored Procedure") which relies upon an underlying table. That means these are the dependents of the table MinorCategory:
Now select Objects on which <object name> depends. This is the other objects that the object depends on.
View the Dependencies in SQL Server (Programmatically)
We can View the Dependencies using the following query:
SELECT routine_name, routine_type FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%MinorCategory%'