Total Clarity: Analyzing and Documenting SQL Server

Parallel Text-Based Tracking

If you don't need a graphical report to show dependencies, there is also the option of a text-based display of all relevant objects in the database. There are two key panes on the right-hand side of the SQL Dependency Tracker window. Top right is the Objects in Project pane, itemizing all the objects currently added to the project. Clicking on an object, such as the Orders Qry view in the screen shot above, populates the bottom-right Dependencies pane with all the objects that both use and are used by that object. This kind of tracking can be useful as a quick way of checking related objects in a database at a glance.

Even more convenient for this task is SQL Doc, a newly released tool produced by Red Gate Software that documents databases in an easy-to-read HTML format. SQL Dependency Tracker and SQL Doc can be used as complementary tools for gaining an in-depth view of databases and for tracking dependencies and changes.

Documenting Databases in Detail

If you want to take a closer look at your database, SQL Doc will help you drill down in seconds to produce an in-depth HTML report of its contents. This can be of particular use if a coworker or manager needs a text-based overview of a database.

SQL Doc is compatible with both SQL Server 2000 and 2005. The development focused on simplicity of use, and the results are evident in the UI from the moment you connect your server. We can see in Figure 7 how easy it is to select a database and any relevant entities in the left-hand pane. The selected database and entities can then be previewed in the large right-hand pane before we click the bottom right-hand button to generate documentation.

In the example in Figure 7, we will use NorthWind, the same database that we analyzed with SQL Dependency Tracker.

Figure 7

Creation of the documentation is smooth. In its neatly categorized format, it can also be easily navigated. Documentation results are opened in a separate Web browser window. Figure 8 shows the documentation of the Tables in the NorthWind database. The different primary keys are quickly identifiable by their color-coded key icons.

Figure 8

Internal links to each object within the table allow you to investigate deeper. If we click on the Customer Demographics object, as seen in Figure 9, we can view its properties, columns, indexes, permissions and a SQL script to recreate the object.

Figure 9

SQL Doc can be tailored to suit your purposes: you can produce HTML documentation with or without frames; edit descriptions of selected objects and SQL Doc will update the relevant extended properties across your database. There is also the facility to insert copyright links, text or author references at the end of every page.

Cool Tools … So What Next?

After using SQL Dependency Tracker and SQL Doc to thoroughly analyze the contents of your database, you can deploy the graphical reports and HTML documentation pages to help simplify a number of processes, including version control and auditing for productivity and security. With this detailed knowledge, you can make better-informed decisions when you use SQL Compare and SQL Data Compare to push changes upstream from development to your test and live databases.

More product and download information about SQL Dependency Tracker and SQL Doc can be found at the following Web pages:

http://www.red-gate.com/products/SQL_Dependency_Tracker/index.htm?utm_source=ssp&utm_medium=advertorial&utm_term=SQLDepTrack_SQLDoc

and

http://www.red-gate.com/products/SQL_Doc/index.htm?utm_source=ssp&utm_medium=advertorial&utm_term=SQLDepTrack_SQLDoc.