Daily Archives: Nov 16, 2014


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:

  1. Find the entire hierarchy of all the parent tables and the corresponding parent table column names and their respective child(s).
  2. Find the entire hierarchy of all the child tables and the corresponding child table column names and their respective parent(s).
  3. The name of the triggers the table is referring to.
  4. The name of the stored proc which uses the table.

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

Let’s say I have created 3 tables as follows:

CREATE TABLE parent
(
parent_col1 int primary key,
parent_col2 int
)

GO
CREATE TABLE Child1
(
Child1_col1 int primary key,
Child1_col2 int Foreign key references parent(parent_col1)
)
GO

CREATE TABLE Child2
(
More