• SQL Toolbelt Essentials
• Exploring and Refactoring Databases

Finding Dependencies in SQL Server Databases using SQL Dependency Tracker

If you work with SQL Server on a regular basis, whether as an administrator or developer, you likely need to update your database schemas on occasion. To perform these updates successfully, you must be able to identify any dependencies that exist between database objects, before making any changes. The updates themselves might be simple, such as renaming or dropping a table’s column, but other database objects might reference the table being updated, turning what seemed an easy task into a complex and risky one that could impact database operations or break applications accessing the database.

Unfortunately, the tools available through T-SQL or SQL Server Management Studio (SSMS) are not always efficient or accurate when it comes to finding SQL references and dependencies. For example, you might try to use the View Dependencies feature in SSMS, but there’s little guarantee your results will be complete. The same goes for T-SQL tools such as sp_depends or sys.sysdepends, which are known for being unreliable and have been deprecated. The system catalog view, sys.sql_expression_dependencies, is more reliable, but it won’t return foreign key references, forcing you to take multiple steps to find a table’s dependencies. You can get further dependency information from sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities.

Redgate’s SQL Dependency Tracker can show you all dependencies in one diagram. It will include the dependencies an object has (objects that make references to it), and the objects it depends on (those it references). It includes foreign key references. It also allows you to see exactly which columns are referenced, and to generate DDL scripts for all objects in the dependency diagram.

SQL Dependency Tracker is included with SQL Toolbelt and SQL Toolbelt Essentials. It’s easy to install and requires no special setup, and it’s integrated into SSMS. Once you understand how to use SQL Dependency Tracker, you can identify any object’s dependencies with little effort. You’ll still need to update the dependent objects after you identify them, but with SQL Dependency Tracker, you have a quick and simple way to get started.

Generating a database-level dependency diagram

When using SQL Dependency Tracker to identify an object’s dependencies, you start by generating a dependency diagram. The diagram works in conjunction with other SQL Dependency Tracker features to help quickly identify an object’s dependencies, whether they’re tables, views, stored procedures, or other types of objects.

To demonstrate how SQL Dependency Tracker works, this article walks you through the steps necessary to identify the dependencies on a table and its columns. The examples are based on the Warehouse.PackageTypes table in the WideWorldImporters database. The example assumes that you plan to rename the table’s PackageTypeName column, but first you need to identify any dependencies that involve that column.

We’re going to start by generating a dependency diagram for the entire WideWorldImporters database. We can do this either from SSMS, or from within the SQL Dependency Tracker interface. We’ll start from SSMS, but you’ll see the same results by opening the SQL Dependency tracker GUI, and adding the WideWorldImporters database to the project.

Generating dependency diagrams from SSMS

To generate the diagram, right-click the database in Object Explorer and then click the option View Dependency Diagram for [WideWorldImporters], as shown in the following figure.

When you click this option, SQL Dependency Tracker automatically launches and generates a diagram that includes the objects in the WideWorldImporters database, as shown in the following figure.

The SQL Dependency Tracker interface comprises four main sections (outlined in red in the figure):

• The toolbar provides features for adding and selecting objects and changing the diagram’s layout.
• The Diagram pane displays the dependency diagram. The diagram includes all the specified objects (in this case, everything in the WideWorldImporters database). The diagram also shows the dependencies between objects, which are represented by the gray lines.
• The Diagram overview pane provides a bird’s eye view of the diagram. The pane enables you to see your current position relative to the complete diagram as you scroll through the diagram or zoom in on objects.
• The Objects in project pane lists all the objects in the project. If the check box associated with an object is selected, that object is visible within the Diagram pane. If the object itself is selected (highlighted), that object is also selected in the diagram. (Selected objects are kept in sync between the two panes.) The Objects in project pane also includes a search box for filtering the list of objects.
• The Dependencies pane lists the dependencies of the objects selected in the Diagram pane and Objects in project pane. In this case, no objects are selected, so nothing is listed in the Dependencies pane.

