Parse TSQL scripts and return table and column usage

Question

we are currently involved in a data migration project, for which we have to analyze data usage, meaning we need to figure out which tables and columns are used and need to be migrated and which data is obsolete. We not only have many SSRS reports based on stored procedures, we also have hundreds if not thousands of TSQL scripts used for ad hoc analyzes. Analyzing them one by one by hand is virtually impossible.

I looked for a way to parse these scripts and return the data used by the scripts. I managed to write a macro in EditPad which extracts tables from a script, but I failed to do the same for columns. The main problems here are aliases, CTEs and even distinguishing column names in the script from other commands.

In contrast to a simple regex based macro, SQL Server must have a clear idea which columns are used by the script -> Execution plan. Is there any way we could use this capability for our purposes? Or even better: is there any third party tool that could do what we need? So far, I could not find anything.

Thx very much for any advice!

Answer 1

A partial answer:

Based on this article, it's possible to use PowerShell (or a compiled .Net language) to use the Microsoft.SqlServer.Management.SqlParser to generate the parse tree for a SQL statement as used by Microsoft tools.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.SqlParser") | Out-Null
$ParseOptions = New-Object Microsoft.SqlServer.Management.SqlParser.Parser.ParseOptions
$ParseOptions.BatchSeparator = 'GO' 
$ParseOptions.CompatibilityLevel = [Microsoft.SqlServer.Management.SqlParser.Common.DatabaseCompatibilityLevel]::Current
$ParseOptions.IsQuotedIdentifierSet = $true
$ParseOptions.TransactSqlVersion= [Microsoft.SqlServer.Management.SqlParser.Common.TransactSqlVersion]::Current

set-psdebug -strict

#$SQL=get-content $Args[0] |foreach-object{"$_`n"} 

$SQL = "SELECT  c.COLUMN_NAME,
        c.TABLE_NAME, 
        t.TABLE_SCHEMA,
        t.TABLE_TYPE,
        t.TABLE_NAME AS t2
FROM INFORMATION_SCHEMA.TABLES AS t
JOIN INFORMATION_SCHEMA.COLUMNS AS c
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_CATALOG = t.TABLE_CATALOG
"

$Script=[Microsoft.SqlServer.Management.SqlParser.Parser.Parser]::Parse($SQL, $ParseOptions)

$flags = [System.Reflection.BindingFlags]::Instance -bor [System.Reflection.BindingFlags]::NonPublic
$Script.GetType().GetProperty("Script", $flags).GetValue($Script).Xml

I have a local version of SSMS 2016 and SQL Server 2014 installed, and this worked without installing anything else.

This still leaves quite a lot of work to do in terms of interpreting the tree, but you might be able to make use of it.