public NameValueCollection GetTables()
{
NameValueCollection nv = new NameValueCollection();
foreach (SMO.Table t in _db.Tables)
{
if (!t.IsSystemObject)
{
SMO.ScriptingOptions so = new SMO.ScriptingOptions();
so.DriAll = true;
StringCollection sc = t.Script(so);
string newFile = "";
foreach (string s in sc)
{
newFile += s + "\r\n";
}
nv.Add(t.Schema + "." + t.Name, newFile);
}
}
return(nv);
}
public NameValueCollection GetLogins()
{
NameValueCollection nv = new NameValueCollection();
foreach (SMO.Login l in _srv.Logins)
{
if (!l.IsSystemObject)
{
SMO.ScriptingOptions so = new SMO.ScriptingOptions();
so.LoginSid = true;
StringCollection sc = l.Script(so);
string newFile = "";
foreach (string s in sc)
{
newFile += s + "\r\n";
}
newFile = System.Text.RegularExpressions.Regex.Replace(newFile, "PASSWORD=N'.*?', SID", "PASSWORD=N'******', SID", System.Text.RegularExpressions.RegexOptions.Singleline);
nv.Add(l.Name, newFile);
}
}
return(nv);
}
/// <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
});
}
}
/// <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
});
}
}
} // End Sub
public static void SlightlyMoreComplete()
{
Microsoft.SqlServer.Management.Smo.Server srv = new Microsoft.SqlServer.Management.Smo.Server();
// really you would get these from config or elsewhere:
srv.ConnectionContext.Login = "******";
srv.ConnectionContext.Password = "******";
srv.ConnectionContext.ServerInstance = "ServerName";
string dbName = "DatabaseName";
Microsoft.SqlServer.Management.Smo.Database db = new Microsoft.SqlServer.Management.Smo.Database();
db = srv.Databases[dbName];
System.Text.StringBuilder sb = new System.Text.StringBuilder();
foreach (Microsoft.SqlServer.Management.Smo.Table tbl in db.Tables)
{
Microsoft.SqlServer.Management.Smo.ScriptingOptions options = new Microsoft.SqlServer.Management.Smo.ScriptingOptions();
options.ClusteredIndexes = true;
options.Default = true;
options.DriAll = true;
options.Indexes = true;
options.IncludeHeaders = true;
System.Collections.Specialized.StringCollection coll = tbl.Script(options);
foreach (string str in coll)
{
sb.Append(str);
sb.Append(System.Environment.NewLine);
} // Next str
} // Next tbl
System.IO.StreamWriter fs = System.IO.File.CreateText("c:\\temp\\output.txt");
fs.Write(sb.ToString());
fs.Close();
} // End Sub SlightlyMoreComplete
public NameValueCollection GetTables()
{
NameValueCollection nv = new NameValueCollection();
foreach (SMO.Table t in _db.Tables)
{
if (!t.IsSystemObject)
{
SMO.ScriptingOptions so = new SMO.ScriptingOptions();
so.DriAll = true;
StringCollection sc = t.Script(so);
string newFile = "";
foreach (string s in sc)
{
newFile += s + "\r\n";
}
nv.Add(t.Schema + "." + t.Name, newFile);
}
}
return nv;
}
public NameValueCollection GetLogins()
{
NameValueCollection nv = new NameValueCollection();
foreach (SMO.Login l in _srv.Logins)
{
if (!l.IsSystemObject)
{
SMO.ScriptingOptions so = new SMO.ScriptingOptions();
so.LoginSid = true;
StringCollection sc = l.Script(so);
string newFile = "";
foreach (string s in sc)
{
newFile += s + "\r\n";
}
newFile = System.Text.RegularExpressions.Regex.Replace(newFile, "PASSWORD=N'.*?', SID", "PASSWORD=N'******', SID", System.Text.RegularExpressions.RegexOptions.Singleline);
nv.Add(l.Name, newFile);
}
}
return nv;
}
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);
}