You can zoom in and out of the dependency diagram and move it around to view specific components, either by scrolling through the diagram or by dragging it to a specific position. You can also select objects in the diagram, reposition them, and then use Toolbar menu icons to hide objects, remove them from the project and so on. The following figure shows several objects up close – the Suppliers table, Suppliers view, and the SearchForPeople and SearchForSuppliers stored procedures – but you can focus on any objects or reposition them as necessary.

The figure also shows the dependencies between objects (the gray lines). We’ll go into these dependencies in more detail later in the article. Just know that the lines are currently gray because no objects have been selected.

SQL Dependency Tracker also provides several predefined layouts that you can apply to the dependency diagram. When we first generated the diagram, SQL Dependency Tracker used the Balloon tree layout for the WideWorldImporters database objects, but you can choose from several other layouts, which you can select from the toolbar. For example, the following figure shows the dependency diagram in the Hierarchic layout.

Regardless of the selected layout, if your database is of any significant size or complexity, the diagram itself can be quite unwieldy. Even if you have a 42-inch monitor, trying to identify one table’s dependencies can be a daunting task. Fortunately, SQL Dependency Tracker provides a much easier way to find the dependencies you’re looking for.

Drilling down to identify a database object’s dependencies

The Objects in project pane makes it quick and easy to identify a table’s dependencies (or any object’s dependencies). At the bottom of the pane, you’ll find a search box, where you can type the name of your target object. Any database objects that match your search are listed in the pane’s main window. The search box also supports the asterisk (*) wildcard, which represents any number of characters, and the question mark (?) wildcard, which indicates a single character.

To quickly locate the PackageTypes table, type PackageTypes into the search box. SQL Dependency Tracker will list the table in the main part of Objects in project pane. Select the table so it’s highlighted. When you do, SQL Dependency Tracker automatically lists the table’s dependencies in the Dependencies pane. At the same time, SQL Dependency Tracker selects the table in the Diagram pane and highlight its dependencies with other objects, as shown in the following figure.

The orange lines connect the objects that reference the PackageTypes table, and the blue lines connect the objects that are referenced by the PackageTypes table. The referencing and referenced objects are also outlined in the same color as the dependency lines. We’ll get a closer look at these dependencies in just a bit.

For many database developers and administrators, the information in the Dependencies pane is all they need to prepare for the column update. The referencing objects are listed beneath the Used By node, and the referenced objects are listed beneath the Uses node. Four tables and four stored procedures reference the PackageTypes table. The StockItems table is listed twice because it includes two FOREIGN KEYs that reference the PackageTypes table. The PackageTypes table is also dependent on two objects: the People table and Warehouse schema.

If this is all the information you need, you can stop here and follow up with the dependent objects to see whether they need to be updated. The original scenario for this article is based on the idea that the PackageTypeName column in the PackageTypes table will be renamed. If you were to look at the SQL script for the eight referencing objects (right-click the object in the diagram and then clicking Show SQL Script), you would discover that only the stored procedures specifically reference this column, so if you rename the column, you’ll need to update the stored procedures.

You will not need to change the referencing tables because they reference only the PackageTypeID column in the PackageTypes table. You also do not need to worry about the referenced objects because they would not be impacted by renaming the PackageTypeName column. In addition, the FOREIGN KEY defined on the PackageTypes table is based on the table’s LastEditedBy column, not the PackageTypeName column.

As you can see, SQL Dependency Tracker makes it quick and easy to get a list of an object’s dependencies. All it takes is a few simple steps:

1. Generate a dependency diagram for the entire database.
2. Search for your target object in the Objects in project pane.
3. Select your target object in the Objects in project pane.
4. View the object’s dependencies in the Dependencies pane.

This approach is about as clean and simple as it gets, and I recommend that you adopt this as your default method for viewing dependencies. By taking a top-down approach, starting at the database level, and drilling down, as we’ve done here, you get all the referencing and referenced dependencies within the database. You can view dependencies just for individual objects, as I’ll demo later in the article, but you often create a bit of extra work for yourself, or need to tweak project options to get the results you need.

Despite the ease of this approach, the diagram we’ve seen so far is rather unwieldy. We need a way to limit the scope or range of the dependencies displayed. Fortunately, SQL Dependency Tracker includes several features that make it easy to update the diagram. I’ll show you a simple Select-Invert-Hide technique that allows you to view only the direct dependencies for specific objects, in this case the PackageTypes table.

