Using the TransactSql.ScriptDOM parser to get statement counts

Today there was a question on the #sqlhelp Twitter hashtag: “Is it possible to get UPDATE/SELECT/INSERT/DELETE statement counts from SQL Server?”


This is a perfect use case for the SQLDOM parser a.k.a. Microsoft.SqlServer.TransactSql.ScriptDom. I put together a sample C# application to demonstrate how easy it is for requirements like the above. The assumption made below (for simplicity) is to parse the input from a .SQL script file. In the real world, we can easily script the object code from a live connection (using SMO for example) and pass it as a MemoryStream to the TextReader below. But that is left, as they say, to the reader as an intellectual exercise Smile

Test case

Here is the sample code, and interestingly if you run it on a script of AdventureWorks, it tells you that there is not a single DELETE statement embedded in any of the sample database’s Stored Procedures, Views or Functions (which is correct and it is a minor eye-opener as well Smile).

Found 37 SELECTs, 1743 INSERTs, 15 UPDATEs & 0 DELETEs


The ScriptDom excels at simple tasks like this, but do keep in mind some complications which need to be considered for more advanced requirements. I’ll be posting some tips on how to overcome those complications later on.

Sample Code

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.TransactSql.ScriptDom;
using System.IO;

namespace SampleSQLVisitor
    class Program
        static void Main(string[] args)
            TextReader txtRdr = new StreamReader("myscriptfile.sql");
            TSql110Parser parser = new TSql110Parser(true);

            IList<ParseError> errors;
            TSqlFragment sqlFragment = parser.Parse(txtRdr, out errors);
             // TODO report the parsing errors generated (if any)

            SQLVisitor myVisitor = new SQLVisitor();


    internal class SQLVisitor : TSqlFragmentVisitor
        private int SELECTcount = 0;
        private int INSERTcount = 0;
        private int UPDATEcount = 0;
        private int DELETEcount = 0;

        private string GetNodeTokenText(TSqlFragment fragment)
            StringBuilder tokenText = new StringBuilder();
            for (int counter = fragment.FirstTokenIndex; counter <= fragment.LastTokenIndex; counter++)

            return tokenText.ToString();

        // SELECTs
        public override void ExplicitVisit(SelectStatement node)
            //Console.WriteLine("found SELECT statement with text: " + GetNodeTokenText(node));

        // INSERTs
        public override void ExplicitVisit(InsertStatement node)

        // UPDATEs
        public override void ExplicitVisit(UpdateStatement node)

        // DELETEs
        public override void ExplicitVisit(DeleteStatement node)

        public void DumpStatistics()
            Console.WriteLine(string.Format("Found {0} SELECTs, {1} INSERTs, {2} UPDATEs & {3} DELETEs",


This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment.  THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.  We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at

Comments (6)

  1. SomewhereSomehow says:

    Absolutely great example!

    Very helpful, thank you so much!

  2. jose says:


    I started to work with scriptdom and your posts have been valuable to learn about this.

    I started making additions to the sample script you provided but not having the expected behaviour. I.e. I wanted to count for the number of 'alter' as well as to obtain the alter script

    Added something like this:

      // ALTERs

           public override void ExplicitVisit(AlterTableDropTableElement node)


               Console.WriteLine("found ALTER statement with text: " + GetNodeTokenText(node));



    sql file content:

    ALTER TABLE [dbo].[Test] DROP CONSTRAINT [DF_Test_Test1];

    However, the script content written to the console was just:  DF_Test_Test1 as opposed to the full statement.

    In the debugger, I have sen that the fragment.FirstTokenIndex and fragment.LastTokenIndex was the same

    Am I doing something wrong?

    Many thanks Jose

  3. @Jose, you need to visit AlterTableDropTableElementStatement instead of the AlterTableDropTableElement that you are trying.

  4. Sudhakar says:

    Likewise, is their any option to count number of columns accessed through select statement ?

    1. Sure… look for the ColumnReferenceExpression classes in the SelectStatement. You may find it easier if you visualized T-SQL using my tool at

  5. Rajendra says:

    Hi Arvind S,

    I just started working with TransactSql.ScriptDOM. I needed to check any parser errors, stripout comments and check whether the given query is a sql query or an SP or a function etc. I have two code bases. One is VS 2008 version & another VS 2015 version. With VS 2015 I can able to use TransactSql.ScriptDOM.dll version (14.0…) but not in VS 2008. In VS 2008 project I have tried using each of its multiple versions of dll from 11.0.2100.60 to 14.0… but none of those dlls worked in my VS 2008 project. Can you please suggest me the proper version TransactSql.ScriptDOM.dll for VS 2008 project. I’ll be very thankful to you.

    thank you,

Skip to main content