Andy and I have complained about sysdepends for a long time, especially at SSC. In working on an infrequent basis on any database, it is always difficult to remember which objects refer to which other ones, the various dependencies, and more. And the larger the database in terms of numbers of objects, the worse this problem becomes.
Now the SQLServerCentral.com databases aren't that large, but there are quite a few tables, over a hundred and when you add in a few hundred stored procedures and some views, this is a fairly complicated system. And since we do not develop or make changes on a regular basis, it becomes difficulty to reliably be sure that a table alter will not affect some other object negatively. Especially when there are three of us working in different places at different times. Yes, I know documentation solves all this, but documentation is hard and unless it's automated, you cannot be sure it is up to date.
The sysdepends table is supposed to handle this for you, keeping track of which objects depend on which other ones. However there are a few flaws with this system, many if which have been documented, complained about, and discussed to no end. At least, not as long as the process doesn't get fixed. I won't go into the details here, but suffice it to say that relying on sysdepends isn't great for job security.
Andy and I toyed with the idea of writing some type of software to figure this out and were brainstorming at PASS last year when Simon Galbraith from Red Gate software mentioned that they had solved this problem for their SQLCompare product. We've both used SQLCompare and it works great, but that doesn't necessarily help me when I need to determine the dependency tree. We talked with him about pulling the code out into a separate product that would allow people to "fix" their sysdepends table and find the relationships between objects.
And we were slightly successful. Announcing the Red-Gate Software
SQL Dependency Viewer, a new utility that will allow you to graphically view the objects that depend on other objects. This small, FREE utility will connect to a database and generate a view of all the objects, of all types, and show the various relationships between them. Clicking on a particular object will highlight all of the dependent objects and their types. At least we're announcing the Beta here :)
I got an early look at this tool and I think it's great. There are definitely a few "wish list" items that I've asked for, but out of the ether, with a limited impact on your budget, this is a great tool to use when working on refactoring your databases. Take a moment to check it out and send your feedback in as well.
The various objects are color coded and include many new types that are in SQL Server 2005 such as CLR data types, so this is ready for the next version of SQL Server right away. It does require the .NET framework (1.1), but it's a fairly lightweight utility and it has already garnered a spot on my desktop.
Download it today and see what you think.
Steve Jones
