private void ScriptViews()
{
try
{
#region Options
ScriptingOptions so = new ScriptingOptions();
so.IncludeHeaders = false;
// so.NoExecuteAs = true;
so.AllowSystemObjects = true;
so.NoCommandTerminator = false;
#endregion
Server srv = null;
Database db = GetDatabase(ref srv);
srv.SetDefaultInitFields(typeof(View), "IsSystemObject");
UrnCollection urns = new UrnCollection();
foreach (View v in db.Views)
{
// exclude these objects
if (v.IsSystemObject) continue;
if (v.Name.StartsWith("aspnet_")) continue;
urns.Add(v.Urn);
}
string filepath = this.BuildScriptFilePath(ViewDirectory, null);
CreateDirectory(filepath);
StringCollection FileCollection = new StringCollection();
if (urns.Count > 0)
{
DependencyWalker depwalker = new Microsoft.SqlServer.Management.Smo.DependencyWalker(srv);
DependencyTree tree = depwalker.DiscoverDependencies(urns, true);
DependencyCollection depcoll = depwalker.WalkDependencies(tree);
foreach (DependencyCollectionNode dep in depcoll)
{
if (dep.Urn.Type == "View")
{
foreach (View t in db.Views)
{
if (t.Name.ToString() == dep.Urn.GetAttribute("Name").ToString())
{
if (!t.IsEncrypted)
{
string viewFilePath = Path.Combine(filepath, t.Schema + "." + t.Name.ToString() + ".sql");
so.ScriptDrops = true;
StringCollection drop = t.Script(so);
so.ScriptDrops = false;
StringCollection create = t.Script();
StringCollection sc = new StringCollection();
sc.Add("IF EXISTS(SELECT * FROM SYSOBJECTS WHERE XTYPE='V' AND NAME='" + t.Name.ToString() + "')\r\nBEGIN\r\n");
foreach (string s in drop)
{
sc.Add(s);
}
sc.Add("\r\nEND\r\nGO\r\n");
foreach (string s in create)
{
sc.Add(s);
sc.Add("GO\r\n");
}
WriteFile(viewFilePath, sc);
string relativeViewFilePath = this.BuildRelativeFilePath(viewFilePath);
FileCollection.Add(relativeViewFilePath); // We want a relative path here.
WriteStatus("File written: " + relativeViewFilePath);
break;
}
else
{
WriteStatus("WARNING: View is NOT scripted. It was found to be encrypted and cannot be scripted. (" + t.Name + ")");
}
}
}
}
}
} //if urns.Count > 0
string viewListFilePath = this.BuildScriptFilePath(ViewDirectory, ViewsFile);
WriteFile(viewListFilePath, FileCollection);
string relativeViewListFilePath = this.BuildRelativeFilePath(viewListFilePath);
WriteStatus("File written: " + relativeViewListFilePath);
}
catch (Exception err)
{
WriteStatus(Thread.CurrentThread.ThreadState.ToString());
if (Thread.CurrentThread.ThreadState != ThreadState.AbortRequested)
{
WriteStatus("An error may have been encountered. Please check the log file for more details.");
MMDB.Core.MMDBLogFile.Log(err, "ScriptView");
throw new ViewScriptingException("A View scripting exception has been found. Please review the log for more information.", err);
}
}
}
public string ScriptEverything(Database selectedDB, ObjectsToScriptOptions objectsToScript)
{
Microsoft.SqlServer.Management.Smo.Table tbl = null;
StringBuilder sb = new StringBuilder();
objectsToScriptOptions = objectsToScript;
SelectedDB = selectedDB;
ClearVariables();
Console.WriteLine("Scripting tables started at : " + DateTime.Now);
Table[] tbls = new Table[SelectedDB.Tables.Count];
SelectedDB.Tables.CopyTo(tbls, 0);
DependencyTree tree = DBServerConnectionSingleton.Instance.DBScripter.DiscoverDependencies(tbls, true);
DependencyWalker depwalker = new Microsoft.SqlServer.Management.Smo.DependencyWalker();
DependencyCollection depcoll = depwalker.WalkDependencies(tree);
ScriptSchemas();
SQLScript += SQLScriptSchemas;
sb.AppendLine("--================ Start of Table objects script ( " + DateTime.Now + " ) ================");
sb.AppendLine(Environment.NewLine);
foreach (DependencyCollectionNode dep in depcoll)
{
tbl = SelectedDB.Tables[dep.Urn.GetAttribute("Name"), dep.Urn.GetAttribute("Schema")];
ScriptingOptions options = new ScriptingOptions();
options.NoCollation = true;
options.ClusteredIndexes = false;
options.Default = true;
options.DriAll = false;
options.Indexes = false;
options.IncludeHeaders = true;
options.IncludeIfNotExists = true;
options.SchemaQualify = true;
options.DriDefaults = true;
if (!tbl.IsSystemObject)
{
if (objectsToScript.ScriptTables)
{
StringCollection coll = tbl.Script(options);
foreach (string str in coll)
{
sb.Append(str);
sb.Append(Environment.NewLine);
}
sb.AppendLine("GO");
}
Console.WriteLine(tbl.Name);
if (objectsToScript.ScriptIndexes)
{
ScriptIndexes(tbl);
}
if (objectsToScript.ScriptForeignKeys)
{
ScriptForeignKeys(tbl);
}
if (objectsToScript.ScriptTriggers)
{
ScriptTriggers(tbl);
}
}
}
sb.AppendLine("--================ End of Table objects script ( " + DateTime.Now + " ) ================");
sb.AppendLine(Environment.NewLine);
Console.WriteLine("Scripting tables finished at : " + DateTime.Now);
if (objectsToScript.ScriptUserDefinedFunctions)
{
ScriptUserDefinedFunctions(selectedDB);
}
if (objectsToScript.ScriptStoredProcs)
{
ScriptStoredProcs(selectedDB);
}
if (objectsToScript.ScriptViews)
{
ScriptViews(selectedDB);
}
SQLScript += sb.ToString();
SQLScript += SQLScriptFunctions;
return(SQLScript);
}
private void ScriptPartitionFunction()
{
try
{
#region Options
ScriptingOptions so = new ScriptingOptions();
so.IncludeHeaders = false;
// so.NoExecuteAs = true;
so.AllowSystemObjects = true;
so.NoCommandTerminator = false;
#endregion
Server srv = null;
Database db = GetDatabase(ref srv);
UrnCollection urns = new UrnCollection();
foreach (PartitionFunction v in db.PartitionFunctions)
{
// exclude these objects
urns.Add(v.Urn);
}
string filepath = this.BuildScriptFilePath(DatabaseDirectory, null);
CreateDirectory(filepath);
StringCollection FileCollection = new StringCollection();
if (urns.Count > 0)
{
DependencyWalker depwalker = new Microsoft.SqlServer.Management.Smo.DependencyWalker(srv);
DependencyTree tree = depwalker.DiscoverDependencies(urns, true);
DependencyCollection depcoll = depwalker.WalkDependencies(tree);
foreach (DependencyCollectionNode dep in depcoll)
{
if (dep.Urn.Type == "PartitionFunction")
{
foreach (PartitionFunction t in db.PartitionFunctions)
{
if (t.Name.ToString() == dep.Urn.GetAttribute("Name").ToString())
{
string PartitionFunctionFilePath = Path.Combine(filepath, t.Name.ToString() + ".sql");
so.ScriptDrops = true;
StringCollection drop = t.Script(so);
so.ScriptDrops = false;
StringCollection create = t.Script();
StringCollection sc = new StringCollection();
foreach (string s in create)
{
sc.Add(s);
sc.Add("GO\r\n");
}
WriteFile(PartitionFunctionFilePath, sc);
string relativePartitionFunctionFilePath = this.BuildRelativeFilePath(PartitionFunctionFilePath);
FileCollection.Add(relativePartitionFunctionFilePath); // We want a relative path here.
WriteStatus("File written: " + relativePartitionFunctionFilePath);
break;
}
}
}
}
} //if urns.Count > 0
string PartitionFunctionListFilePath = this.BuildScriptFilePath(DatabaseDirectory, PartitionFunctionFile);
WriteFile(PartitionFunctionListFilePath, FileCollection);
string relativePartitionFunctionListFilePath = this.BuildRelativeFilePath(PartitionFunctionListFilePath);
WriteStatus("File written: " + relativePartitionFunctionListFilePath);
}
catch (Exception err)
{
WriteStatus(Thread.CurrentThread.ThreadState.ToString());
if (Thread.CurrentThread.ThreadState != ThreadState.AbortRequested)
{
WriteStatus("An error may have been encountered. Please check the log file for more details.");
MMDB.Core.MMDBLogFile.Log(err, "ScriptPartitionFunction");
throw new PartitionFunctionScriptingException("A Partition Scheme scripting exception has been found. Please read the log for more information.", err);
}
}
}
