public static void Test() { System.Text.StringBuilder sb = new System.Text.StringBuilder(); using (System.Data.SqlClient.SqlConnection con = (System.Data.SqlClient.SqlConnection) //SqlFactory.GetConnection() SqlFactory.LocalConntection ) { Microsoft.SqlServer.Management.Common.ServerConnection sc = new Microsoft.SqlServer.Management.Common.ServerConnection(con); lock (con) { sc.Connect(); Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(sc); Microsoft.SqlServer.Management.Smo.Database database; // = new Microsoft.SqlServer.Management.Smo.Database(); //database = server.Databases["redmine"]; //string schemaName = @"dbo"; //string tableName = @"issues"; database = server.Databases["COR_Basic_Demo_V4"]; string schemaName = @"dbo"; string tableName = @"T_Benutzer"; Microsoft.SqlServer.Management.Smo.Table table = database.Tables[tableName, schemaName]; System.Collections.Specialized.StringCollection result = table.Script(); // table.Script(new ScriptingOptions() { ScriptForAlter = true }); Microsoft.SqlServer.Management.Smo.StoredProcedure proc = database.StoredProcedures["procname", "schema"]; // proc.Script(new ScriptingOptions() { ScriptForAlter = true }); // string alterText = proc.ScriptHeader(true) + proc.TextBody; foreach (string line in result) { sb.AppendLine(line); } // Next line sc.Disconnect(); } // End Lock con } // End Using con using (System.IO.FileStream fs = System.IO.File.OpenWrite(@"d:\testScriptSAQl.sql")) { using (System.IO.TextWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.UTF8)) { sw.Write(sb.ToString()); sw.Flush(); fs.Flush(); } // End Using sw } // End Using fs } // End Sub
/// <summary> /// Compare 2 schema Tables /// </summary> /// <param name="sp1">your first smo Table</param> /// <param name="sp2">your second smo Table</param> /// <param name="checkComments">True if you want to compare comments</param> /// <param name="checkBrackets">True if you want to compare scripts with brackets</param> /// <param name="ignoreCaseSensitive">True if you want to ignore Case Sensitive. False if Case sensitive</param> /// <returns></returns> public static KMOCompareInfo CompareSchema(this smo.Table t1, smo.Table t2, bool checkComments = false, bool checkBrackets = false, bool ignoreCaseSensitive = true) { smo.ScriptingOptions so = new smo.ScriptingOptions(); so.DriAll = true; string s1 = String.Join(Environment.NewLine, t1.Script(so).Cast <String>().Select(s => s.ToString()).AsEnumerable()); string s2 = String.Join(Environment.NewLine, t2.Script(so).Cast <String>().Select(s => s.ToString()).AsEnumerable()); string message = string.Empty; if (t1.Columns.Count != t2.Columns.Count) { message += "Tables don't have the same column count. "; } foreach (smo.Column c1 in t1.Columns) { smo.Column c2 = t2.Columns[c1.Name]; if (c2 == null) { message += "The column " + c1.Name + " doesn't exist in the second table. "; } else if (c2.DataType.Name != c1.DataType.Name || c2.DataType.NumericPrecision != c1.DataType.NumericPrecision || c2.DataType.NumericScale != c1.DataType.NumericScale) { message += "The column " + c1.Name + " doesn't have the same type. "; } } foreach (smo.Column c2 in t2.Columns) { smo.Column c1 = t1.Columns[c2.Name]; if (c1 == null) { message += "The column " + c2.Name + " doesn't exist in the first table. "; } } if (message != string.Empty) { return(new KMOCompareInfo { IsIdentical = false, Message = message, Script1 = s1, Script2 = s2 }); } if (KMOCompareHelper.CompareScript(s1, s2, ignoreCaseSensitive, checkComments, checkBrackets)) { return(new KMOCompareInfo { IsIdentical = true, Message = string.Empty, Script1 = s1, Script2 = s2 }); } else { return(new KMOCompareInfo { IsIdentical = false, Message = "Script difference", Script1 = s1, Script2 = s2 }); } }
private void GenerateScript() { this._scriptBody = String.Empty; try { SMO.Database smoDb = _context.SmoServer.Databases[this._dbName]; if (smoDb == null) { this._scriptBody = String.Format( "Database '{0}' not found. No T-SQL script generated.", this._dbName); Log.Warning(this._scriptBody); return; } SMO.Table smoTable = smoDb.Tables[this._tableName, this._schemaName]; if (smoTable == null) { this._scriptBody = String.Format( "Table '{0}' not found in Schema '{1}' in Database {2}. No T-SQL script generated.", this._tableName, this._schemaName, this._dbName); Log.Warning(this._scriptBody); return; } StringCollection scripts = smoTable.Script(); foreach (var script in scripts) { this._scriptBody += script; } } catch (Exception e) { this._scriptBody = String.Format( "Error generating script for Table '{0}' in Schema '{1}' in Database '{2}'\n\n{3}", this._tableName, this._schemaName, this._dbName, e.ToString()); Log.Error(this._scriptBody); } }
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); }
public void ExtractScript(SQLObjectName oname, SQLScripts SQLScriptsCollection, bool Verbose) { // Store extracted scripts. Each extract may include multiple scripts. StringCollection OutputScripts = new StringCollection(); string FinalScript = String.Empty; switch (oname.ObjectType) { case SQLObjectType.Table: Microsoft.SqlServer.Management.Smo.Table scriptTable = connDatabase.Tables[oname.Name, oname.Schema]; if (scriptTable != null) { StringCollection CheckScripts = new StringCollection(); // Store scripts to be checked String TableScript = String.Empty; // Stores individual script for output collection. ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.DriAll = true; scriptOptions.Statistics = true; scriptOptions.ClusteredIndexes = true; scriptOptions.NonClusteredIndexes = true; scriptOptions.DriAllConstraints = true; scriptOptions.WithDependencies = false; // Get table and related scripts CheckScripts = scriptTable.Script(scriptOptions); // Check scripts so we can remove invalide SQL 2012 column store options from the script. // (Why doesn't the target server version remove this? // This is a crappy place to do this, and it's version specific. // Need to implement the new versioning code to check target model. foreach (string CheckCCI in CheckScripts) { if (CheckCCI.Contains(", DATA_COMPRESSION = COLUMNSTORE")) { TableScript = CheckCCI.Replace(", DATA_COMPRESSION = COLUMNSTORE", ""); } else { TableScript = CheckCCI; } // Add the script into the OutputScripts collection. OutputScripts.Add(TableScript); } } break; case SQLObjectType.View: Microsoft.SqlServer.Management.Smo.View scriptView = connDatabase.Views[oname.Name, oname.Schema]; if (scriptView != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.DriAll = true; scriptOptions.ClusteredIndexes = true; scriptOptions.NonClusteredIndexes = true; scriptOptions.WithDependencies = false; // Must specify tables seperatly, but safer to do so // to avoid having duplicate table names in the model. OutputScripts = scriptView.Script(scriptOptions); } break; case SQLObjectType.StoredProcedure: Microsoft.SqlServer.Management.Smo.StoredProcedure scriptStoredProcedure = connDatabase.StoredProcedures[oname.Name, oname.Schema]; if (scriptStoredProcedure != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.WithDependencies = false; OutputScripts = scriptStoredProcedure.Script(scriptOptions); } break; case SQLObjectType.PartitionScheme: { Microsoft.SqlServer.Management.Smo.PartitionScheme scriptPScheme = connDatabase.PartitionSchemes[oname.Name]; if (scriptPScheme != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.WithDependencies = false; OutputScripts = scriptPScheme.Script(scriptOptions); } } break; case SQLObjectType.PartitionFunction: { Microsoft.SqlServer.Management.Smo.PartitionFunction scriptPFunction = connDatabase.PartitionFunctions[oname.Name]; if (scriptPFunction != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.WithDependencies = false; OutputScripts = scriptPFunction.Script(scriptOptions); } } break; case SQLObjectType.Schema: { Microsoft.SqlServer.Management.Smo.Schema scriptSchema = connDatabase.Schemas[oname.Name]; if (scriptSchema != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.WithDependencies = false; scriptOptions.ScriptOwner = true; // This includes the "with authorize" part. OutputScripts = scriptSchema.Script(scriptOptions); } } break; case SQLObjectType.FileGroup: { Microsoft.SqlServer.Management.Smo.FileGroup scriptFG = connDatabase.FileGroups[oname.Name]; if (scriptFG != null) { // Create manual script for FileGroups OutputScripts.Add("ALTER DATABASE [$(DatabaseName)] ADD FILEGROUP " + scriptFG.Name); } } break; case SQLObjectType.User: { Microsoft.SqlServer.Management.Smo.User scriptUser = connDatabase.Users[oname.Name]; if (scriptUser != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.WithDependencies = false; OutputScripts = scriptUser.Script(scriptOptions); } } break; case SQLObjectType.Function: Microsoft.SqlServer.Management.Smo.UserDefinedFunction userDefinedFunction = connDatabase.UserDefinedFunctions[oname.Name, oname.Schema]; if (userDefinedFunction != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.WithDependencies = false; OutputScripts = userDefinedFunction.Script(scriptOptions); } break; } if (OutputScripts.Count > 0) { Console.WriteLine("Extracted SQL script: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name)); foreach (string script in OutputScripts) { // Add the script to the script collection. FinalScript = FinalScript + script + Environment.NewLine + "GO" + Environment.NewLine; } } else { Console.WriteLine("Warning - Could not retrieve: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name)); FinalScript = String.Empty; } if (FinalScript != String.Empty) { SQLScriptsCollection.Scripts.Add(FinalScript); } else { SQLScriptsCollection.MissingScripts.Add("Missing SQL object: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name)); } // Print script(s) if verbose is on. if (Verbose) { Console.WriteLine(FinalScript); } }