View the Dependencies of a Table in SQL Server 2012

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.

Table: MajorCategory

Table: MinorCategory

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:

View-Dependencies-on-View-or-Stored Procedure-in-SQL-Server.jpg

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%'

Output

2 Comments

Join the conversation! Your thoughts help the community grow.

Rohatash Kumar13y ago
Thanks Kevin.
Kevin N13y ago
Great article, but it is important to understand that when working on large databases that it will take a large amount of time to determine all dependencies. There are some great products out there including a new product called SQL Negotiator Pro available at www.aphilen.com this will graphically generate the dependencies of any table and stored procedure