Problem Summary:
We have a set of databases on multiple servers that "should" all have the same SQL objects. Over the years, our developers have added/modified objects in various databases such that they do not match anymore. I need to obtain a list of all SQL objects (tables, views, stored procedures, user defined functions) from multiple databases on multiple servers that are exactly the SAME. (later to get the list of unique items and later a list of modified items). My current solution works but is pretty slow. I wanted to know if there was a better existing alternative but I can't find one.
Current Solution:
For now I have been using SMO in C# to get the urns of all objects and script them. When I attempt to script them 1 object at a time, the process is slow (lots of calls to the server). If I try to script them by packing their urns into an array, the process is faster but I just get an Enumerable or StringCollection of the resulting scripts without organization as to which object the script came from, etc. What would be a better way to approach this (I know of existing tools such as ApexSQL or Red-Gate, they are out of the question for the moment). My current solution is to group them up by names (and split by server) and script them in those smaller by-name batches.
Excuse my current code, I've been all over the place trying different methods. Maybe there is a solution that doesn't even need to analyse the code. Two things to note:
- I have a class called SqlObjectInfo which stores just some basic info on each object such as: Name, Server, DB, Schema, Type, Urn
- items is a SqlObjectInfoCollection which is a class which contains a list of SqlObjectInfo plus some helping functions to add objects from servers and databases. Filling this collection with all of the SqlObjectInfo's is fast so thats not the problem.
//Create DataTable
var table = new DataTable("Equal Objects");
table.Columns.Add("Name");
table.Columns.Add("Type");
//Create DataRows
int dbCount = items.SqlObjects.GroupBy(obj => obj.Database).Count();
DMP dmp = DiffMatchPatchModule.Default;
var rows = new List<DataRow>();
foreach (IGrouping<string, SqlObjectInfo> nameGroup in items.SqlObjects.GroupBy(obj => obj.Name))
{
var likeNamedObjs = nameGroup.ToList();
if (likeNamedObjs.Count != dbCount)
{
continue; //object not in all databases
}
//Script Objects
var rawScripts = new List<string>();
bool scriptingSucceeded = true;
foreach (IGrouping<Server, SqlObjectInfo> serverGroup in nameGroup.GroupBy(obj => obj.Server))
{
Server server = serverGroup.Key;
Urn[] urns = serverGroup.Select(obj => obj.Urn).ToArray();
var scripter = new Scripter(server)
{
Options = items.ScriptingOptions
};
IEnumerable<string> results;
try
{
results = scripter.EnumScript(urns);
}
catch (FailedOperationException)
{
scriptingSucceeded = false;
break; //the object is probably encrypted
}
rawScripts.AddRange(results);
}
if (!scriptingSucceeded)
{
continue;
}
if (rawScripts.Count % nameGroup.Count() != 0)
{
continue;
}
var allScripts = new List<string>();
int stringsPerScript = rawScripts.Count / nameGroup.Count();
for (int i = 0; i < rawScripts.Count; i += stringsPerScript) //0, 3, 6, 9
{
IEnumerable<string> scriptParts = rawScripts.Skip(i).Take(stringsPerScript);
allScripts.Add(string.Join(Environment.NewLine, scriptParts));
}
//Compare Scripts
bool allEqual = true;
for (int i = 1; i < allScripts.Count; i++)
{
(string lineScript0, string lineScriptCurr, _) = dmp.DiffLinesToChars(allScripts[0], allScripts[i]).ToValueTuple();
List<Diff> diffs = dmp.DiffMain(lineScript0, lineScriptCurr, false);
if (!diffs.TrueForAll(diff => diff.Operation.IsEqual))
{
allEqual = false;
break; //scripts not equal
}
}
//If all scripts are equal, create data row for object
if (allEqual)
{
DataRow row = table.NewRow();
row["Name"] = likeNamedObjs[0].Name;
row["Type"] = likeNamedObjs[0].Type;
rows.Add(row);
}
}
//Add DataRows to DataTable
foreach (DataRow row in rows.OrderBy(r => r["Type"]).ThenBy(r => r["Name"]))
{
table.Rows.Add(row);
}
//Write DataTable to csv
var builder = new StringBuilder();
builder.AppendLine(string.Join(",", table.Columns.Cast<DataColumn>().Select(col => col.ColumnName)));
foreach (DataRow row in table.Rows)
{
builder.AppendLine(string.Join(",", row.ItemArray.Select(field => field.ToString())));
}
File.WriteAllText("equalObjects.csv", builder.ToString());
The code works. I can get my expected resulting csv file of (Name|Type) of all the objects that are exactly the same in all DB's across multiple servers. It's just so darn slow. Am I approaching this the right way? Is there a better/more modern solution?
