sql server - How to extract cross databases references using scriptdom API -

microsoft has exposed scriptdom api parse , generate tsql. i'm new , still playing it. want know how cross databases references queries one.

update  t3 set     description = 'abc'    database1.dbo.table1 t1         inner join database2.dbo.table2 t2             on (t1.id = t2.t1_id)         left outer join database3.dbo.table3 t3             on (t3.id = t2.t3_id)         inner join database2.dbo.table4 t4             on (t4.id = t2.t4_id) 

what want list of references:

database1.dbo.table1.id = database2.dbo.table2.t1_id database3.dbo.table3.id = database2.dbo.table2.t3_id database2.dbo.table4.id = database2.dbo.table2.t4_id 

however, last entry database2.dbo.table4.id = database2.dbo.table2.t4_id, both of columns 2 ends same database database2, not want. final required result is:

database1.dbo.table1.id = database2.dbo.table2.t1_id database3.dbo.table3.id = database2.dbo.table2.t3_id 

is possible implemented scriptdom?

a robust implementation not easy. limited problem posed in question, solution relatively simple -- stress "relatively". assume following:

• the query has 1 level -- there no unions, subqueries, expressions or other things introduce new scopes aliases (and can complicated quickly).
• all identifiers in query qualified there no doubt object it's referring to.

the solution strategy looks this: first visit tsqlfragment make list of table aliases, visit again equijoins, expanding aliases along way. using list, determine list of equijoins not refer same database. in code:

var sql = @"   update  t3   set     description = 'abc'      database1.dbo.table1 t1       inner join database2.dbo.table2 t2         on (t1.id = t2.t1_id)       left outer join database3.dbo.table3 t3         on (t3.id = t2.t3_id)       inner join database2.dbo.table4 t4         on (t4.id = t2.t4_id)  ";                  var parser = new tsql120parser(initialquotedidentifiers: false); ilist<parseerror> errors; tsqlscript script; using (var reader = new stringreader(sql)) {   script = (tsqlscript) parser.parse(reader, out errors); } // first resolve aliases. var aliasresolutionvisitor = new aliasresolutionvisitor(); script.accept(aliasresolutionvisitor);  // find equijoins, expanding aliases along way. var findequalityjoinvisitor = new findequalityjoinvisitor(   aliasresolutionvisitor.aliases ); script.accept(findequalityjoinvisitor);  // list aliases left database not same // right database. foreach (   var equijoin in    findequalityjoinvisitor.equalityjoins.where(     j => !j.joinssamedatabase()   ) ) {   console.writeline(equijoin.tostring()); } 

output:

database3.dbo.table3.id = database2.dbo.table2.t3_id database1.dbo.table1.id = database2.dbo.table2.t1_id 

aliasresolutionvisitor simple thing:

public class aliasresolutionvisitor : tsqlfragmentvisitor {   readonly dictionary<string, string> aliases = new dictionary<string, string>();   public dictionary<string, string> aliases { { return aliases; } }    public override void visit(namedtablereference namedtablereference ) {     identifier alias = namedtablereference.alias;     string baseobjectname = namedtablereference.schemaobject.asobjectname();     if (alias != null) {       aliases.add(alias.value, baseobjectname);     }   } } 

we go through named table references in query and, if have alias, add dictionary. note fail miserably if subqueries introduced, because visitor has no notion of scope (and indeed, adding scope visitor harder because tsqlfragment offers no way annotate parse tree or walk node).

the equalityjoinvisitor more interesting:

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 { { 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 columnreferenceexpression       ;       var secondcolumnreferenceexpression =          equalitycomparison.secondexpression 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();     }   } } 

this hunts qualifiedjoin instances and, if find them, in turn examine search condition find occurrences of equality comparisons. note work nested search conditions: in bar join foo on bar.quux = foo.quux , bar.baz = foo.baz, find both expressions.

how find them? using small visitor:

public class findequalitycomparisonvisitor : tsqlfragmentvisitor {   list<booleancomparisonexpression> comparisons =      new list<booleancomparisonexpression>()   ;   public list<booleancomparisonexpression> comparisons {      { return comparisons; }    }    public override void visit(booleancomparisonexpression e) {     if (e.isequalitycomparison()) comparisons.add(e);   } } 

nothing complicated here. wouldn't hard fold code other visitor, think clearer.

that's it, except helper code i'll present without comment:

public class equalityjoin {   readonly schemaobjectname left;   public schemaobjectname left { { return left; } }    readonly schemaobjectname right;   public schemaobjectname right { { 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 booleancomparisonexpression &&        ((booleancomparisonexpression) expression).comparisontype == booleancomparisontype.equals     ;   } } 

as mentioned before, code quite brittle. assumes queries have particular form, , fail (quite badly, giving misleading results) if don't. major open challenge extend can handle scopes , unqualified references correctly, other weirdness t-sql script can feature, think it's useful starting point nevertheless.