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