private void AnalyzeUpdateStatement(UpdateStatement updateStatement, ParserResults results)
{
updateStatement.PrintTSqlStatementBlockToDebugConsole();
List <TableParsingResult> temp = new List <TableParsingResult>();
Dictionary <string, List <TableParsingResult> > cteModel = new Dictionary <string, List <TableParsingResult> >();
if (updateStatement.WithCtesAndXmlNamespaces?.CommonTableExpressions.Count > 0)
{
foreach (CommonTableExpression cte in updateStatement.WithCtesAndXmlNamespaces.CommonTableExpressions)
{
AnalyzeCommonTableExpression(cteModel, cte);
}
}
if (updateStatement.UpdateSpecification.Target is NamedTableReference updateNamedTableReference)
{
string tableName = ExtraceTableNameFromNamedTableRefernce(updateNamedTableReference, out string alias);
if (updateStatement.UpdateSpecification.FromClause == null)
{
temp.AddIfNotExists(tableName, SqlOperationType.UPDATE, alias);
}
else
{
var items = ExtractTablesUsedInFromClause(updateStatement.UpdateSpecification.FromClause);
if (cteModel.Count > 0)
{
foreach (var cte in cteModel)
{
var item = items.Find(x => x.TableName == cte.Key);
if (item != null)
{
items.Remove(item);
foreach (var table in cte.Value)
{
items.AddIfNotExists(table.TableName, table.OperationType, table.Alias);
}
}
}
}
temp.AddIfNotExists(items);
var result = items.Find(x => x.Alias == tableName);
if (result != null)
{
results.AddIfNotExists(result.TableName, SqlOperationType.UPDATE, tableName);
}
}
foreach (var setClause in updateStatement.UpdateSpecification.SetClauses)
{
temp.AddIfNotExists(ExtractTablesUsedInAssignmentClause(setClause));
}
}
updateStatement.PrintTablesToDebugConsole(temp);
results.AddIfNotExists(temp);
}
private void AnalyzeSelectStatement(SelectStatement selectStatement, ParserResults results)
{
selectStatement.PrintTSqlStatementBlockToDebugConsole();
List <TableParsingResult> temp = new List <TableParsingResult>();
if (selectStatement.QueryExpression is QuerySpecification selectQuerySpecification)
{
var result = ExtractTablesFromQuerySpecification(selectQuerySpecification); // ExtractTablesUsedInFromClause(selectQuerySpecification.FromClause);
foreach (SelectElement selectElement in selectQuerySpecification.SelectElements)
{
if (selectElement is SelectScalarExpression scalarExpression && scalarExpression.Expression is ScalarSubquery scalarSubquery && scalarSubquery.QueryExpression is QuerySpecification querySpecification)
{
var items = ExtractTablesFromQuerySpecification(querySpecification);
temp.AddIfNotExists(items);
}
}
temp.AddIfNotExists(result);
}
else if (selectStatement.QueryExpression is BinaryQueryExpression binaryQueryExpression)
{
var result = ExtractTablesFromBinaryQueryExpression(binaryQueryExpression);
temp.AddIfNotExists(result);
}
selectStatement.PrintTablesToDebugConsole(temp);
results.AddIfNotExists(temp);
}
private void AnalyzeInsertStatement(InsertStatement insertStatement, ParserResults results)
{
insertStatement.PrintTSqlStatementBlockToDebugConsole();
List <TableParsingResult> temp = new List <TableParsingResult>();
Dictionary <string, List <TableParsingResult> > cteModel = new Dictionary <string, List <TableParsingResult> >();
if (insertStatement.WithCtesAndXmlNamespaces?.CommonTableExpressions.Count > 0)
{
foreach (CommonTableExpression cte in insertStatement.WithCtesAndXmlNamespaces.CommonTableExpressions)
{
AnalyzeCommonTableExpression(cteModel, cte);
}
}
if (insertStatement.InsertSpecification.Target is NamedTableReference insertNamedTableReference)
{
string tableName = ExtraceTableNameFromNamedTableRefernce(insertNamedTableReference, out string alias);
temp.AddIfNotExists(tableName, SqlOperationType.INSERT, alias);
var items = ExtractTablesFromInsertStatement(insertStatement, cteModel);
temp.AddIfNotExists(items);
}
else if (insertStatement.InsertSpecification.Target is VariableTableReference variableTableReference)
{
temp.AddIfNotExists(variableTableReference.Variable.Name, SqlOperationType.INSERT, null);
var items = ExtractTablesFromInsertStatement(insertStatement, cteModel);
temp.AddIfNotExists(items);
}
insertStatement.PrintTablesToDebugConsole(temp);
results.AddIfNotExists(temp);
}
private void AnalyzeDeclareVariableStatement(DeclareVariableStatement declareVariableStatement, ParserResults results)
{
foreach (DeclareVariableElement element in declareVariableStatement.Declarations)
{
if (element.Value is ScalarSubquery scalarSubquery)
{
var items = ExtractTablesFromScalarSubQuery(scalarSubquery);
results.AddIfNotExists(items);
}
}
}
private void AnalyzeIfStatement(IfStatement ifStatement, ParserResults results)
{
ifStatement.PrintTSqlStatementBlockToDebugConsole();
if (ifStatement.Predicate is ExistsPredicate existsPredicate)
{
var items = ExtractTablesFromScalarSubQuery(existsPredicate.Subquery);
results.AddIfNotExists(items);
}
AnalyzeTsqlStatement(results, ifStatement.ThenStatement);
AnalyzeTsqlStatement(results, ifStatement.ElseStatement);
}
private void AnalyzeDeleteStatement(DeleteStatement deleteStatement, ParserResults results)
{
deleteStatement.PrintTSqlStatementBlockToDebugConsole();
if (deleteStatement.DeleteSpecification.Target is NamedTableReference deleteNamedTableReference)
{
string tableName = ExtraceTableNameFromNamedTableRefernce(deleteNamedTableReference, out string alias);
if (deleteStatement.DeleteSpecification.FromClause != null)
{
var items = ExtractTablesUsedInFromClause(deleteStatement.DeleteSpecification.FromClause);
var match = items.Find(x => x.Alias == tableName);
if (match != null)
{
match.OperationType = SqlOperationType.DELETE;
}
results.AddIfNotExists(items);
}
else
{
results.AddIfNotExists(tableName, SqlOperationType.DELETE, alias);
}
}
}
private void AnalyzeMergeStatement(MergeStatement mergeStatement, ParserResults results)
{
mergeStatement.PrintTSqlStatementBlockToDebugConsole();
List <TableParsingResult> temp = new List <TableParsingResult>();
if (mergeStatement.MergeSpecification.Target is NamedTableReference mergeNamedTableReference)
{
string tableName = ExtraceTableNameFromNamedTableRefernce(mergeNamedTableReference, out string alias);
temp.AddIfNotExists(tableName, SqlOperationType.INSERT, alias);
temp.AddIfNotExists(tableName, SqlOperationType.UPDATE, alias);
if (mergeStatement.MergeSpecification.TableReference is QueryDerivedTable mergeQueryDerivedTable && mergeQueryDerivedTable.QueryExpression is QuerySpecification querySpecification)
{
var result = ExtractTablesFromQuerySpecification(querySpecification); //ExtractTablesUsedInFromClause(mergeQuerySpecification.FromClause);
temp.AddIfNotExists(result);
}
}
mergeStatement.PrintTablesToDebugConsole(temp);
results.AddIfNotExists(temp);
}