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
(
Child2_col1 int primary key,
Child2_col2 int Foreign key references Child1(Child1_col1)
)
GO

After that I have created the following stored procedure:

CREATE proc GetTableDetails @tablename varchar(200)
AS
BEGIN
-- Get all child table names for given table name

;with cte2 as
(
select fk.name as ConstraintName
,fk.object_id
,o.name as Child_table_name
,c.name as parent_table_column_name
,t.name as Parent_Table_Name
,c1.name as child_column_name
,1 as level
from sys.foreign_keys fk
join sys.objects o on o.object_id=fk.parent_object_id
join sys.tables t on t.object_id=fk.referenced_object_id
and t.name =@tablename--'isos'
join sys.foreign_key_columns fkc on fkc.referenced_object_id=t.object_id and fkc.parent_object_id=o.object_id
join sys.columns c on c.object_id=o.object_id and c.column_id=fkc.parent_column_id
join sys.objects o1 on fkc.referenced_object_id=o1.object_id
join sys.columns c1 on c1.object_id=o1.object_id
and c1.column_id=fkc.referenced_column_id

union all

select fk.name as ConstraintName
,fk.object_id
,o.name as Child_table_name
,c.name as Child_table_column_name
,t.name as Parent_Table_Name
,c1.name as Parent_column_name
,cte2.level+1 as level
from sys.foreign_keys fk
join sys.objects o on o.object_id=fk.parent_object_id
join sys.tables t on t.object_id=fk.referenced_object_id
join cte2 on t.name=cte2.child_table_name
join sys.foreign_key_columns fkc on fkc.referenced_object_id=t.object_id and fkc.parent_object_id=o.object_id
join sys.columns c on c.object_id=o.object_id and c.column_id=fkc.parent_column_id
join sys.objects o1 on fkc.referenced_object_id=o1.object_id
join sys.columns c1 on c1.object_id=o1.object_id
and c1.column_id=fkc.referenced_column_id
where cte2.level<7
)
select * from cte2 order by level
-- get all parent table names for given table name

;with cte1 as

(
select fk.name as ConstraintName
,fk.object_id
--,fk.parent_object_id

--,fk.referenced_object_id
,o.name as parent_table_name
,c.name as parent_table_column_name
,t.name as Child_Table_Name
,c1.name as child_column_name
,1 as level
from sys.foreign_keys fk
join sys.objects o on o.object_id=fk.referenced_object_id
join sys.tables t on t.object_id=fk.parent_object_id
and t.name =@tablename--'child3'
join sys.foreign_key_columns fkc on fkc.referenced_object_id=o.object_id and fkc.parent_object_id=t.object_id
join sys.columns c on c.object_id=o.object_id and c.column_id=fkc.referenced_column_id
join sys.objects o1 on fkc.parent_object_id=o1.object_id
join sys.columns c1 on c1.object_id=o1.object_id
and c1.column_id=fkc.parent_column_id

union all

select fk.name
,fk.object_id
,o.name as parent_name
,c.name as parent_column_name
,t.name as Child_Table_Name
,c1.name as child_column_name
,cte1.level+1 as level
from sys.foreign_keys fk
join sys.objects o on o.object_id=fk.referenced_object_id
join sys.tables t on t.object_id=fk.parent_object_id
join cte1 on cte1.parent_table_name=t.name
join sys.foreign_key_columns fkc on fkc.referenced_object_id=o.object_id and fkc.parent_object_id=t.object_id
join sys.columns c on c.object_id=o.object_id and c.column_id=fkc.referenced_column_id
join sys.objects o1 on fkc.parent_object_id=o1.object_id
join sys.columns c1 on c1.object_id=o1.object_id and c1.column_id=fkc.parent_column_id
where cte1.level<7 -- to limit the recursion in case of self referencing tables
)

Select distinct * from cte1 order by level
-- GET Triggers/Stored proc names
select t.name as TRIGGER_NAME--,t.type_desc
from sys.triggers t
inner join sys.objects o
ON o.name=@tablename
and o.object_id=t.parent_id

SELECT distinct p.name AS StoredProcedure_Name--, p.type_desc
FROM sys.procedures p
inner join sys.sql_dependencies d
ON d.object_id=p.object_id
inner join sys.objects o
on o.object_id=d.referenced_major_id
WHERE o.name=@tablename
END;

Now I will execute the procedure with following parameters:

1) Child1 as parameter

exec GetTableDetails 'Child1'

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:

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.