Using Babelfish Compass’s ANTLR Grammar to Analyze T-SQL Code Quality

Uncategorized

Hello everyone! I want to show you a quick trick that I use to perform syntax searches in T-SQL files.

To understand this approach, we first need to understand the Babelfish Compass utility. I’ll explain it briefly below, but the following demos are probably the best way to get an overview of the Babelfish Compass utility (and I will also include a link for a Babelfish for Aurora PostgreSQL demo, but we will not be using a Babelfish for Aurora PostgreSQL instance obviously). We will be repurposing the Babelfish Compass utility for its ANTLR syntax parsing ability.

What is ANTLR?

ANTLR (ANother Tool for Language Recognition) is a powerful parser generator for reading, processing, executing, or translating structured text or binary files. It’s widely used to build languages, tools, and frameworks. From a grammar, ANTLR generates a parser that can build and walk parse trees.

The Babelfish Compass utility’s purpose is to analyze a SQL Server code base and determine its compatibility with Babelfish for Aurora PostgreSQL. It’s a simple, lightweight tool that you download from GitHub and execute with just a few commands.  The Babelfish Compass utility does not require access to your SQL Server database; it analyses SQL files saved in a directory.

We will be repurposing the Babelfish Compass utility for its ANTLR syntax parsing ability!

If you’re unfamiliar with Babelfish for Aurora PostgreSQL, it’s a built-in capability and translation layer that allows Amazon Aurora PostgreSQL to understand commands and queries originally written for Microsoft SQL Server. When provisioning a PostgreSQL database in AWS, simply check the Babelfish compatibility option. Once the database is provisioned, you can interact with the PostgreSQL instance using SSMS and T-SQL. However, in this post we’re only using the Babelfish Compass utility (which analysis a code base for Babelfish for Aurora PostgreSQL compatibility), which is available on GitHub and uses the ANTLR grammar parser to analyze code compatibility with just a few quick commands.

I have my own tool that I created on top of the Bablefish Compass utility that automates the creation and import into SQLite, more on this later.

When I’m new to a code base, I often run a Babelfish Compass analysis to determine which stored procedures use index hints, session-level SET options, cursors, global temp tables, and other constructs to get a lay of the land. This is also useful when you’re trying to identify code smells that you want to eradicate from the code base, saving you the effort of figuring out how to search for them.

I created a utility that generates the Babelfish Compass report and imports it into an SQLite database. Babelfish Compass has the ability to create a data file intended for import into PostgreSQL. I simply execute that option using arbitrary username and password values so the .dat file is created, while allowing the PostgreSQL import to fail. I then pick up the .dat file and import it into SQLite. With this method, you don’t have to install PostgreSQL and can instead take advantage of SQLite’s simplicity. Another benefit is that the .dat file created by the PostgreSQL import option is much more robust than the HTML and CSV files generated when the import option isn’t specified.

Hope that all made sense.

Now on to the analysis…

What can Babelfish Compass and the ANLR parser engine detect?

A lot!

I had an LLM create a sample file containing all sorts of items we might want to discover in a code base. SET options, index hints, cursors, GOTO statements, and so on, which I included at the end of this blog post. It’s ~300 lines long, so instead I’ll show the output from the Babelfish Compass report first.

A few notes before I print the output list…

This output was created by running the following SQL statement against the SQLite table (which contains the results of the Babelfish Compass report via my utility):

SELECT DISTINCT item FROM bbfcompass ORDER BY 1;

There is also a srcFile column (along with a bunch of other columns) that can be used to map each syntax item back to the source file. I will not be going over all the output columns.

As you scroll through the list, you can easily see that the Babelfish Compass utility does a great job of identifying T-SQL syntax constructs.

That’s about it! Hope this helps. And here is a link to my Babelfish Compass utility if you wish to automate this analysis.

Now here is that list of syntax constructs you can gleam from your source SQL files…

  • @@ERROR value 0
  • @@ERROR, reference
  • @@FETCH_STATUS, reference
  • Arithmetic operator +
  • Arithmetic operator /
  • BIT procedure parameter (with default value)
  • BIT variable (with default value)
  • CHAR(2) column
  • CHAR(2) procedure parameter (with default value)
  • CLOSE
  • COUNT()
  • CREATE INDEX
  • CREATE OR ALTER PROCEDURE
  • CREATE TABLE
  • CREATE TABLE ##globaltmptable
  • Comparison operator <>
  • Comparison operator =
  • Comparison operator >
  • Constraint FOREIGN KEY, in CREATE TABLE
  • Constraint PRIMARY KEY/UNIQUE, in CREATE TABLE
  • Constraint column DEFAULT, in CREATE TABLE
  • Cursor option FAST_FORWARD
  • Cursor option LOCAL
  • Cursor option SCROLL
  • DATETIME column
  • DATETIME procedure parameter (with default value)
  • DEALLOCATE CURSOR
  • DECLARE CURSOR
  • DELETE, OUTPUT INTO @tableVariable
  • DROP TABLE
  • ERROR_MESSAGE()
  • ERROR_SEVERITY()
  • ERROR_STATE()
  • EXECUTE procedure
  • EXECUTE sp_executesql
  • EXECUTE(string)
  • EXECUTE(string): dynamic SQL statements must be analyzed manually
  • FETCH LAST
  • FETCH NEXT
  • FK constraint referencing DB name
  • GETDATE()
  • GOTO label
  • IF
  • INNER JOIN
  • INSERT..VALUES
  • INT IDENTITY(1,1) column
  • INT column
  • INT column (table variable)
  • INT procedure parameter (with default value)
  • INT variable
  • INT variable (with default value)
  • LEFT()
  • MERGE
  • MONEY column
  • MONEY column (table variable)
  • MONEY variable
  • MONEY variable (with default value)
  • NVARCHAR(2048) variable (with default value)
  • NVARCHAR(MAX) variable
  • OBJECT_ID()
  • OPEN
  • Procedure (CREATE OR ALTER), option WITH EXECUTE AS OWNER
  • Procedure (CREATE OR ALTER), option WITH RECOMPILE
  • Query hint RECOMPILE
  • RAISERROR
  • SELECT
  • SELECT FOR JSON PATH
  • SELECT FOR XML AUTO ELEMENTS
  • SELECT TOP <number>
  • SELECT TOP without ORDER BY
  • SELECT subquery
  • SELECT..INTO #tmptable
  • SELECT..PIVOT
  • SET ANSI_NULLS ON
  • SET ANSI_PADDING ON
  • SET ANSI_WARNINGS ON
  • SET ARITHABORT ON
  • SET CONCAT_NULL_YIELDS_NULL ON
  • SET IMPLICIT_TRANSACTIONS OFF
  • SET NOCOUNT ON
  • SET NUMERIC_ROUNDABORT OFF
  • SET QUOTED_IDENTIFIER ON
  • SET ROWCOUNT 0
  • SET ROWCOUNT <number>
  • SET XACT_ABORT ON
  • SUM()
  • String concatenation operator +
  • TABLE variable declaration
  • TRY-CATCH
  • Table hint FORCESCAN
  • Table hint FORCESEEK
  • Table hint INDEX(index name)
  • Table hint NOLOCK
  • Table hint ROWLOCK
  • Table hint TABLOCK
  • Table hint UPDLOCK
  • UPDATE
  • VARCHAR(100) column
  • VARCHAR(20) column
  • VARCHAR(20) variable (with default value)
  • VARCHAR(MAX) column
  • Variable assignment by SELECT @v =
  • Variable assignment by SET @v =
  • WAITFOR DELAY
  • WHILE
  • YEAR()
  • label: (for GOTO)
  • sp_executesql: dynamic SQL statements must be analyzed manually

Here is the code that I ran my analysis on. 

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET ARITHABORT ON
GO
IF OBJECT_ID('dbo.Orders',         'U') IS NOT NULL DROP TABLE dbo.Orders;
IF OBJECT_ID('dbo.Customers',      'U') IS NOT NULL DROP TABLE dbo.Customers;
IF OBJECT_ID('dbo.OrderSummary',   'U') IS NOT NULL DROP TABLE dbo.OrderSummary;
GO
CREATE TABLE dbo.Customers (
    CustomerID   INT          NOT NULL PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL,
    Region       CHAR(2)      NOT NULL,
    CreditLimit  MONEY        NOT NULL DEFAULT 0,
    CreatedDate  DATETIME     NOT NULL DEFAULT GETDATE()
);
CREATE TABLE dbo.Orders (
    OrderID      INT          NOT NULL PRIMARY KEY IDENTITY(1,1),
    CustomerID   INT          NOT NULL REFERENCES dbo.Customers(CustomerID),
    OrderDate    DATETIME     NOT NULL DEFAULT GETDATE(),
    TotalAmount  MONEY        NOT NULL DEFAULT 0,
    Status       VARCHAR(20)  NOT NULL DEFAULT 'OPEN',
    Notes        VARCHAR(MAX)     NULL
);
CREATE TABLE dbo.OrderSummary (
    Region       CHAR(2)  NOT NULL,
    OrderCount   INT      NOT NULL DEFAULT 0,
    TotalRevenue MONEY    NOT NULL DEFAULT 0
);
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID ON dbo.Orders (CustomerID);
CREATE NONCLUSTERED INDEX IX_Orders_Status     ON dbo.Orders (Status, OrderDate);
GO
CREATE OR ALTER PROCEDURE dbo.usp_CompassTest
    @CustomerID     INT           = NULL,
    @Region         CHAR(2)       = NULL,
    @StartDate      DATETIME      = NULL,
    @EndDate        DATETIME      = NULL,
    @Debug          BIT           = 0
