Click here to Skip to main content
Click here to Skip to main content

SQL Compare and SQL Packager

, 24 Jun 2004
A simple solution to scripting when sysdepends are damaged

Editorial Note

This article is in the Product Showcase section 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.

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.

 

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

Share

About the Author

Red Gate Software
Red Gate Software Ltd.
United Kingdom United Kingdom
Red Gate Software makes ingeniously simple tools used by over 650,000 Microsoft technology professionals working with .NET (incl. ASP.NET), SQL Server, Azure, and Oracle. More than 100,000 companies use our products, including 93% of the Fortune 100.

Our philosophy is to design highly usable, reliable tools which elegantly solve the problems that developers and database administrators face every day.
Group type: Organisation

4 members

Follow on   Twitter   Google+

Comments and Discussions

 
GeneralSQL server 2000 Database comparison PinmemberLokanatha Reddy20-Dec-06 20:45 
GeneralRe: SQL server 2000 Database comparison PinmemberBrian Donahue2-Jan-07 1:04 
GeneralRe: SQL server 2000 Database comparison PinmemberLokanatha Reddy2-Jan-07 2:57 
GeneralRe: SQL server 2000 Database comparison PinmemberBrian Donahue2-Jan-07 8:00 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150414.1 | Last Updated 25 Jun 2004
Article Copyright 2004 by Red Gate Software
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid