人的依賴關系和物的依賴關系，如何在SQL Server中創建SQL依賴關系圖

Deleting or changing objects may affect other database objects like views or procedures that depends on them and in certain instances, can “break” the depending object. An example can be that if a View queries a table and the name of that table changes. The View will no longer function.

刪除或更改對象可能會影響其他數據庫對象，例如依賴于它們的視圖或過程，并且在某些情況下可能會“破壞”依賴的對象。 一個示例可以是，如果View查詢一個表，并且該表的名稱發生更改。 視圖將不再起作用。

人的依賴關系和物的依賴關系、 To understand the interdependencies of our database it is very helpful to see and analyze these dependencies in a SQL Server dependency tree, and ultimately to even create a SQL dependency diagram visually displaying the hierarchical relationships

要了解我們數據庫的相互依賴性，在SQL Server依賴性樹中查看和分析這些依賴性非常有幫助，最終甚至可以創建可視化顯示層次關系SQL依賴性圖。

In SQL Server there are several ways to find object dependencies and create a SQL dependency tracker.

sql server創建數據表， 在SQL Server中，有幾種查找對象依賴關系和創建SQL依賴關系跟蹤器的方法。

1. The sp_depends system stored procedure
   sp_depends系統存儲過程
2. • sys.dm_sql_referencing_entities
     sys.dm_sql_referencing_entities
   • sys.dm_sql_referenced_entities
     sys.dm_sql_referenced_entities
3. The View Dependencies feature in SQL Server Management Studio (SSMS)
   SQL Server Management Studio（SSMS）中的“視圖依賴項”功能

sp_depends (sp_depends )

sp_depends is a system stored procedure that displays information about all object types (e.g. procedures, tables, etc) that depend on the object specified in the input parameter as well as all objects that the specified object depends on.

sp_depends是一個系統存儲過程，它顯示有關所有對象類型（例如，過程，表等）的信息，這些對象類型取決于輸入參數中指定的對象以及指定對象所依賴的所有對象。

Access創建關系， The sp_depends procedure accepts one parameter, the name of a database object. E.g. EXECUTE sp_depends ‘ObjectName’

sp_depends過程接受一個參數，即數據庫對象的名稱。 例如EXECUTE sp_depends'ObjectName '

Below are examples, which will be used in this article:

創建數據表之間的關系圖。 下面是示例，將在本文中使用：

-- New database
CREATE DATABASE TestDB;
GOUSE TestDB
GOCREATE TABLE UserAddress (AddresID INT PRIMARY KEY IDENTITY(1, 1),FirstName VARCHAR(100),Lastname VARCHAR(150),Address VARCHAR(250))
GO-- New procedure
CREATE PROCEDURE sp_GetUserAddress
AS
BEGINSELECT FirstName,Lastname,AddressFROM UserAddress
END
GOCREATE TABLE Address (ID INT NOT NULL IDENTITY(1, 1),City VARCHAR(120),PostalCode INT,UserAddressID INT FOREIGN KEY REFERENCES UserAddress(AddresID))
GO
-- New View
CREATE VIEW v_Address
AS
SELECT ID,City,PostalCode,UserAddressID
FROM dbo.Address
GOCREATE PROCEDURE sp_GetUserCity
AS
BEGINSELECT UserAddress.FirstName,UserAddress.Lastname,Address.CityFROM UserAddressINNER JOIN Address ON UserAddress.AddresID = Address.UserAddressID
END
GO
-- New Trigger
CREATE TRIGGER trgAfterInsert ON [dbo].[UserAddress]
FOR INSERT
AS
PRINT 'Data entered successfully'
GO

Let’s run these scripts above to create the test objects then execute the following SQL.

讓我們在上面運行這些腳本來創建測試對象，然后執行以下SQL。

EXECUTE sp_depends 'UserAddress'

The following result will be:

結果如下：

• name – name of dependent object 名稱 –依賴對象的名稱
• type –類型 – type of dependent object (e.g. table) 依賴對象的類型（例如表）

If a stored procedure is specified as an argument value in sp_depends, then a name of the table and the column names on which the procedure depends will be shown.

如果將存儲過程指定為sp_depends中的參數值，則將顯示該過程所依賴的表名和列名。

Let’s see how this looks with sp_GetUserAddress

