{"id":84,"date":"2014-10-01T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/query-object-dependencies"},"modified":"2026-03-18T21:51:16","modified_gmt":"2026-03-18T21:51:16","slug":"query-object-dependencies","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2014\/10\/01\/query-object-dependencies\/","title":{"rendered":"Query Object Dependencies"},"content":{"rendered":"

SQL Server Management Studio has a decent interface for viewing object dependencies, check out TechNet <\/a>.aspx)for those procedures. But, we all know the value of being able to run queries for the information that we need. There are two methods that we\u2019re going to cover for finding object dependencies. SQL Server 2005 is still\u00a0in extended support<\/a>, so first we will cover\u00a0the deprecated method, sp_depends<\/a>. Then we\u2019ll discuss sys.dm_sql_referencing_entities<\/a> and sys.dm_sql_referenced_entities<\/a> which are the now preferred functions to use and available in SQL Server 2008 and above.<\/p>\n

sp_depends<\/h2>\n

sp_depends has fairly straight forward syntax. Simply pass in the object name when connected under the context of the database that you are working in to get a list of the dependencies.<\/p>\n

USE AdventureWorks2012;\nexec sp_depends 'Person.Person';<\/code><\/pre>\n
\"\"<\/a><\/p>\n
Dynamic Management Functions<\/h2>\n
When using our DMFs we will first need to understand the terminology in their names. A\u00a0referenced\u00a0entity<\/em>\u00a0is an entity which at least one other entity depends upon. For example, if you have a user-defined function and a stored procedure which uses the UDF, the UDF is the referenced entity. In that case, the stored procedure would be the referencing entity<\/em>. Referencing entities depend upon the existence of the referenced entity.<\/p>\n
sys.dm_sql_referencing_entities<\/a>\u00a0is the DMF whose result set closely matches that of\u00a0sp_depends<\/a>. To use this DMF you will need to call it in a SELECT statement passing in the referenced entity name and its type.<\/p>\n
USE AdventureWorks2012;\nGO\nSELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent\nFROM sys.dm_sql_referencing_entities ('Production.Product', 'OBJECT');\nGO<\/code><\/pre>\n
\"\"<\/a><\/p>\n
Similar to the referencing entities, the\u00a0sys.dm_sql_referenced_entities<\/a>\u00a0DMF accepts an entity and object type. The\u00a0only difference is that this entity\u2019s\u00a0input\u00a0parameter accepts the referencing object and not the referenced.<\/p>\n
USE AdventureWorks2012;\nGO\nSELECT referenced_schema_name, referenced_entity_name, referenced_minor_name, \n    referenced_minor_id, referenced_class_desc\nFROM sys.dm_sql_referenced_entities ('ddlDatabaseTriggerLog', 'DATABASE_DDL_TRIGGER');\nGO<\/code><\/pre>\n
\n
\"\"<\/strong>Written by Derik Hammer of SQL Hammer<\/a><\/p>\n
Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability<\/a>, disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\n
Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n
Derik is the owner and lead author of SQL Hammer, a Microsoft SQL Server resource<\/a>.<\/p>\n
For more information, visit http:\/\/www.sqlhammer.com<\/a>. Follow Derik on Twitter<\/a> for <\/strong>SQL tips and chat**<\/p>\n","protected":false},"excerpt":{"rendered":"
SQL Server Management Studio has a decent interface for viewing object dependencies, check out TechNet .aspx)for those procedures. But, we all know the value of being able to run queries for the information that we need. There are two methods that we\u2019re going to cover for finding object dependencies. SQL Server 2005 is still\u00a0in extended […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-84","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/84","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=84"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/84\/revisions"}],"predecessor-version":[{"id":347,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/84\/revisions\/347"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=84"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=84"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=84"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}