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