Viewing the direct dependencies for a selected object

The first step is to select all the dependent objects associated with the PackageTypes table. To do this, first ensure that the PackageTypes table is still selected in the Objects in project pane, and then click the Connected Objects button on the toolbar. SQL Dependency Tracker will select all the dependent objects and highlight their dependencies, as shown in the following figure.

The next step is to invert your selection so that all objects are selected except the target object and its dependencies. You can then hide all these extra objects, essentially clearing them from the diagram. To make the switch, click the Invert button on the toolbar and then click the Hide button. This will remove all objects but the target table and its dependencies.

At this point, you should again ensure that the PackageTypes table is selected in the Objects in project pane. You might also want to select a different layout. For example, the following figure shows the PackageTypes table and its dependencies using the Circular layout. The PackageTypes table is solid blue because it is selected in the diagram and in the Objects in project pane.

With all other objects removed, the dependency diagram is now much easier to read. The objects that reference the PackageTypes table are outlined in orange, and the dependency lines are orange, with the arrows pointing toward the PackageTypes table. The objects that the PackageTypes table references are outlined in blue, and the dependency lines are blue, with the arrows pointing away from the PackageTypes table.

You can customize the diagram table even further by repositioning the objects around to display them in a way that best suits your need. You can also click the Table column names and types button on the toolbar to display the table’s column. For example, the following figure shows the diagram with similar objects grouped together. The referencing tables are near the top, the referencing stored procedures are near the bottom, and the referenced table and schema are off to the left. In addition, all the table objects now show the column names.

The View menu also includes this option, and several other others, such as the Qualify name option, which lets you display the full names of the database and its objects as well as other options.

When you move objects around, SQL Dependency Tracker automatically maintains the dependency lines between them, so you always have a clear indication of how the objects reference each other.

Step-by-step: the ‘top down’ approach to viewing an object’s dependencies

SQL Dependency Tracker makes it easy to create diagrams that can help you visualize dependencies. You can start with the same four steps as before but then add three more steps to set up a customized diagram:

1. Generate a dependency diagram for the entire database.
2. Search for your target object in the Objects in project pane.
3. Select your target object in the Objects in project pane.
4. View the object’s dependencies in the Dependencies pane.
5. On the toolbar, click Connected Objects, then click Invert, and finally click Hide.
6. Ensure that the PackageTypes table is selected in the Diagram pane or Objects in project pane.
7. Arrange the diagram to meet your needs.

At this point, you can export the dependencies diagram as an image or PDF file, or you can export a list of dependencies as a PDF file or in an XML format. You’ll find the export options on the File menu.

Viewing column details and SQL Scripts for database objects

As I’ve mentioned previously, you can view specific information about any object in your diagram. For example, the following image shows a close-up of the PackageTypes table, where you can see the object name and type, the column names, the number of indexes, and the database name.

By default, an object also displays a red bar that shows its relative importance. This bar indicates how many other objects use the target object, taking into account dependencies at all levels, not just the immediate level. The Hierarchic layout also uses the relative importance feature to organize objects based on their usage by other objects. Those with the most connections are positioned at the bottom of the hierarchy. If you don’t want your objects to show the relative importance bar, you can disable this feature by clicking the Relative importance bars option on the View menu.

You can also view an object’s SQL script by right clicking the object and selecting Show SQL Script. The following figure shows the script used to create the PackageTypes table and the PackageTypes_Archive table, which supports system versioning for the PackageTypes table. You can also select, copy, or search the script by right-clicking the script area and then selecting the appropriate option.

One thing worth noting about the script is that it creates a unique nonclustered index on the PackageTypeName column, so you would need to be aware of that when renaming the column.

Being able to view and search the SQL script can be handy when determining how a dependent object references the target table. For example, the following figure shows the SQL script for the GetSalesUpdates stored procedure.

As you can see, the procedure’s SELECT statement references the PackageTypeName column in the PackageTypes table. If you were to rename this column, you would also have to update this stored procedure.

