How to visualize SQL database objects dependencies

ApexSQL Search is a SQL Server Management Studio and Visual Studio add-in that, among other features, provides object a database dependency diagram.

The View Dependencies feature has the ability to visualize all SQL database objects’ relationships, including those between encrypted and system objects, SQL server 2012 specific objects, and objects stored in databases encrypted with Transparent Data Encryption (TDE). Let’s take a closer look at this feature.

To visualize SQL object’s dependencies, right-click an object (e.g. a table) in the SQL Server Management Studio’s Object Explorer, and select the View Dependencies command from the ApexSQL Search drop-down menu:

Database dependency diagram layout

Besides having the ability to present database dependencies in a diagram, the View Dependencies feature also allows you to set up and customize the visual database dependencies diagram layout, including the relationships that will be presented, the layout and size of the generated diagram, and the drill-down depth of the dependencies.

The Dependency layout option controls how the View dependencies feature will organize generated dependency diagrams:

The Dependency layout option

Circular – Objects will be shown in a circle around the object they are related to. This option is useful when you need to identify cross-referencing objects. All cross-references are immediately visible in this mode:

Force directed – Places the most frequently used objects nearer to the center of the diagram, while the least frequently used ones are placed nearer to the outside. This mode allows quick identification of all objects to be affected by a change to a given object (i.e. all objects that depend on it)

Hierarchical – Organizes objects based on the generation, so that parents are at the top and descendants at the bottom of the diagram; all objects from the same generation are in the same horizontal row. This display type helps you to quickly find out if it’s safe to delete an object (i.e. that no other object depends on it)

Orthogonal – This 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)

After introducing View dependencies feature basics and database dependency diagram layout options we will proceed to present Dependency relationships’ options

Dependency relationships

The View Dependencies feature allows choosing relationships to be included in the generated database dependency diagrams, by selecting different dependency types:

Children only – Shows only the specified object and those directly depending on it. Children objects depend directly on the specified object. Parents and the second generation descendants(grandchildren) will not be included

Descendants – Descendant objects indirectly depend on the specified object via other objects. Diagrams show the object and all descendants, including children and grandchildren (e.g. it allows you to see the specified object, those directly depending on it and all objects depending on them as well):

Parents and children only – A parent object is an object the specified one depends on. Parents Diagrams will show the object, plus its parents and children. Descendants will not be shown. Shown is the specified object, those it depends on, and those that directly depend on it:

Parents and descendants – Diagrams will show all dependencies for the specified object, including parents, children, and descendants. You’re able to see the complete dependency chain:

March 18, 2015