|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Yesterday @ 6:36 AM
Points: 37,
Visits: 153
|
|
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) {
}
|
|
|
|