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"