SMO.Scripter: Help Walking a DependencyCollection

  • zlthomps

    Default port

    Points: 1480

    Hi there I am working on automating the process of scripting creates for our SQL Views and object dependencies of the views (namely functions in this case). I don't care about table dependencies. I was able to easily script the objects not in dependency order, but I am struggling with walking the dependency tree in my code.

    Here is where I am so far. You can see in the last loop (blank) that I am struggling. Would someone be so kind as to point me in the appropriate direction?

    //Trying to Connect to Source Database and Server

    Server SrcServerCon = new Server(SrcServer);

    SrcServerCon.ConnectionContext.LoginSecure = Convert.ToBoolean(isWindowsAuth);

    try

    {

    if (SrcServerCon.ConnectionContext.LoginSecure == false)

    {

    SrcServerCon.ConnectionContext.Login = SQLAuthLogin;

    SrcServerCon.ConnectionContext.Password = SQLAuthPword;

    }

    }

    catch (Exception sqlConnect)

    {

    using (System.IO.StreamWriter exAtvws = new System.IO.StreamWriter(Logger, true))

    {

    exAtvws.WriteLine("SQLConnect - " + DateTime.Now.ToString() + "-" + sqlConnect.Message );

    }

    Environment.Exit(1);

    }

    Scripter vwScripter = new Scripter(SrcServerCon);

    //Initiating Scripter Constructor

    vwScripter.Options = new ScriptingOptions();

    vwScripter.Options.ScriptDrops = true;

    vwScripter.Options.IncludeIfNotExists = true;

    vwScripter.Options.SchemaQualify = true;

    vwScripter.Options.WithDependencies = true;

    vwScripter.Options.NoCollation = true;

    //Deleteing the File if it exists in the target location

    if (System.IO.File.Exists(ScriptOutputLoc))

    {

    System.IO.File.Delete(ScriptOutputLoc);

    }

    try

    {

    Database SrcDbCOn = SrcServerCon.Databases[SrcDbName];

    //Adding UDFs and Vws to Urn Collection

    UrnCollection udfobjs = new UrnCollection();

    foreach (UserDefinedFunction udf in SrcDbCOn.UserDefinedFunctions)

    {

    if (!udf.IsSystemObject)

    {

    udfobjs.Add(udf.Urn);

    }

    }

    foreach (View views in SrcDbCOn.Views)

    {

    if (!views.IsSystemObject)

    {

    udfobjs.Add(views.Urn);

    }

    }

    //Creating Dependency Tree

    DependencyTree dtree = vwScripter.DiscoverDependencies(udfobjs, true);

    DependencyWalker dwalker = new DependencyWalker();

    DependencyCollection dcollect = dwalker.WalkDependencies(dtree);

    using (System.IO.StreamWriter FxscriptsToFile = new System.IO.StreamWriter(ScriptOutputLoc, true))

    {

    foreach (DependencyCollectionNode dcoln in dcollect)

    {

    }

  • thava

    SSCrazy

    Points: 2275

    use the Urn.Type for your requirement

    foreach (DependencyCollectionNode dcoln in dcollect)

    {

    if( dcoln.urn.Type=="View")

    -- do what you want

    }

    Every rule in a world of bits and bytes, can be bend or eventually be broken
    MyBlog About Common dialog control
    A Visualizer for viewing SqlCommand object script [/url]

  • Ben Miller

    SSCrazy

    Points: 2900

    You can look at this. I used a dependency tree.

    http://sqlblog.com/blogs/ben_miller/archive/2007/10/18/scripting-tables-views-and-data-using-smo-part-3.aspx



    Ben Miller
    Microsoft Certified Master: SQL Server, SQL MVP
    @DBAduck - http://dbaduck.com

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply