/// <summary> /// Compare 2 ddl stored procedures /// </summary> /// <param name="sp1">your first smo procedure</param> /// <param name="sp2">your second smo procedure</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.StoredProcedure sp1, smo.StoredProcedure sp2, bool checkComments = false, bool checkBrackets = false, bool ignoreCaseSensitive = true) { smo.ScriptingOptions so = new smo.ScriptingOptions(); so.DriAll = true; string s1 = String.Join(Environment.NewLine, sp1.Script(so).Cast <String>().Select(s => s.ToString()).AsEnumerable()); string s2 = String.Join(Environment.NewLine, sp2.Script(so).Cast <String>().Select(s => s.ToString()).AsEnumerable()); string message = string.Empty; 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 }); } }
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); } }
public TSQL.StoredProcedure Generate(String ProcedureRepositoryPath) { TSQL.StoredProcedure procedure = null; if (CheckRequiredObjects()) { procedure = new Microsoft.SqlServer.Management.Smo.StoredProcedure(this._database, "ssp_" + this._table.Name, _table.Schema); // Set the text mode to false and then set other properties; procedure.TextMode = false; #region [ Operation 1 - Search by Any Criteria ] // Add Parameters; System.Text.StringBuilder SelectSql = new StringBuilder(); SelectSql.AppendLine("SELECT "); int Count = _table.Columns.Count - 1; int Index = 0; while (Index <= Count) { MemberGraph mGraph = new MemberGraph(_table.Columns[Index]); TSQL.StoredProcedureParameter parameter = new Microsoft.SqlServer.Management.Smo.StoredProcedureParameter(procedure, "@" + mGraph.PropertyName(), _table.Columns[Index].DataType); parameter.DefaultValue = "NULL"; procedure.Parameters.Add(parameter); // ntext Data types cna not be searched if (!(mGraph.SqlColumn.DataType.SqlDataType == TSQL.SqlDataType.NText)) { if (Index == Count) { SelectSql.AppendLine("[" + _table.Columns[Index].Name + "]"); } else { SelectSql.AppendLine("[" + _table.Columns[Index].Name + "],"); } } Index = Index + 1; } SelectSql.AppendLine("FROM " + _table.Name); SelectSql.AppendLine("WHERE "); Count = _table.Columns.Count - 1; Index = 0; while (Index <= Count) { MemberGraph mGraph = new MemberGraph(_table.Columns[Index]); String ParameterName = "@" + mGraph.PropertyName(); if (!(mGraph.SqlColumn.DataType.SqlDataType == TSQL.SqlDataType.NText)) { if (Index == 0) { // This is the last column if (_table.Columns[Index].DataType.SqlDataType == Microsoft.SqlServer.Management.Smo.SqlDataType.Text) { SelectSql.AppendLine("(" + ParameterName + " IS NULL OR " + _table.Columns[Index].Name + " LIKE " + ParameterName + ")"); } else { SelectSql.AppendLine("(" + ParameterName + " IS NULL OR " + _table.Columns[Index].Name + " =" + ParameterName + ")"); } } else { SelectSql.AppendLine("AND"); if (_table.Columns[Index].DataType.SqlDataType == Microsoft.SqlServer.Management.Smo.SqlDataType.Text) { SelectSql.AppendLine("(" + ParameterName + " IS NULL OR " + _table.Columns[Index].Name + " LIKE " + ParameterName + ")"); } else { SelectSql.AppendLine("(" + ParameterName + " IS NULL OR " + _table.Columns[Index].Name + " = " + ParameterName + ")"); } } } Index = Index + 1; } SelectSql.AppendLine("\n"); #endregion #region [ Build Script ] string totalScript = SelectSql.ToString(); procedure.TextBody = totalScript; TSQL.ScriptingOptions Options = new Microsoft.SqlServer.Management.Smo.ScriptingOptions(); StringBuilder sbSql = new StringBuilder(); foreach (var s in procedure.Script()) { sbSql.AppendLine(s); } #endregion File.WriteAllText(ProcedureRepositoryPath + @"\" + procedure.Name + ".sql", sbSql.ToString()); } return(procedure); }