SMO.Scripter: Help Walking a DependencyCollection
-
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 ServerServer 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 ConstructorvwScripter.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 locationif (System.IO.File.Exists(ScriptOutputLoc)){System.IO.File.Delete(ScriptOutputLoc);}try{Database SrcDbCOn = SrcServerCon.Databases[SrcDbName];//Adding UDFs and Vws to Urn CollectionUrnCollection 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 TreeDependencyTree 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){} -
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] -
You can look at this. I used a dependency tree.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply