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

Leave a Reply

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