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 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)
{
}
-
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