SQL Server SMO: drop multiple objects with dependencies

Question

Given a set of objects (tables, views, procs, ...) I want to drop all of them and all objects that depend on them.

This requires executing the drop operations in dependency order for both the objects that I want to drop as well as all dependent objects.

How can this be done with SQL Server Management Objects (SMO) for .NET in a clean and elegant way?

(Background for question: The set of objects to drop is programatically generated and not predictable. I'm building an internal tool.).

Dobb

Be forewarned: it is possible to have dependency cycles in a SQL Server Database. – Doctrinal

Answer 1

I would look into the DependencyWalker SMO class - http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.dependencywalker_members(v=sql.90).aspx

Basically you create one of these, call DiscoverDependencies() with a list of Urns/SqlSmoObjects, and get a DependencyTree back. You can then pass this DependencyTree object to the WalkDependencies() method on the DependencyWalker object, and get back a linear list of dependency, with which you can do what you need.

Here is a sketch how a method to delete an arbitrary set of tables including everything that is being referenced by them could look like:

using (var conn = TestConnection.OpenSqlConnectionForTest())
{
    var db = SmoHelpersTest.GetDatabaseSmoObject(conn);

    var smoTables = (from Table t in db.Tables
                        where t.Name.EndsWith("-XXX")
                        select t).ToList();

    var dependencyWalker = new DependencyWalker(db.Parent);
    var dependencyTree = dependencyWalker.DiscoverDependencies(smoTables.Cast<SqlSmoObject>().ToArray(), DependencyType.Parents);
    var dependencyCollection = dependencyWalker.WalkDependencies(dependencyTree);

    foreach (DependencyCollectionNode dependencyCollectionNode in dependencyCollection.Reverse())
    {
        Console.WriteLine(dependencyCollectionNode.Urn);
        var smoObject = db.Parent.GetSmoObject(dependencyCollectionNode.Urn);
        ((dynamic)smoObject).Drop();
    }
}

Eurythermal