Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SMO.Scripter: Help Walking a DependencyCollection Expand / Collapse
Author
Message
Posted Wednesday, November 14, 2012 1:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:06 PM
Points: 38, Visits: 160
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)
{



}
Post #1384841
Posted Thursday, July 18, 2013 8:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:25 AM
Points: 249, Visits: 529
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
Post #1475070
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse