Which API in the Microsoft Tsql Scriptdom will allow me to retrieve/store ALL table references from a SQL script?

Question

Here is the MS doc that I am referencing: https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.transactsql.scriptdom?view=sql-dacfx-140.3881.1

I have looked through this several times already, but can not seem to find where I am able to retrieve the actual name of every table reference in a sql script. NamedTableReference is the closest thing I have found, but that only allows you to see Alias's of a table whereas I want the actual name.

Example:

SELECT * FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID

I would want an output of: Table1 Table2

This last part is not necessarily important, but just for context.. I am trying to extend the functionality of an open-source TSQLLint project and this will give me the ability to check for very active table names in our company that a developer is using without a NOLOCK hint.

Answer 1

You can identify all NamedTableReference fragments in the script using a visitor pattern. The fragment's SchemaObject.BaseIdentifier.Value is the referenced table name.

Below is a PowerShell example that derives from TSqlConcreteFragmentVisitor. This can be adapted for the .NET language of your choice.

$script = @"
SELECT * FROM Table1 INNER JOIN Table2 as t2 ON Table1.ID = Table2.ID
"@

class MyVisitor: Microsoft.SqlServer.TransactSql.ScriptDom.TSqlConcreteFragmentVisitor {

    [void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.NamedTableReference] $fragment) {
            Write-Host "Found $($fragment.GetType().Name) at line $($fragment.StartLine), column $($fragment.StartColumn). Table name: $($fragment.SchemaObject.BaseIdentifier.Value)"
    }

}

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

try {

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

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

    $visitor = [MyVisitor]::new()

    $frament.Accept($visitor)

}
catch {
    throw
}

Output:

Found NamedTableReference at line 1, column 15. Table name: Table1.
Found NamedTableReference at line 1, column 33. Table name: Table2.

In PowerShell, one must first load the external assembly that defines the base type into the app domain using Add-Type so that the script with the derived class will compile. This could be done with a wrapper script that dot-sources the actual script like:

# example wrapper script to load script DOM assembly and execute the visitor script
Add-Type -Path "C:\DacFxAssemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
. "C:\Scripts\Find-NamedTableReferences.ps1"