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.
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