Visual Studio Team System 2008 Database Edition (VSDB) ships with a .NET class for parsing T-SQL. I’ve previously blogged about producing a Stored Procedure Call Tree and even built the Test-SqlScript and Out-SqlScript cmdlets included in SQL Server PowerShell Extensions using the assemblies.

Recently I’ve discovered another useful SQL parser called MacroScope that’s worthy of mention . A short article on CodeProject describes MacroScope as an Antlr based SQL parsing/transformation utility with support for Oracle, SQL Server, MySQL and even MS Access. Currently only CRUD (Select, Insert, Update, Delete) SQL parsing is implemented. Although MacroScope isn’t as full-featured as the VSDB classes, which supports the full range of T-SQL syntax, the fact that other DBMS types are covered is a plus. In addition MacroScope is licensed under GPL.

To use MacroScope from PowerShell you’ll first need to download the source code and build the project. I’ve already done this, so alternately you can grab the assemblies from here…

As a test I’ve created a PowerShell script for finding all table names and aliases within a SQL string:

param ($commandText)            
#Assumes MacroScope and Antlr3 assemblies are in same directory
add-type -Path $(Resolve-Path .\MacroScope.dll | Select-Object -ExpandProperty Path)
add-type -Path $(Resolve-Path .\Antlr3.Runtime.dll | Select-Object -ExpandProperty Path)            
#######################
function Get-Table
{
    param($table)            
    $table            
    if ($table.HasNext)
    { Get-Table $table.Next }            
}            
$sqlparser =[MacroScope.Factory]::CreateParser($commandText)
$expression = $sqlparser.queryExpression()
Get-Table $expression.From.Item | Select @{n='Name';e={$_.Source.Identifier}}, @{n='Alias';e={$_.Alias}}

Calling the PowerShell scripting with a simple SQL string which has multiple tables and aliases produces the following output:

Note: I haven’t done more complex testing—your mileage may vary.