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.

Guessing this is a joke, but we have certain tables that are highly used and require NOLOCK hints. Of course, we only do this with SELECT statements that won't be effected by dirty reads. — Data Dill, Jan 26 '20 at 16:18
@DylanJanszen, also be aware that read uncommitted can cause missing or duplicated rows in the results. A row-versioning isolation level would be the right way to avoid blocking. Regarding the `NamedTableReference` fragment, I think the `SchemaObject.BaseIdentifier.Value` property will contain the table name. — Dan Guzman, Jan 26 '20 at 16:42
It looks like the FromClause class might actually be what I am looking for. If this doesn't work I will make note of what you said and try that as well. Thanks! — Data Dill, Jan 26 '20 at 16:56

Dan Guzman · Accepted Answer · 2020-01-26T22:51:39.080

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"

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

1 Answers1