Find dependencies of a table in SQL Server

Many a times need arises to find all objects table depends on or is being referred. This blog will help you to find dependencies of a table in SQL Server.

This can be done using sys.sql_expression_dependencies system view and sys.dm_sql_referencing_entities system function. To get more details about these system objects refer to http://technet.microsoft.com/en-us/library/bb677315(v=sql.110).aspx and http://msdn.microsoft.com/en-us/library/bb630351.aspx

Let’s create a stored procedure referencing AdventureWorks.Person.Address table.

-- create a proc 
if object_id('usp_listaddress') is not null
drop proc usp_listaddress
GO
create proc usp_listaddress
AS
select * from Person.Address
GO

Let’s execute the below query and find out dependency information using sys.sql_expression_dependencies system view.

SELECT OBJECT_SCHEMA_NAME ( referencing_id ) + '.' + 
    OBJECT_NAME(referencing_id) AS referencing_object_name, 
    obj.type_desc AS referencing_object_type
    FROM sys.sql_expression_dependencies AS depends
INNER JOIN sys.objects AS obj ON depends.referencing_id = obj.object_id
WHERE referenced_id = OBJECT_ID(N'Person.Address')
Order by referencing_object_type

The referenced_id as the name suggest is the id of the object being referenced and the referencing_id is the referring object. A join with sys.objects gives the type of the referencing object. The output of the query is shown below

1_find dependencies of table in sql server

The other method to get same result is using the function sys.dm_sql_referencing_entities.  The object takes two arguments, the “schemaname.referenced_entity_name” the name of the referenced entity and the “referenced_class” which is the class of the referenced entity. The valid values for referenced_class are OBJECT, TYPE, XML_SCHEMA_COLLECTION and PARTITION_FUNCTION.  Execute the below query to get the dependency.

SELECT 
referencing_id, 
referencing_schema_name, 
referencing_entity_name,
obj.type_desc As referencing_entiry_type,
referencing_class_desc, 
is_caller_dependent
FROM sys.dm_sql_referencing_entities ('Person.Address', 'OBJECT') sre
join sys.objects obj on sre.referencing_id=obj.object_id
GO

The output of the above query is self-explanatory and is shown below.

2_find dependencies of table in sql server

 
Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *