Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Chad Miller

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.

Parsing SQL for Table Names

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:

MacroScope

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

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.