{"id":6471,"date":"2019-10-24T19:18:35","date_gmt":"2019-10-25T02:18:35","guid":{"rendered":"https:\/\/database.guide\/?p=6471"},"modified":"2024-09-21T05:23:27","modified_gmt":"2024-09-20T19:23:27","slug":"find-referencing-entities-in-sql-server-sys-dm_sql_referencing_entities","status":"publish","type":"post","link":"https:\/\/database.guide\/find-referencing-entities-in-sql-server-sys-dm_sql_referencing_entities\/","title":{"rendered":"Find Referencing Entities in SQL Server: sys.dm_sql_referencing_entities()"},"content":{"rendered":"\n<p class=\"\">In <a href=\"https:\/\/database.guide\/what-is-sql-server\/\">SQL Server<\/a>, you can use the <code>sys.dm_sql_referencing_entities()<\/code> system dynamic management function to get a list of all entities in the current database that reference another user-defined entity by name.<\/p>\n\n\n\n<p class=\"\">In other words, it returns a list of entities that depend on the given entity.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p class=\"\">Specifically, it reports on the following entity types in the current database that reference the specified entity:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li class=\"\"><a href=\"https:\/\/database.guide\/what-does-schema-bound-mean-in-sql-server\/\" data-type=\"post\" data-id=\"40165\">Schema-bound<\/a> or non-schema-bound entities<\/li>\n\n\n\n<li class=\"\">Database-level DDL triggers<\/li>\n\n\n\n<li class=\"\">Server-level DDL triggers<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Syntax<\/h2>\n\n\n\n<p class=\"\">The syntax goes like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sys.dm_sql_referencing_entities (  \n    ' schema_name.referenced_entity_name ' , '  ' )  \n  \n ::=  \n{  \n    OBJECT  \n  | TYPE  \n  | XML_SCHEMA_COLLECTION  \n  | PARTITION_FUNCTION  \n}<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Example 1 &#8211; Basic Example<\/h2>\n\n\n\n<p class=\"\">Here&#8217;s a basic example of usage:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>USE WideWorldImporters;\nSELECT *\nFROM sys.dm_sql_referencing_entities (\n    'Application.Cities', \n    'OBJECT');<\/code><\/pre>\n\n\n\n<p class=\"\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+---------------------------+---------------------------+------------------+---------------------+--------------------------+-----------------------+<br>| referencing_schema_name   | referencing_entity_name   | referencing_id   | referencing_class   | referencing_class_desc   | is_caller_dependent   |<br>|---------------------------+---------------------------+------------------+---------------------+--------------------------+-----------------------|<br>| Application               | DetermineCustomerAccess   | 1051150790       | 1                   | OBJECT_OR_COLUMN         | 0                     |<br>| Integration               | GetCityUpdates            | 1758629308       | 1                   | OBJECT_OR_COLUMN         | 0                     |<br>| Website                   | Customers                 | 1694629080       | 1                   | OBJECT_OR_COLUMN         | 0                     |<br>| Website                   | SearchForCustomers        | 942626401        | 1                   | OBJECT_OR_COLUMN         | 0                     |<br>| Website                   | SearchForSuppliers        | 926626344        | 1                   | OBJECT_OR_COLUMN         | 0                     |<br>| Website                   | Suppliers                 | 1678629023       | 1                   | OBJECT_OR_COLUMN         | 0                     |<br>+---------------------------+---------------------------+------------------+---------------------+--------------------------+-----------------------+<\/pre>\n\n\n\n<p class=\"\">In this example, there are six entities that reference &#8216;Application.Cities&#8217; in the &#8216;WideWorldImporters&#8217; database.<\/p>\n\n\n\n<p class=\"\">Microsoft specifically <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-dynamic-management-views\/system-dynamic-management-views?view=sql-server-ver15\" target=\"_blank\" rel=\"noopener noreferrer\">recommends against<\/a> using the asterisk (<code>*<\/code>) to select all columns from dynamic management views and functions (of which <code>sys.dm_sql_referencing_entities()<\/code> is one). This is because their schemas and the data they return may change in future releases of SQL Server. This could result in columns being added to the end of the column list in future releases, which could mess up your application if you&#8217;re relying on the asterisk to select all columns.<\/p>\n\n\n\n<p class=\"\">Therefore, the previous code should be rewritten to this:<\/p>\n\n\n\n<p class=\"\">Example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>USE WideWorldImporters;\nSELECT \n  referencing_schema_name,\n  referencing_entity_name,\n  referencing_id,\n  referencing_class,\n  referencing_class_desc,\n  is_caller_dependent\nFROM sys.dm_sql_referencing_entities (\n    'Application.Cities', \n    'OBJECT');<\/code><\/pre>\n\n\n\n<p class=\"\">Of course, you can always specify less columns if you want.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Example 2 &#8211; Get the Referencing Entity Type<\/h2>\n\n\n\n<p class=\"\">The above example is all good and well, but it doesn&#8217;t tell us the referencing entity&#8217;s type. In other words, we can&#8217;t see whether it&#8217;s a <a href=\"https:\/\/database.guide\/what-is-a-view\/\">view<\/a>, a <a href=\"https:\/\/database.guide\/what-is-a-stored-procedure\/\">stored procedure<\/a>, etc.<\/p>\n\n\n\n<p class=\"\">You can get this information by joining <code>sys.dm_sql_referencing_entities()<\/code> with <code>sys.objects<\/code>.<\/p>\n\n\n\n<p class=\"\">So we could modify the previous example like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \n  o.type_desc 'Type',\n  re.referencing_schema_name 'Schema',\n  re.referencing_entity_name 'Name'\nFROM sys.dm_sql_referencing_entities (\n    'Application.Cities', \n    'OBJECT') re\nINNER JOIN sys.objects o\nON re.referencing_id = o.object_id\nORDER BY 'Type' ASC;<\/code><\/pre>\n\n\n\n<p class=\"\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+----------------------------------+-------------+-------------------------+<br>| Type                             | Schema      | Name                    |<br>|----------------------------------+-------------+-------------------------|<br>| SQL_INLINE_TABLE_VALUED_FUNCTION | Application | DetermineCustomerAccess |<br>| SQL_STORED_PROCEDURE             | Integration | GetCityUpdates          |<br>| SQL_STORED_PROCEDURE             | Website     | SearchForCustomers      |<br>| SQL_STORED_PROCEDURE             | Website     | SearchForSuppliers      |<br>| VIEW                             | Website     | Suppliers               |<br>| VIEW                             | Website     | Customers               |<br>+----------------------------------+-------------+-------------------------+<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Example 3 &#8211; User-Defined Types<\/h2>\n\n\n\n<p class=\"\">Here&#8217;s an example of using <code>sys.dm_sql_referencing_entities()<\/code> to return entities that reference a given user-defined alias type.<\/p>\n\n\n\n<p class=\"\">For this example, I <a href=\"https:\/\/database.guide\/how-to-create-a-user-defined-data-type-alias-in-sql-server-using-t-sql\/\">created a user-defined alias<\/a> called <code>clientcode<\/code>. I then used it in two columns (in two different tables), and I also created a stored procedure that references the type by name (it accepts an argument called <code>@ClientCode<\/code> which is of the <code>clientcode<\/code> type).<\/p>\n\n\n\n<p class=\"\">To return a user-defined type, use <code>TYPE<\/code> as the second argument.<\/p>\n\n\n\n<p class=\"\">Example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>USE Test;\nSELECT \n  referencing_entity_name\nFROM sys.dm_sql_referencing_entities (\n    'dbo.clientcode', \n    'TYPE');<\/code><\/pre>\n\n\n\n<p class=\"\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+---------------------------+<br>| referencing_entity_name   |<br>|---------------------------|<br>| uspGetClient              |<br>| uspGetOrdersByClient      |<br>+---------------------------+<\/pre>\n\n\n\n<p class=\"\">I can tell by the names that both of these are stored procedures (they&#8217;re both prefixed with <code>usp<\/code>, which is a common convention when creating user-defined stored procedures), but we can confirm this by checking the <code>sys.objects<\/code> system catalog view once again:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \n  o.type_desc 'Type',\n  re.referencing_schema_name 'Schema',\n  re.referencing_entity_name 'Name'\nFROM sys.dm_sql_referencing_entities (\n    'dbo.clientcode', \n    'TYPE') re\nINNER JOIN sys.objects o\nON re.referencing_id = o.object_id\nORDER BY 'Type' ASC;<\/code><\/pre>\n\n\n\n<p class=\"\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+----------------------+----------+----------------------+<br>| Type                 | Schema   | Name                 |<br>|----------------------+----------+----------------------|<br>| SQL_STORED_PROCEDURE | dbo      | uspGetClient         |<br>| SQL_STORED_PROCEDURE | dbo      | uspGetOrdersByClient |<br>+----------------------+----------+----------------------+<\/pre>\n\n\n\n<p class=\"\">Note that this alias type is used in the columns of two tables in this database. However, these don&#8217;t appear in our list of dependencies because the user-defined type is not in the definition of a computed column, <code>CHECK<\/code> constraint, or <code>DEFAULT<\/code> constraint in the table.<\/p>\n\n\n\n<p class=\"\">Also, one of the tables references the other table via a foreign key constraint on the column that uses the <code>dbo.clientcode<\/code> user-defined type, but this also doesn&#8217;t appear in our list.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Official Documentation<\/h2>\n\n\n\n<p class=\"\">For more detailed information, see <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-dynamic-management-views\/sys-dm-sql-referencing-entities-transact-sql\" target=\"_blank\" rel=\"noopener noreferrer\"><code>sys.dm_sql_referencing_entities <\/code><\/a> on the Microsoft website.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In SQL Server, you can use the sys.dm_sql_referencing_entities() system dynamic management function to get a list of all entities in the current database that reference another user-defined entity by name. In other words, it returns a list of entities that depend on the given entity.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,5],"tags":[93,77,61],"class_list":["post-6471","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server","tag-functions","tag-mssql","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/6471","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=6471"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/6471\/revisions"}],"predecessor-version":[{"id":40346,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/6471\/revisions\/40346"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=6471"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=6471"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=6471"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}