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