讓我們看一下sp_GetUserAddress的外觀

EXECUTE sp_depends 'sp_GetUserAddress'

The following result will be:

結果如下：

• name – name of dependent object名稱 –依賴對象的名稱
• type – type of dependet object (e.g. table) 類型 –依賴對象的類型（例如表）
• updated – whether the object is updated or not 已更新 –對象是否已更新
• selected – object is used in the SELECT statement selected –在SELECT語句中使用對象
• column – column on which the dependency exists column –依賴項所在的列

sp_depends does not display triggers.

sp_depends不顯示觸發器。

To illustrate this, execute the following code in the query window:

為了說明這一點，請在查詢窗口中執行以下代碼：

CREATE TRIGGER trgAfterInsert ON [dbo].[UserAddress]
FOR INSERT
AS
PRINT 'Data entered successfully'
GO

Now execute the sp_depends over the UserAddress table, the trgAfterInsert will not appear in the Results table:

現在，在UserAddress表上執行sp_depends ， trgAfterInsert將不會出現在Results表中：

sp_dependes in some case does not report dependencies correctly. Let’s look at the situation when an object (e.g. UserAddress) on which another object depends (e.g. sp_GetUserAddress) is deleted and recreated. When sp_dependes is executed using EXECUTE sp_depends ‘sp_GetUserAddress’ or EXECUTE sp_depends ‘UserAddress’ the following message will appear:

在某些情況下， sp_dependes無法正確報告依賴關系。 讓我們看看當一個物體（如UserAddress）另一對象取決于形勢（如sp_GetUserAddress）被刪除并重新創建。 當使用的sp_depends EXECUTE“sp_GetUserAddress”或EXECUTE sp_depends將“UserAddress”執行sp_dependes將出現以下消息：

Sadly, sp_dependes is on the path to deprecation and will be removed from future versions of the SQL Server. But you can use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead.

遺憾的是， sp_dependes即將棄用，并且將從SQL Server的未來版本中刪除。 但是您可以改用sys.dm_sql_referencing_entities和sys.dm_sql_referenced_entities 。

sys.dm_sql_referencing_entities

sys.dm_sql_referencing_entities

This function returns all objects from the current database which depend on the object that is specified as an argument.

此函數從當前數據庫返回所有對象，這些對象取決于指定為參數的對象。

SELECT referencing_schema_name,referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.UserAddress', 'Object')

The result will be:

結果將是：

referencing_schema_name – schema of the referencing entity

referencing_schema_name –引用實體的架構

referencing_entity_name – name of the referencing object

referencing_entity_name –引用對象的名稱

More information about result sets can be found on this link.

關于結果集的更多信息可以在此鏈接上找到。

sys.dm_sql_referenced_entities

sys.dm_sql_referenced_entities

This system function returns all objects from the current database on which specified object depends on.

該系統函數從當前數據庫返回指定對象所依賴的所有對象。

Enter the following code in the query window:

在查詢窗口中輸入以下代碼：

SELECT referenced_entity_name,referenced_minor_name
FROM sys.dm_sql_referenced_entities('dbo.sp_GetUserAddress', 'Object')

The following result will be shown:

將顯示以下結果：

referenced_entity_name – Name of the referenced object

referenced_entity_name –引用對象的名稱

referenced_minor_name – Name of the column of the referenced entity

referenced_minor_name –被引用實體的列名

For detailed information about result sets, please visit page on this link.

有關結果集的詳細信息，請訪問此鏈接上的頁面。

引用與引用 (Referencing vs referenced)

The objects that are appears inside the SQL expression are called the referenced entity and the objects which contain expressions are called referencing entity:

SQL表達式內出現的對象稱為引用實體，而包含表達式的對象稱為引用實體：

When using these two function the schema name (e.g. dbo) must be specified as part of the object name:

使用這兩個函數時，必須將模式名稱（例如dbo ）指定為對象名稱的一部分：

SELECT referencing_schema_name,referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.UserAddress', 'Object')

Otherwise no results will be displayed. Run the query without shema nema (dbo):

否則，將不會顯示任何結果。 在不使用shema nema（dbo）的情況下運行查詢：

SELECT referencing_schema_name,referencing_entity_name
FROM sys.dm_sql_referencing_entities('UserAddress', 'Object')

The result will be empty set:

結果將為空集：

An empty result set will be shown under these situations:

在以下情況下將顯示空結果集：

• ‘dbo.UserAddress’,’NN’ 'dbo.UserAddress'，'NN'dbo.UserAddress’,’Object’dbo.UserAddress'，'Object'
• sys.all_columns) sys.all_columns ）
• When the specified object does not reference any objects
  當指定的對象未引用任何對象時
• The specified object does not exist in the current database
  指定的對象在當前數據庫中不存在

消息2020 (The message 2020 )

Typically, the message 2020 occurs when a referencing object e.g. procedure, calls a referenced object e.g. table or a column from the table that does not exist. For example, if in the Address table change name of the column City to name Town and execute the SELECT * FROM sys.dm_sql_referenced_entities (‘[dbo].[v_Address]’,’Object’) query, the message 2020 will appear.

通常，消息2020在引用對象（例如過程）調用引用對象（例如表或表中不存在的列）時發生。 例如，如果在“ 地址”表中將“ 城市 ”列的名稱更改為“ 城市”并執行SELECT * FROM sys.dm_sql_referenced_entities（'[dbo]。[v_Address]'，'Object'）查詢，則會出現消息2020。

Execute the following code:

執行以下代碼：

EXEC sys.sp_rename 'dbo.Address.City','Town','COLUMN'SELECT *
FROM sys.dm_sql_referenced_entities('dbo.v_Address', 'OBJECT')

The following message will appear:

將顯示以下消息：

Msg 207, Level 16, State 1, Procedure v_Address, Line 6
Invalid column name ‘City’.
Msg 2020, Level 16, State 1, Line 3 The dependencies reported for entity “dbo.v_Address” might not include references to all columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity. Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.

消息207，級別16，狀態1，過程v_Address，第6行
無效的列名“城市”。
消息2020，級別16，狀態1，第3行實體“ dbo.v_Address”報告的依賴項可能未包括對所有列的引用。 這是因為該實體引用了一個不存在的對象，或者是由于該實體中的一個或多個語句中的錯誤。 重新運行查詢之前，請確保該實體中沒有錯誤，并且該實體引用的所有對象都存在。

故障排除 (Troubleshooting )

In order to prevent dropping or modifying objects, which depends on another object, the v_Address view should be altered and added the WITH SCHEMABINDING option:

為了防止丟棄或修改依賴于另一個對象的對象，應更改v_Address視圖并添加WITH SCHEMABINDING選項：

ALTER VIEW v_AddressWITH SCHEMABINDING
AS
SELECT ID,City,PostalCode,UserAddressID
FROM dbo.Address

Now, when changing the name of the column in the Address table, the following message will appear, which proactively provides information that the object, the table “City” in this example, is a part of another object.

現在，當更改“ 地址”表中的列名稱時，將出現以下消息，該消息主動提供信息，即對象（在此示例中為“城市”）是另一個對象的一部分。

Code:

碼：

EXEC sys.sp_rename 'dbo.Address.City','Town','COLUMN'

Message:

信息：

Msg 15336, Level 16, State 1, Procedure sp_rename, Line 501
Object ‘dbo.Address.City’ cannot be renamed because the object participates in enforced dependencies.

消息15336，級別16，狀態1，過程sp_rename，第501行
無法重命名對象“ dbo.Address.City”，因為該對象參與了強制性依賴關系。

架構綁定與非架構綁定 (Schema-bound vs Non-schema-bound)

There are two types of dependencies: Schema-bound and Non-schema-bound dependencies.

有兩種類型的依賴關系：架構綁定的和非架構綁定的依賴。

A Schema-bound dependency (SCHEMABINDING) prevents referenced objects from being altered or dropped as long as the referencing object exists

綁定模式的依賴關系（SCHEMABINDING）防止只要存在引用對象，就可以更改或刪除引用的對象

A Non-schema-bound dependency: does not prevent the referenced object from being altered or dropped.

非模式綁定的依賴項 ：不會阻止所引用的對象被更改或刪除。

For sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities dependency information will not be displayed for temporary tables, temporary stored procedures or system objects.

對于sys.dm_sql_referenced_entities和sys.dm_sql_referencing_entities，將不會顯示臨時表，臨時存儲過程或系統對象的依賴項信息。