Generating an object-level dependency diagram

When we first started off with our PackageTypes example, we generated the initial dependencies diagram for the entire database. Although this ‘top-down’ approach works well, you can also generate dependency diagrams based on object types, such as tables or views, as well as on individual objects.

Again, you can generate the object-level diagram from within SSMS or SQL Dependency Tracker but using the latter, in this case, gives you some extra functionality. For example, in Dependency Tracker, I can add multiple object types at the same time, such as all tables, views, and stored procedures, or add multiple individual objects, such as all tables in the Sales schema. In SSMS, I can generate a diagram for only one item at a time.

So, let’s see how we can generate object-level dependency diagrams in the SQL Dependency Tracker GUI. Open Dependency Tracker, and on the toolbar, click Add objects to project. Locate your target SQL Server instance in the top pane and then select the WideWorldImporters database, and in the bottom pane, navigate to the Warehouse.PackageTypes table, select the check box associated with that table, and click Add Selection to Project, as shown in the following figure.

SQL Dependency Tracker automatically adds the target object and referencing dependencies to the diagram. Be sure to click Close to close the dialog box. With the PackageTypes table selected you’ll see a diagram that looks like this (I’ve chosen the circular format)

You will notice that this time, unlike when we generated a database-level diagram, we only see the referencing (‘used by’) dependencies and not the referenced (‘used’) objects. However, we can change this behavior by tweaking the project’s diagram options.

Controlling which dependencies are shown: the diagram options

You can access the project’s diagram options by selecting Options from the Tools menu. This launches the Project Options dialog box. The diagram options are located on the Diagram tab. The following figure shows the tab’s default settings.

When generating a dependency diagram from within SQL Dependency Tracker, the diagram will include, by default, all dependency levels for referencing objects, as well as external and unresolved references. It’s won’t include objects that the target object references (uses), which is why they do not appear in our previous diagram.

If we were to click the Add objects that the selected object use box, click OK, and then regenerate the dependency diagram for PackageTypes, it will now include the “uses” objects as well, and we could then use the Select-Invert-Hide technique from earlier to arrive at the same dependency diagram we saw when drilling top-down from the database level diagram.

The alternative way to arrive at the same diagram, is to edit the options to include only those objects that directly reference the PackageTypes table and exclude all other objects from your project, whether referencing or referenced objects. To do this, just clear the All levels check box and ensure that 1 is specified in the levels text box directly beneath it. In this case, I’m going to do this just for referencing objects, so that I’ll see only objects that directly reference the PackageTypes table, and I also clear the Add external and unresolved references check box near the bottom of the tab (however, you can choose to also see objects your chosen object directly references, as well as external references, if you prefer).

Click OK to apply the new settings and then you’ll need to regenerate the dependency diagram for them to take effect (unfortunately, you can’t change the project’s diagram options unless you already have an open project, and the project contains objects).

Viewing all the objects that reference a table

In the Diagram pane or Objects in project pane, select the PackageTypes table and then rearrange the diagram to best suit your needs. Your diagram should now look similar to the following figure. The table’s dependencies are also listed in the Dependencies pane, just like you saw earlier.

This approach makes it easy to generate exactly the diagram you need for a specific object, without having to filter out all the other objects. However, if you want to view the dependencies for multiple objects during a single session, you might find it easier to generate a diagram for the entire database, and then use the Objects in project pane to work with one object at a time.

Making the most of SQL Dependency Tracker

SQL Dependency Tracker provides several other features for identifying and illustrating database dependencies, and it could well be worth your effort to learn more about these features. But even if you don’t go beyond what I explained here, you can see how easy is to generate a diagram and identify an object’s dependencies. It’s no exaggeration to say that you can get the dependency information you need and even export it to a convenient format within only a couple minutes. And given that SQL Dependency Tracker is already included with SQL Toolbelt, customers have little to lose by taking it for a test drive to see for themselves how handy this tool can be.

Tools in this post

SQL Dependency Tracker

Visualize SQL Server object dependencies

Find out more

SQL Toolbelt Essentials

The industry-standard tools for SQL Server development and deployment.

Find out more