How To Draw The Dependency Diagrams

Deleting or irresolute 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 tin be that if a View queries a table and the name of that tabular array changes. The View will no longer function.

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

In SQL Server there are several means to notice object dependencies and create a SQL dependency tracker.

sp_depends is a organization stored procedure that displays data about all object types (due east.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.

The sp_depends procedure accepts one parameter, the proper noun of a database object. E.g. EXECUTE sp_depends 'ObjectName'

Let'south run these scripts higher up to create the test objects so execute the following SQL.

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

sp_depends does not display triggers.

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

sp_dependes in some case does not report dependencies correctly. Let'due south look at the situation when an object (due east.g. UserAddress ) on which another object depends (due east.k. sp_GetUserAddress ) is deleted and recreated. When sp_dependes is executed using EXECUTE sp_depends 'sp_GetUserAddress' or EXECUTE sp_depends 'UserAddress' the post-obit message will appear:

"Object does not reference whatsoever object, and no objects reference it."

This office returns all objects from the electric current database which depend on the object that is specified as an statement.

Type the following in the query window:

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

The result volition be:

	referencing_schema_name	referencing_entity_name
1	dbo	sp_GetUserAddress
ii	dbo	sp_GetUserCity

referencing_schema_name – schema of the referencing entity

referencing_entity_name – name of the referencing object

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

sys.dm_sql_referenced_entities

This organisation office 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 exist shown:

	referenced_entity_name	referenced_minor_name
1	UserAddress	Zippo
2	UserAddress	FirstName
three	UserAddress	Lastname
4	UserAddress	Accost

referenced_entity_name – Name of the referenced object

referenced_minor_name – Proper noun of the column of the referenced entity

For detailed information about outcome sets, delight visit page on this link.

Referencing vs referenced

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

When using these two function the schema proper noun (eastward.m. dbo ) must be specified as part of the object name:

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

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

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

The result will be empty set:

	referencing_schema_name	referencing_entity_name

An empty result set will be shown nether these situations:

• When is an invalid parameter passed (e.k. 'dbo.UserAddress','NN' instead dbo.UserAddress','Object' )
• When a arrangement object is specified as argument (e.g. sys.all_columns)
• When the specified object does not reference any objects
• The specified object does not exist in the current database

The message 2020

Typically, the message 2020 occurs when a referencing object e.k. procedure, calls a referenced object e.g. tabular array or a column from the table that does not be. For case, if in the Accost table change name of the column Metropolis to name Town and execute the SELECT * FROM sys.dm_sql_referenced_entities ('[dbo].[v_Address]','Object') query, the bulletin 2020 will announced.

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 announced:

Msg 207, Level sixteen, Country 1, Procedure v_Address, Line half-dozen
Invalid column proper name 'Metropolis'.
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 non be or because of an error in 1 or more statements in the entity. Before rerunning the query, ensure that in that location are no errors in the entity and that all objects referenced by the entity exist.

Troubleshooting

In social club to preclude dropping or modifying objects, which depends on some other object, the v_Address view should be altered and added the WITH SCHEMABINDING option:

ALTER VIEW v_Address WITH SCHEMABINDING AS SELECT ID , Metropolis , PostalCode , UserAddressID FROM dbo . Accost

At present, when changing the name of the column in the Address table, the following bulletin will appear, which proactively provides data that the object, the table "City" in this example, is a part of some other object.

Code:

EXEC sys . sp_rename 'dbo.Address.City' , 'Boondocks' , 'Cavalcade'

Bulletin:

Msg 15336, Level sixteen, Country one, Procedure sp_rename, Line 501
Object 'dbo.Address.City' cannot be renamed considering the object participates in enforced dependencies.

Schema-jump vs Non-schema-bound

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

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

A Non-schema-bound dependency: does non 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.

Below is an example of a temporary procedure:

CREATE Procedure #sp_tempData AS Brainstorm SELECT AddresID , FirstName , Lastname , Accost FROM UserAddress END

Now, when executing sys.dm_sql_referencing_entities for the tabular array UserAddress the information about the #sp_tempData procedure that depends on the UserAddress volition non exist shown in the list.

Code:

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

Result:

	referencing_schema_name	referencing_entity_name
1	dbo	sp_GetUserAddress
2	dbo	sp_GetUserCity

Viewing Dependencies

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

This will open up the Object Dependencies window. Past 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 (eastward.g. Address ):

If selected the Object on which radio button, volition display in the Dependencies section all objects on which selected object (e.one thousand. Accost ) depends:

The Selected object section consists of 3 fields:

• Proper name – name of the selected object from the Dependencies listing
• Type – type of the selected object (east.g.table)
• Dependency type – dependency betwixt ii objects (Schema-bound, Non-schema-bound).

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

This SQL dependency tracker is kind of a poor homo'due south SQL dependency diagram, in that information technology doesn't show cross relationships or offer many value added features, but it volition give yous a quick preview of the dependences in the hierarchy that contains a particular object

Alternatives

ApexSQL Analyze is a iii^rd party tool that tin analyzes graphical SQL Server database object dependencies and the bear upon 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.

The tool tin can be downloaded from SQL tools downloads.

To see object dependencies, on the Dwelling house tab, click the New button, nether the Connection to SQL Server window, choose the SQL Server instance, pick the type of hallmark, and after selecting a desired database in the Database driblet-down box, click the Connect button:

The Dependency viewer window will announced:

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:

Dependency viewer provides graphical view of all dependencies between objects in the heart 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 (eastward.g. view) that will be displayed in the dependency graph can be specified:

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

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

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

The Layout option under the Display ribbon offers dissimilar 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. Information technology 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 tin easily be determined how many objects depend on a specific object and a conclusion tin can be made as to whether it is condom to delete it without breaking relationships

The Testify columns pick shows columns with datatypes of the tables and views objects:

Additionally, the definition of an object can exist easily reviewed by selecting the desired object (eastward.g. Accost tabular array), correct click and from the context menu, select the Show script control:

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 political party solution, and finally how to create a SQL dependency diagram from the results. Happy diagramming!

• Writer
• Contempo Posts

Marko aka "Zivko" is a senior software annotator from Nis, Serbia focusing on SQL Server and MySQL equally well as customer technologies like SSMS, Visual Studio, and VSCode. He has extensive experience with quality assurance, event escalation/resolution, and product evangelism.

He is a prolific author of authoritative content related to SQL Server including a number of "platinum" articles (top 1% in terms of popularity and engagement). His writing covers a range of topics on MySQL and SQL Server including remote/linked servers, import/consign, LocalDB, SSMS, and more.

In his part-time, Zivko likes basketball game, foosball (table-soccer), and stone music.

See more than about Marko at LinkedIn

View all posts past Marko Zivkovic

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

Posted by: howardsciallsolle.blogspot.com

Share this post