Below is an example of a temporary procedure:

下面是一個臨時過程的示例：

CREATE PROCEDURE #sp_tempData
AS
BEGINSELECT AddresID,FirstName,Lastname,AddressFROM UserAddress
END

Now, when executing sys.dm_sql_referencing_entities for the table UserAddress the information about the #sp_tempData procedure that depends on the UserAddress will not be shown in the list.

現在，當執行sys.dm_sql_referencing_entities時 為表UserAddress有關依賴于UserAddress的#sp_tempData過程中的信息將不會在列表中顯示。

Code:

碼：

SELECT referencing_schema_name,referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.UserAddress', 'Object')

Result:

結果：

查看依賴關系 (Viewing Dependencies)

Another way to view dependencies between objects, but to create a visual SQL dependency tracker, is by using the View Dependencies option from SSMS. From the Object Explorer pane, right click on the object and from the context menu, select the View Dependencies option:

查看對象之間的依賴關系但創建可視SQL依賴關系跟蹤器的另一種方法是使用SSMS中的“ 查看依賴關系”選項。 在“ 對象資源管理器”窗格中，右鍵單擊對象，然后從上下文菜單中選擇“ 視圖依賴項”選項：

This will open the Object Dependencies window. By default, the Object that depend on radio button is selected. This radio button will list in the Dependencies section all objects that depends on the selected object (e.g. Address):

這將打開“ 對象依賴關系”窗口。 默認情況下， 依賴單選按鈕的對象處于選中狀態。 此單選按鈕將在Dependencies部分中列出所有依賴于所選對象的對象（例如Address ）：

If selected the Object on which radio button, will display in the Dependencies section all objects on which selected object (e.g. Address) depends:

如果選中了對象所在的單選按鈕，將在“ 依賴關系”部分中顯示所選對象（例如Address ）所依賴的所有對象：

The Selected object section consists of three fields:

選定對象部分包括三個字段：

• Name – name of the selected object from the 名稱 -從Dependencies list 相關性列表中選擇的對象的名稱
• Type – type of the selected object (e.g.table) 類型 –所選對象的類型（例如）
• Dependency type – dependency between two objects (Schema-bound, Non-schema-bound). 依賴關系類型 –兩個對象之間的依賴關系（架構綁定，非架構綁定）。

Under the Type field the Unresolved Entity type for the object can be appear. This happens when the objects refer to an object that don’t exist in the database. This is equivalent to the Msg 2020 message that appears when using sys.dm_sql_referencing_entities or sys.dm_sql_referenced_entities functions:

在“ 類型”字段下，可以顯示對象的“ 未解析實體”類型。 當對象引用數據庫中不存在的對象時，就會發生這種情況。 這等效于使用sys.dm_sql_referencing_entities或sys.dm_sql_referenced_entities函數時出現的Msg 2020消息：

This SQL dependency tracker is kind of a poor man’s SQL dependency diagram, in that it doesn’t show cross relationships or offer many value added features, but it will give you a quick preview of the dependences in the hierarchy that contains a particular object

該SQL依賴關系跟蹤器有點像窮人SQL依賴關系圖，因為它不顯示交叉關系或提供許多增值功能，但是它將為您提供包含特定對象的層次結構中的依賴關系的快速預覽。

備擇方案 (Alternatives)

ApexSQL Analyze is a 3^rd party tool that can analyzes graphical SQL Server database object dependencies and the impact of potential deletions on your SQL database and create a SQL dependency diagram. It determines object interrelationships within the database, and allows customization of the resulting SQL dependency diagram appearance.

ApexSQL分析是一個^第三方的工具，可以分析圖形SQL Server數據庫對象依賴性和潛在的缺失你SQL數據庫的影響，并創建一個SQL依賴關系圖。 它確定數據庫內的對象相互關系，并允許自定義結果SQL依賴圖外觀。

The tool can be downloaded from SQL tools downloads.

該工具可以從SQL工具下載中下載 。

To see object dependencies, on the Home tab, click the New button, under the Connection to SQL Server window, choose the SQL Server instance, pick the type of authentication, and after selecting a desired database in the Database drop-down box, click the Connect button:

若要查看對象依賴性，請在“ 主頁”選項卡上，單擊“ 新建”按鈕，在“ 連接到SQL Server”窗口下，選擇SQL Server實例，選擇身份驗證的類型，然后在“ 數據庫”下拉框中選擇所需的數據庫后，單擊“確定”。 連接按鈕：

