如何使用 scriptdom API 提取跨数据库引用

We simply go through all the named table references in the query and, if they have an alias, add this to a dictionary. Note that this will fail miserably if subqueries are introduced, because this visitor has no notion of scope (and indeed, adding scope to a visitor is much harder because the TSqlFragment offers no way to annotate the parse tree or even walk it from a node).

EqualityJoinVisitor 更有趣:

public class FindEqualityJoinVisitor : TSqlFragmentVisitor {
  readonly Dictionary<string, string> aliases;
  public FindEqualityJoinVisitor(Dictionary<string, string> aliases) {
    this.aliases = aliases;
  }

  readonly List<EqualityJoin> equalityJoins = new List<EqualityJoin>();
  public List<EqualityJoin> EqualityJoins { get { return equalityJoins; } }

  public override void Visit(QualifiedJoin qualifiedJoin) {
    var findEqualityComparisonVisitor = new FindEqualityComparisonVisitor();
    qualifiedJoin.SearchCondition.Accept(findEqualityComparisonVisitor);
    foreach (
      var equalityComparison in findEqualityComparisonVisitor.Comparisons
    ) {
      var firstColumnReferenceExpression = 
        equalityComparison.FirstExpression as ColumnReferenceExpression
      ;
      var secondColumnReferenceExpression = 
        equalityComparison.SecondExpression as ColumnReferenceExpression
      ;
      if (
        firstColumnReferenceExpression != null && 
        secondColumnReferenceExpression != null
      ) {
        string firstColumnResolved = resolveMultipartIdentifier(
          firstColumnReferenceExpression.MultiPartIdentifier
        );
        string secondColumnResolved = resolveMultipartIdentifier(
          secondColumnReferenceExpression.MultiPartIdentifier
        );
        equalityJoins.Add(
          new EqualityJoin(firstColumnResolved, secondColumnResolved)
        );
      }
    }
  }

  private string resolveMultipartIdentifier(MultiPartIdentifier identifier) {
    if (
      identifier.Identifiers.Count == 2 && 
      aliases.ContainsKey(identifier.Identifiers[0].Value)
    ) {
      return 
        aliases[identifier.Identifiers[0].Value] + "." + 
        identifier.Identifiers[1].Value;
    } else {
      return identifier.AsObjectName();
    }
  }
}

这会寻找 QualifiedJoin 实例，如果我们找到它们，我们会依次检查搜索条件以查找所有出现的相等比较.请注意，这确实适用于嵌套搜索条件:在 Bar JOIN Foo ON Bar.Quux = Foo.Quux AND Bar.Baz = Foo.Baz 中，我们将找到这两个表达式.

public class FindEqualityComparisonVisitor : TSqlFragmentVisitor {
  List<BooleanComparisonExpression> comparisons = 
    new List<BooleanComparisonExpression>()
  ;
  public List<BooleanComparisonExpression> Comparisons { 
    get { return comparisons; } 
  }

  public override void Visit(BooleanComparisonExpression e) {
    if (e.IsEqualityComparison()) comparisons.Add(e);
  }
}

public class EqualityJoin {
  readonly SchemaObjectName left;
  public SchemaObjectName Left { get { return left; } }

  readonly SchemaObjectName right;
  public SchemaObjectName Right { get { return right; } }

  public EqualityJoin(
    string qualifiedObjectNameLeft, string qualifiedObjectNameRight
  ) {
    var parser = new TSql120Parser(initialQuotedIdentifiers: false);
    IList<ParseError> errors;
    using (var reader = new StringReader(qualifiedObjectNameLeft)) {
      left = parser.ParseSchemaObjectName(reader, out errors);
    }
    using (var reader = new StringReader(qualifiedObjectNameRight)) {
      right = parser.ParseSchemaObjectName(reader, out errors);
    }
  }

  public bool JoinsSameDatabase() {
    return left.Identifiers[0].Value == right.Identifiers[0].Value;
  }

  public override string ToString() {
    return String.Format("{0} = {1}", left.AsObjectName(), right.AsObjectName());
  }
}

public static class MultiPartIdentifierExtensions {
  public static string AsObjectName(this MultiPartIdentifier multiPartIdentifier) {
    return string.Join(".", multiPartIdentifier.Identifiers.Select(i => i.Value));
  }
}

public static class ExpressionExtensions {
  public static bool IsEqualityComparison(this BooleanExpression expression) {
    return 
      expression is BooleanComparisonExpression && 
      ((BooleanComparisonExpression) expression).ComparisonType == BooleanComparisonType.Equals
    ;
  }
}