Option in ScriptDom to retrieve table names with join column names?

Question

Would like to create analysis for queries in SQL Server which will be checking which tables were join on which tables on which columns. Would like to use ScriptDom in C#. For example we have query:

SELECT *
FROM tabel1
INNER JOIN tabel2 ON tabel1.varchar1col = tabel2.varchar1col
INNER JOIN tabel3 ON tabel1.varchar2col = tabel3.varchar2col
LEFT JOIN tabel4 ON tabel3.varchar2col = tabel4.varchar3col

And would like to have result:

tabel1 varchar1col
tabel1 varchar2col
tabel2 varchar1col
tabel3 varchar2col
tabel4 varchar3col

Is it possible to create it in ScriptDom?

Answer 1

As discussed in comments, the parser does not actually resolve column names referenced in JOIN/WHERE clause predicates to objects in the query. Consider a syntactically valid query can reference invalid tables/aliases, unqualified names are ambiguous, plus constructs like CTEs, and derived tables, etc. present a challenge.

The example PowerShell script below (which you can convert to your .NET language of choice) uses a script DOM visitor. This will provide the desired results for the query in your question because the column names qualified with the actual table name. For more generalized use, predicates would need to consistently specify a 2-part name that reference a table (not alias, CTE, derived table, etc.).

class ColumnReference {
    [String]$TableName
    [String]$ColumnName
}

class ColumnReferenceVisitor: Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor {

    [System.Collections.Generic.List[ColumnReference]]$ColumnReferences

    [void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.BooleanComparisonExpression] $fragment) {

        # assume 2-part column references are a table and column name
        if($fragment.FirstExpression.GetType().ToString() -eq "Microsoft.SqlServer.TransactSql.ScriptDom.ColumnReferenceExpression") {
            [Microsoft.SqlServer.TransactSql.ScriptDom.ColumnReferenceExpression]$colRef = $fragment.FirstExpression
            if($colRef.MultiPartIdentifier.Count -eq 2) {
                $columnReference = [ColumnReference]::new()
                $columnReference.TableName = $colRef.MultiPartIdentifier[0].Value
                $columnReference.ColumnName = $colRef.MultiPartIdentifier[1].Value
                $this.ColumnReferences.Add($columnReference)
            }
        }

        if($fragment.SecondExpression.GetType().ToString() -eq "Microsoft.SqlServer.TransactSql.ScriptDom.ColumnReferenceExpression") {
            [Microsoft.SqlServer.TransactSql.ScriptDom.ColumnReferenceExpression]$colRef = $fragment.SecondExpression
            if($colRef.MultiPartIdentifier.Count -eq 2) {
                $columnReference = [ColumnReference]::new()
                $columnReference.TableName = $colRef.MultiPartIdentifier[0].Value
                $columnReference.ColumnName = $colRef.MultiPartIdentifier[1].Value
                $this.ColumnReferences.Add($columnReference)
            }
        }

    }

    # parse script and invoke visitor
    static [System.Collections.Generic.List[ColumnReference]] getColumnReferences($script) {

        $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql160Parser($true)
        $parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
        $stringReader = New-Object System.IO.StringReader($script)

        $fragment = $parser.Parse($stringReader, [ref]$parseErrors)
        if($parseErrors.Count -gt 0) {
            throw "$($parseErrors.Count) parsing errors: $(($parseErrors | ConvertTo-Json))"
        }

        $visitor = [ColumnReferenceVisitor]::new()
        $fragment.Accept($visitor)

        return $visitor.ColumnReferences

    }

    ColumnReferenceVisitor() {

        $this.ColumnReferences = New-Object System.Collections.Generic.List[ColumnReference]

    }
}


# ############
# ### MAIN ###
# ############

$script = @"
SELECT *
FROM tabel1
INNER JOIN tabel2 ON tabel1.varchar1col = tabel2.varchar1col
INNER JOIN tabel3 ON tabel1.varchar2col = tabel3.varchar2col
LEFT JOIN tabel4 ON tabel3.varchar2col = tabel4.varchar3col
"@

try {

    $columnReferences = [ColumnReferenceVisitor]::getColumnReferences($script)
    $columnReferences | Sort-Object -Property TableName, ColumnName -Unique | Format-Table

}
catch {

    throw

}

Note that visitors in PowerShell need the script DOM assembly loaded before the derived class will compile. This can be done with a wrapper script like the example below that loads the assembly and dot-sources the script with the above visitor code. I cheated here and used the assembly included with the latest SSMS version. One should generally the assembly included in the DacFX NuGet package .

Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server Management Studio 19\Common7\IDE\Extensions\Application\Microsoft.SqlServer.TransactSql.ScriptDom.dll"

Results:

TableName	ColumnName
tabel1	varchar1col
tabel1	varchar2col
tabel2	varchar1col
tabel3	varchar2col
tabel4	varchar3col

. "C:\PowerShellScripts\Get-ColumnReferences.ps1"