The Dependency viewer window will appear:

將顯示“ 依賴關系查看器”窗口：

with the Dependencies pane, which shows all objects that depends on the selected object (e.g. UserAddress), by default this pane appears on the right side of the Dependency viewer window:

使用“ 依賴關系”窗格，該窗格顯示了依賴于所選對象的所有對象（例如UserAddress ），默認情況下，此窗格顯示在“ 依賴關系查看器”窗口的右側：

Dependency viewer provides graphical view of all dependencies between objects in the middle of the Dependency viewer window:

依賴關系查看器在“ 依賴關系查看器”窗口的中間提供了對象之間所有依賴關系的圖形視圖：

視覺依賴 (Visual dependencies)

The Dependency viewer offers various options for filtering, appearance and manipulating objects.

依賴關系查看器提供了用于過濾，顯示和操縱對象的各種選項。

In the Object browser pane, the object types (e.g. view) that will be displayed in the dependency graph can be specified:

在“ 對象瀏覽器”窗格中，可以指定將在依賴關系圖中顯示的對象類型（例如視圖）：

Also, in the Object browser pane, specific objects can be selected that will be shown or omitted from the dependency graph:

另外，在“ 對象瀏覽器”窗格中，可以選擇特定對象，這些對象將在依賴關系圖中顯示或省略：

The Dependencies pane, the complete dependency chain for the selected object in the dependency graph (e.g. Address) can be shown. Referencing indicate the object that depend on the selected object (aka referencing) and Referenced by shows the objects from which selected object depends on (aka referenced):

“ 依賴關系”窗格中，可以顯示依賴關系圖中所選對象的完整依賴鏈（例如Address ）。 引用表示依賴于所選對象的對象（又稱為引用），“ 引用者”顯示所選對象所依賴的對象（又稱為引用）：

Also, the dependency chain can be reviewed by selecting an object in the dependency graph (e.g. Address), right click and from the context menu under the Select sub-menu, choose Referencing objects or Referenced objects command:

同樣，可以通過在依賴關系圖中選擇一個對象（例如Address ），單擊鼠標右鍵，然后從“ 選擇”子菜單下的上下文菜單中，選擇“ 引用對象”或“ 引用的對象”命令來查看依賴關系鏈：

The Layout option under the Display ribbon offers different options for visual organization and display:

“ 顯示”功能區下的“ 布局”選項為視覺組織和顯示提供了不同的選項：

For example, the Orthogonal option attempts to organize objects in the diagrams so that they are at right angles to each other. It is useful for quick identification of all objects related to a given object (i.e. both those that depend on it and those it depends on):

例如，“ 正交”選項嘗試組織圖中的對象，以使它們彼此成直角。 對于快速識別與給定對象相關的所有對象（即，依賴于該對象的對象以及該對象所依賴的對象），這很有用：

Using this option, it can easily be determined how many objects depend on a specific object and a determination can be made as to whether it is safe to delete it without breaking relationships

使用此選項，可以輕松確定有多少對象依賴于特定對象，并且可以確定在不破壞關系的情況下刪除該對象是否安全

The Show columns option shows columns with datatypes of the tables and views objects:

顯示列選項顯示具有表和視圖對象的數據類型的列：

Additionally, the definition of an object can be easily reviewed by selecting the desired object (e.g. Address table), right click and from the context menu, select the Show script command:

此外，通過選擇所需的對象（例如地址表），單擊鼠標右鍵，然后從上下文菜單中選擇“ 顯示腳本”命令，可以輕松查看對象的定義：

The script window will appear with definition of the selected object:

將顯示腳本窗口，其中包含所選對象的定義：

In this article we discussed SQL Server object interdependencies, how to find them using SQL, SSMS and a 3^rd party solution, and finally how to create a SQL dependency diagram from the results. Happy diagramming!

在這篇文章中，我們討論了SQL Server對象的相互依存關系，如何使用SQL，SSMS和^第三方解決方案，最后如何創建一個找他們SQL依賴關系圖從結果。 圖解愉快！

翻譯自: https://www.sqlshack.com/how-to-create-a-sql-dependency-diagram-in-sql-server/