SQL Compare and SQL Packager

This is a showcase review for our sponsors at CodeProject. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers.

The problem

Damaged dependency information is a common problem when a database has a reasonable number of objects. This article explains how to use Red Gate's SQL Compare and SQL Packager to automatically script a database even when the dependency information contained in the sysdepends table is missing.

The background

SQL Server is designed so that dependency information is recorded at the moment of object creation, alteration and destruction. This method means there is virtually zero overhead during script creation. On a large database with many dependencies, this provides a significant performance advantage.

The downside to this approach is that it can introduce dependency errors through three avenues:

1. when an object in a dependency chain is deleted then re-created;
2. when an object in a dependency chain is renamed; and
3. when there are bugs in SQL Server.

When the sysdepends table is corrupted, scripts are likely to fail if run by SQL Server. In this situation, the usual work-around is to get SQL Server to script the database and then re-order the object scripts in a way you hope will preserve dependencies.

Entering "scripting dependencies in SQL Server" in a search engine will quickly return a slew of stories where people are struggling with this very task. If your database has more than a few dependencies, it is difficult to work out in which order you should present your scripts to SQL Server when re-creating your database.

The solution

One of the lesser-known attributes of Red Gate's SQL Compare is the ability to script objects in the correct order without relying on the dependency information in sysdepends.

As an extreme example of how SQL Compare copes with missing or incorrect sysdepends information, we will delete all the dependency information in a database. Obviously this would never happen in real life, but it's an excellent demonstration of how SQL Compare works perfectly with damaged information.

First, we create two databases � damaged and blank.

Then, we create three objects with a simple chain of dependencies � a table, a view which depends on that table, and then a second view which depends on the first view.

Behind the scenes our sysdepends table has been updated.

Next, we change a fundamental permission on SQL Server. Do not do this on any server with important information on it.

Now we clear the sysdepends table.

Next, we re-configure SQL Server to work properly and see if there is anything in sysdepends. As you can see, sysdepends is now empty.

Our database is now damaged beyond repair and, until now, it would have been impossible to script this database. But with the new version of SQL Compare it will work perfectly.

We start by comparing the damaged database with the blank database.

The three objects are present in the damaged database. Let's create a script that will place those objects in the blank database.

Looking at this summary you can see that, despite the empty sysdepends table, these three objects will be created in the correct order. Let�s now run this script and re-compare.

The blank database now has those three objects and is working fine.

Conclusion

It is very easy for sysdepends to get corrupted through renaming, deleting and recreating objects, or through bugs in SQL Server. SQL Compare solves this problem easily during scripting.

Red Gate's new product, SQL Packager, goes one step further, fixing damaged dependencies without the intermediate step of creating a blank database.

Visit Red Gate�s website for more information and to download your fully-functional, free trial.