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.
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
