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