WITH RECOMPILE
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SET IMPLICIT_TRANSACTIONS OFF;
    DECLARE @OldStyleError INT;
    DECLARE @GotoTriggered BIT = 0;
    IF OBJECT_ID('tempdb..##GlobalOrderStaging') IS NOT NULL
        DROP TABLE ##GlobalOrderStaging;
    CREATE TABLE ##GlobalOrderStaging (
        OrderID     INT,
        CustomerID  INT,
        TotalAmount MONEY
    );
    SELECT
        o.OrderID,
        o.CustomerID,
        o.TotalAmount,
        o.Status
    INTO #LocalOrderStaging
    FROM dbo.Orders o
    WHERE o.Status = 'OPEN'
    OPTION (RECOMPILE);
    SELECT * FROM dbo.Customers;
    SELECT TOP 10 * FROM dbo.Orders;
    SELECT o.OrderID, o.TotalAmount
    FROM   dbo.Orders o
    WHERE  YEAR(o.OrderDate) = 2025
      AND  LEFT(o.Status, 4) = 'OPEN';
    SELECT o.OrderID
    FROM   dbo.Orders o
    WHERE  o.CustomerID = '42';
    SELECT o.OrderID, o.TotalAmount
    FROM   dbo.Orders o WITH (INDEX(IX_Orders_Status))
    WHERE  o.Status = 'CLOSED';
    SELECT o.OrderID
    FROM   dbo.Orders o WITH (FORCESEEK)
    WHERE  o.CustomerID = @CustomerID;
    SELECT o.OrderID
    FROM   dbo.Orders o WITH (FORCESCAN)
    WHERE  o.TotalAmount > 100;
    SELECT c.CustomerName, o.TotalAmount
    FROM   dbo.Customers c WITH (NOLOCK)
    JOIN   dbo.Orders    o WITH (NOLOCK)
        ON c.CustomerID = o.CustomerID;
    SELECT o.OrderID
    FROM   dbo.Orders o WITH (ROWLOCK, UPDLOCK)
    WHERE  o.Status = 'OPEN';
    SELECT o.OrderID
    FROM   dbo.Orders o WITH (TABLOCK)
    WHERE  o.TotalAmount > 500;
    DECLARE @CurOrderID     INT;
    DECLARE @CurAmount      MONEY;
    DECLARE @RunningTotal   MONEY = 0;
    DECLARE cur_Orders CURSOR
        LOCAL FAST_FORWARD
    FOR
        SELECT OrderID, TotalAmount
        FROM   dbo.Orders
        WHERE  Status = 'OPEN'
        ORDER  BY OrderDate;
    OPEN cur_Orders;
    FETCH NEXT FROM cur_Orders INTO @CurOrderID, @CurAmount;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @RunningTotal = @RunningTotal + @CurAmount;
        IF @Debug = 1
            WAITFOR DELAY '00:00:00.010';  -- [CAT-24]
        FETCH NEXT FROM cur_Orders INTO @CurOrderID, @CurAmount;
    END
    CLOSE cur_Orders;
    DEALLOCATE cur_Orders;
    DECLARE cur_Scroll CURSOR
        LOCAL SCROLL
    FOR
        SELECT CustomerID, CustomerName FROM dbo.Customers;
    OPEN cur_Scroll;
    FETCH LAST FROM cur_Scroll INTO @CustomerID, @Region;
    CLOSE cur_Scroll;
    DEALLOCATE cur_Scroll;
    DECLARE @DynamicSQL  NVARCHAR(MAX);
    DECLARE @FilterValue VARCHAR(20) = 'OPEN';
    SET @DynamicSQL = 'SELECT OrderID, TotalAmount FROM dbo.Orders WHERE Status = '''
                      + @FilterValue + '''';
    EXEC (@DynamicSQL);              -- [CAT-05a]
    SET @DynamicSQL = N'SELECT OrderID, TotalAmount
                        FROM   dbo.Orders
                        WHERE  Status       = @Status
                          AND  (@CustID IS NULL OR CustomerID = @CustID)';
    EXEC sp_executesql
        @DynamicSQL,
        N'@Status VARCHAR(20), @CustID INT',
        @Status = 'OPEN',
        @CustID = @CustomerID;
    SELECT CustomerID, CustomerName
    FROM   dbo.Customers
    FOR XML AUTO, ELEMENTS;
    SELECT CustomerID, CustomerName
    FROM   dbo.Customers
    FOR JSON PATH;
    SELECT *
    FROM (
        SELECT Region, TotalAmount FROM dbo.Orders o
        JOIN   dbo.Customers c ON c.CustomerID = o.CustomerID
    ) src
    PIVOT (
        SUM(TotalAmount)
        FOR Region IN ([NE],[SE],[MW],[WE])
    ) pvt;
    MERGE dbo.OrderSummary AS tgt
    USING (
        SELECT c.Region,
               COUNT(*)    AS OrderCount,
               SUM(o.TotalAmount) AS TotalRevenue
        FROM   dbo.Orders o
        JOIN   dbo.Customers c ON c.CustomerID = o.CustomerID
        GROUP  BY c.Region
    ) AS src (Region, OrderCount, TotalRevenue)
    ON tgt.Region = src.Region
    WHEN MATCHED THEN
        UPDATE SET tgt.OrderCount   = src.OrderCount,
                   tgt.TotalRevenue = src.TotalRevenue
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (Region, OrderCount, TotalRevenue)
        VALUES (src.Region, src.OrderCount, src.TotalRevenue)
    WHEN NOT MATCHED BY SOURCE THEN
        DELETE;
    DECLARE @DeletedOrders TABLE (OrderID INT, TotalAmount MONEY);
    DELETE FROM dbo.Orders
    OUTPUT deleted.OrderID, deleted.TotalAmount
    INTO   @DeletedOrders
    WHERE  Status = 'CANCELLED';
    UPDATE o
    SET    o.Status = 'REVIEWED'
    FROM   dbo.Orders    o
    JOIN   dbo.Customers c ON c.CustomerID = o.CustomerID
    WHERE  c.Region = @Region;
    SELECT @OldStyleError = @@ERROR;
    IF @OldStyleError <> 0
    BEGIN
        RAISERROR('Legacy error handling detected: %d', 16, 1, @OldStyleError);
        GOTO CleanupLabel;
    END
    BEGIN TRY
        DECLARE @Zero INT = 0;
        SELECT 1 / @Zero;
    END TRY
    BEGIN CATCH
        DECLARE @ErrMsg  NVARCHAR(2048) = ERROR_MESSAGE();
        DECLARE @ErrSev  INT            = ERROR_SEVERITY();
        DECLARE @ErrStat INT            = ERROR_STATE();
        RAISERROR(@ErrMsg, @ErrSev, @ErrStat);
    END CATCH
    SELECT
        c.CustomerID,
        c.CustomerName,
        (SELECT COUNT(*)
         FROM   dbo.Orders o
         WHERE  o.CustomerID = c.CustomerID
           AND  o.Status = 'OPEN') AS OpenOrderCount
    FROM dbo.Customers c;
    SET ROWCOUNT 100;
    SELECT OrderID FROM dbo.Orders;
    SET ROWCOUNT 0;
    CleanupLabel:
        IF OBJECT_ID('tempdb..##GlobalOrderStaging') IS NOT NULL
            DROP TABLE ##GlobalOrderStaging;
        IF OBJECT_ID('tempdb..#LocalOrderStaging') IS NOT NULL
            DROP TABLE #LocalOrderStaging;
END;
GO
CREATE OR ALTER PROCEDURE dbo.usp_CompassTest_Impersonated
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 'Running as owner' AS ExecutionContext;
END;
GO
INSERT INTO dbo.Customers (CustomerID, CustomerName, Region, CreditLimit)
VALUES (1, 'Acme Corp',    'NE', 10000),
       (2, 'Globex Ltd',   'SE', 25000),
       (3, 'Initech',      'MW',  5000);
INSERT INTO dbo.Orders (CustomerID, TotalAmount, Status)
VALUES (1,  250.00, 'OPEN'),
       (1, 1200.00, 'CLOSED'),
       (2,  875.50, 'OPEN'),
       (3,   42.00, 'CANCELLED');
GO
EXEC dbo.usp_CompassTest @Debug = 0;
GO

Hope this all made sense! Happy coding.

Published

Leave a Reply