ApexSQL Clean: Impact Analysis and Graphical Dependency Viewer tool

From the options menu, you can customize each object type’s shape, shape color, and text color.

The Object Browser

You have probably noticed the “Object Browser” panel on the right-hand side of the screen of the Dependency Viewer. You are actually able to navigate through the Dependency Viewer in two ways. You can either use the diagram itself or you can use the Object Browser. Selection of one object in either one will be propagated to the other one, so that both are always in synch with each other and you don’t lose yourself in larger databases with many objects.

The Overview Control

The very same purpose serves the “Overview Control”, which you can see just above the Object Browser. Since I don’t use a very complex database for this paper, the Overview Control isn’t truly necessary and you might decide to ignore it for small to medium-sized databases. But what looks like an overkill for such databases, quickly becomes essential when working on larger databases with hundreds or even thousands of objects. To illustrate that point, I once again, deliberately borrow a screen shot from the ApexSQL Clean Online Manual.

To display a certain region in the diagram view, just click on that region in the Overview Control.

All in all, the Dependency Viewer is an indeed powerful tool which you can use to visually display the dependencies among the objects of a database.

Drops Generation Wizard

Along with the Dependency Viewer, I’ve also mentioned above the “Drops Generation Wizard”, which I now want to return to and have a closer look at this functionality.

The whole purpose of the ApexSQL Clean tool is to identify unused objects in your database that can safely be removed without breaking any functionality. So, naturally, the ApexSQL Clean needs to have some way to generate a script that includes the DROP statements by which those offending objects are removed. This is done via the Drops Generation Wizard. Or, alternatively from the context menu of the main grid. However, for this operation I prefer the Wizard, as it offers more options to fine-tune the output.

Here on the first page of the wizard you can choose, whether transactional handling should be included in the script, so that in any case of failure the operation is rolled-back. Also, you can include a BACKUP statement of the database prior to the DROP operation(s), so that you are able to restore the database to its most recent state before you’ve started the DROP operation. You would, of course, backup the database prior to DROP statements anyway, but the point that the wizard offers this option helps you “remembering” that you SHOULD always have a recent backup prior to any sort of DROP object operation.

As you can see, the second page offers you some options to customize the output script.

You certainly will want to include the script header. And, at this point, we close a circle. When I explained the Options dialog of the main application window, I mentioned, that you should enter, at least, the author name. The information entered there will be included in the output here, when you check the “Script Header” box. Since I suspect, one would want to keep the drop script, at least for some time, I think it is a good idea to be able to clearly identify the person responsible for the script. I for one would print out the script with the header included and keep it for my records.

On the “Object Selection” page you can finally select, which objects are to be included in the DROP script. By default, all unreferenced objects are pre-checked. Here you have another chance to review your selection closely and only pick those objects you want.


Leave a comment

Your email address will not be published.