private static void SaveScriptToFile(TSqlObject scriptTsqlObject, Outcome outcomeName)
{
string filePath = Path.Combine(Environment.CurrentDirectory, _compareResultsPath, $"{scriptTsqlObject.Name}_{outcomeName}.txt");
using (StreamWriter writer = new StreamWriter(filePath))
{
writer.WriteLine(scriptTsqlObject.GetScript());
}
}
/// <summary>
/// Converts a tsql object into a fragment
/// </summary>
/// <param name="obj"></param>
/// <param name="parseErrors"></param>
/// <returns></returns>
public static TSqlFragment GetFragment(this TSqlObject obj, out IList <ParseError> parseErrors)
{
var tsqlParser = new TSql140Parser(true);
TSqlFragment fragment = null;
using (StringReader stringReader = new StringReader(obj.GetScript()))
{
fragment = tsqlParser.Parse(stringReader, out parseErrors);
//so even after parsing, some scripts are coming back as tsql script, lets try to get the root object
if (fragment.GetType() == typeof(TSqlScript))
{
fragment = ((TSqlScript)fragment).Batches.FirstOrDefault()?.Statements.FirstOrDefault();
}
}
return(fragment);
}
public void Visit(Node <Nodes.Database> databaseNode, TSqlObject storedProcedure)
{
var storedProcedureNode = GetStoredProcedureNode(databaseNode, storedProcedure);
foreach (var table in storedProcedure.GetReferenced().Where(x => x.ObjectType == ModelSchema.Table))
{
try
{
var tableNode = GetTableNode(databaseNode, table);
if ((storedProcedureNode == null) || (tableNode == null))
{
continue;
}
if (RelationshipExists(storedProcedureNode.Reference, tableNode.Reference))
{
continue;
}
Console.WriteLine("Creating dependency between stored procedure {0} and table {1}", storedProcedureNode.Data.Id, tableNode.Data.Id);
_graphClient.CreateRelationship(storedProcedureNode.Reference, new StoredProcedureReferencesTable(tableNode.Reference));
}
catch (Exception ex)
{
Console.WriteLine("Error analyzing {0}: {1}", storedProcedure.Name.ToString(), ex);
}
}
foreach (var column in storedProcedure.GetReferenced().Where(x => x.ObjectType == ModelSchema.Column))
{
try
{
var columnNode = GetColumnNode(databaseNode, column);
if ((storedProcedureNode == null) || (columnNode == null))
{
continue;
}
if (RelationshipExists(storedProcedureNode.Reference, columnNode.Reference))
{
continue;
}
Console.WriteLine("Creating dependency between stored procedure {0} and column {1}", storedProcedureNode.Data.Id, columnNode.Data.Id);
_graphClient.CreateRelationship(storedProcedureNode.Reference, new StoredProcedureReferencesColumn(columnNode.Reference));
}
catch (Exception ex)
{
Console.WriteLine("Error analyzing {0}: {1}", storedProcedure.Name.ToString(), ex);
}
}
// Check for dynamic SQL
var script = storedProcedure.GetScript();
if (!script.Contains("EXEC"))
{
return;
}
foreach (Match match in FromRegex.Matches(script))
{
try
{
var tableNode = GetTableNode(databaseNode, string.Format("[dbo].[{0}]", match.Groups["table"].Value));
if ((storedProcedureNode == null) || (tableNode == null))
{
continue;
}
if (RelationshipExists(storedProcedureNode.Reference, tableNode.Reference))
{
continue;
}
Console.WriteLine("Creating dependency between stored procedure {0} and table {1} (dynamic SQL)", storedProcedureNode.Data.Id, tableNode.Data.Id);
_graphClient.CreateRelationship(storedProcedureNode.Reference, new StoredProcedureReferencesTable(tableNode.Reference));
}
catch (Exception ex)
{
Console.WriteLine("Error analyzing {0}: {1}", storedProcedure.Name.ToString(), ex);
}
}
foreach (Match match in JoinRegex.Matches(script))
{
try
{
var tableNode = GetTableNode(databaseNode, string.Format("[dbo].[{0}]", match.Groups["table"].Value));
if ((storedProcedureNode == null) || (tableNode == null))
{
continue;
}
if (RelationshipExists(storedProcedureNode.Reference, tableNode.Reference))
{
continue;
}
Console.WriteLine("Creating dependency between stored procedure {0} and table {1} (dynamic SQL)", storedProcedureNode.Data.Id, tableNode.Data.Id);
_graphClient.CreateRelationship(storedProcedureNode.Reference, new StoredProcedureReferencesTable(tableNode.Reference));
}
catch (Exception ex)
{
Console.WriteLine("Error analyzing {0}: {1}", storedProcedure.Name.ToString(), ex);
}
}
}
