Reader Level:
ARTICLE

View the Dependencies of a Table in SQL Server 2012

Posted by Rohatash Kumar Articles | SQL Server October 30, 2012
Here, we will see use of Object Explorer to identify dependencies before changing or deleting an object or table.
  • 0
  • 0
  • 5746

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 ServerSo 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-in-SQL-Server.jpg

Table: MinorCategory

Table1-in-SQL-Server.jpg

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.

View-Dependencies-in-SQL-Server.jpg

Now click on the View Dependencies. The Object Dependency window will be opened.

View-Dependencies-window-in-SQL-Server.jpg

It contains two options:

  1. Objects that depend on <object name>

  2. 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-Dependencies-on-Table-in-SQL-Server.jpg

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

View-Dependencies-Programatically-in-SQL-Server.jpg

COMMENT USING

Trending up