Total Clarity: Analyzing and Documenting SQL Server

Tracking the Dependencies

Figure 3

Let's add the dependent objects to our diagram, as demonstrated in Figure 3. Move across from Add to Project to click on Objects the Selection is Used By. Then click on Reapply Layout to ensure that the number of objects expand into an easier-to-read grid. As the number of objects increases, you can try different layout types from the five available. A hierarchical layout can be especially useful, as it places different levels of dependencies at graduated levels in the graph, as illustrated in Figure 4.

Figure 4

When you select an object and right-click it, there is also an option to Show the SQL Script for that object.

A quick inspection of the SQL scripts (see Figure 5) will tell you which parameters may need adjusting in which objects as a consequence of the new column you are planning to add to the CustomerCards table.

Figure 5

When you're happy you've identified all the impacts that are going to occur as a result of your schema change, you can save a record of the state of the database and its dependencies exactly as they are at the time of the change. Simply click the Copy button in the toolbar, and the current grid diagram will be copied to the clipboard for you to paste wherever you need to save it, for example, in a spreadsheet or in MS Word. The diagram can also be exported for print, either as a PNG image, or as an XML file.

Customizing the Graphical Report

You can make the diagram even easier to understand by selecting and highlighting certain objects such as stored procedures or views. If you would like to exclude objects, there is a Hide option, which will remove the object(s) from the diagram, but still keep them logged within the Objects in Project pane, should you wish to include them in the graphical display at a later time. If, as shown in Figure 6, you would like to categorize your database report, there is a customizable color-coding scheme that can be accessed by right-clicking on the object and then clicking on Properties.

Figure 6