Finding dependencies of a table in sql server

Many a times we face the problem of finding the foreign key relationships, triggers and stored procedures that are referring to a particular table. The below query will help you in identifying the following things:

Find the entire hierarchy of all the parent tables and the corresponding parent table column names and their respective child(s).

Find the entire hierarchy of all the child tables and the corresponding child table column names and their respective parent(s).

The name of the triggers the table is referring to.

The name of the stored proc which uses the table.

Let me elaborate this with the help of an example to have better understanding.

If I will execute the same stored proc with Child2 as its parameter then in that case first table will be blank and second table will have the following data.From above result we can see that the table Child1 (column child1_col1) is acting as parent to Child2 (column Child2_col2).

After executing this query, we can see that the table ‘Child1’ is acting as child for table ‘parent’ with column ‘Child1_col2’ referring to parent column ‘parent_col1’.
Now lets say that I have created a simple stored proc as below: From result of above procedure will show that the Child 2 is acting as a child to table Child1 which is further acting as a child to table Parent giving us the entire hierarchy along with the column names.

CREATE Proc Demo
AS
BEGIN
SELECT * from Child1
END

If I will execute the same stored proc again with parameter as Child1 then apart from the information received above I will also get the below Information:

StoredProcedure_Name

Demo

This tells us that Child1 is getting used in stored Procedure named Demo. Similarly if a trigger is there on the same table then we can get the Trigger name as well.

Thanks for showing your interest. If you like this article then please don’t forget to share the same on social media. Your comments/feedback is most welcome.