Click here to Skip to main content
16,016,712 members
Articles / Database Development / SQL Server
Article

SQL Compare and SQL Packager

24 Jun 20043 min read 42.5K   2   4
A simple solution to scripting when sysdepends are damaged

This article is in the Product Showcase section for our sponsors at CodeProject. These articles are intended to provide you with information on products and services that we consider useful and of value to developers.

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.

Image 1

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.

Image 2

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.

Image 3

Now we clear the sysdepends table.

Image 4

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.

Image 5

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.

Image 6

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

Image 7

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.

Image 8

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.

 

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Red Gate Software Ltd.
United Kingdom United Kingdom
Redgate makes ingeniously simple software used by 804,745 IT professionals and counting, and is the leading Microsoft SQL Server tools vendor. Our philosophy is to design highly usable, reliable tools which elegantly solve the problems developers and DBAs face every day, and help them adopt database DevOps. As a result, more than 100,000 companies use products in the Redgate SQL Toolbelt, including 91% of those in the Fortune 100.
This is a Organisation

1 members

Comments and Discussions

 
GeneralSQL server 2000 Database comparison Pin
Lokanatha Reddy20-Dec-06 19:45
Lokanatha Reddy20-Dec-06 19:45 
GeneralRe: SQL server 2000 Database comparison Pin
Brian Donahue2-Jan-07 0:04
Brian Donahue2-Jan-07 0:04 
GeneralRe: SQL server 2000 Database comparison Pin
Lokanatha Reddy2-Jan-07 1:57
Lokanatha Reddy2-Jan-07 1:57 
GeneralRe: SQL server 2000 Database comparison Pin
Brian Donahue2-Jan-07 7:00
Brian Donahue2-Jan-07 7:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.