Total Clarity: Analyzing and Documenting SQL Server

Maybe you've inherited an enormous database with the task of analyzing all its dependencies in order to make urgent changes to the database by the next day. Or maybe you're developing a database and need a plain-as-day overview of your database under construction. In either case, it can often feel that the job in hand is overwhelming. This article discusses two new tools from Red Gate Software — SQL Doc and SQL Dependency Tracker — that can bring dramatic clarity and simplicity to SQL Server database development and maintenance.

A survey carried out by the Software Development Times in March 2006 showed that over half of the respondents carry out analysis of dependencies and track the impact of changes to a database manually, by running hand-rolled scripts. More than 20 percent of the respondents also claimed they had to rely on the memory of the person who first wrote the database in order to have any idea of the knock-on effect of possible changes to be made. Most respondents also reckoned on spending a day or more each time on this tedious job.

As SQL Server increasingly becomes the corporate organization's platform of choice for its flexibility and functionality, ever more companies are rolling out their databases across a greater section of their employees. Yet DBAs and database developers know that more employees with access rights to databases mean more human errors. Scaling up an installation thus brings with it the likelihood of unscheduled, often unintended dependency impacts, crashes and concomitant downtime.

SQL Dependency Tracker, released by Red Gate in the summer, can dramatically reduce the labor overhead by generating a graphical analysis of your database, together with all the dependencies mapped, in a matter of seconds. The graphical map can then be manipulated to view the potential impact of changes across linked databases and across connected servers.

SQL Dependency Tracker uses a proprietary engine that is entirely independent of the sysdepends table. For anyone in a database development and administration team, SQL Dependency Tracker offers a range of useful functions for a variety of database tasks, including:

• Recording database version history.
• Analyzing imminent changes to the schema.
• Mapping a dependency tree from an initial target object.
• Reporting to other team members or upwards through the organization.

Selecting Objects for Analysis

Creating a graphical report with SQL Dependency Tracker is easy. On loading the tool, simply click on the Add Objects button to open a wizard that will invite you to add objects to your project (see Figure 1 below).

Figure 1

A dialog opens, displaying all the locally available SQL Servers and the databases present in each. If you select a database, the lower window will display its constituent objects.

Let's imagine you're a development DBA who needs to add a column to a table so that credit card verification numbers can be stored. The numbers will need to be stored along with the credit card numbers and the customer names. Adding this column to one table will mean that changes have to be made to dependent tables; and these tables may not necessarily be in the same database.

In this example, we'll start by selecting just one table — the CustomerCards table — from a database called NorthCards, where customer credit card details are stored.

Hitting the Add Selection to Project button launches the layout engine, and your selected object will be mapped, along with any connected objects. In the screen shot below, we can see that the CustomerCards object has been added to the layout, with dependent objects grouped around it. An externally referenced object from another database (indicated by the black arrow in Figure 2) has also been automatically inserted. The Customers table from the NorthWind database has been added because it is used by two stored procedures — sp_getnamesforcard and sp_createcustomercard — that also use our original CustomerCards table.

Figure 2

The antenna to the left of the external reference in Figure 2 shows that there are further objects used by this reference. Right-clicking the external reference will open up a menu to allow you to add those objects. Rolling over the object will also show a tooltip with the names of those objects.