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

Implementation

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

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 ).

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

Conclusion

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(); 
            sqlFragment.Accept(myVisitor);
            myVisitor.DumpStatistics(); 
        } 
    }
    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++) 
            { 
                tokenText.Append(fragment.ScriptTokenStream[counter].Text); 
            }
            return tokenText.ToString(); 
        }
        // SELECTs 
        public override void ExplicitVisit(SelectStatement node) 
        { 
            //Console.WriteLine("found SELECT statement with text: " + GetNodeTokenText(node)); 
            SELECTcount++; 
        }
        // INSERTs 
        public override void ExplicitVisit(InsertStatement node) 
        { 
            INSERTcount++; 
        }
        // UPDATEs 
        public override void ExplicitVisit(UpdateStatement node) 
        { 
            UPDATEcount++; 
        }
        // DELETEs 
        public override void ExplicitVisit(DeleteStatement node) 
        { 
            DELETEcount++; 
        }
        public void DumpStatistics() 
        { 
            Console.WriteLine(string.Format("Found {0} SELECTs, {1} INSERTs, {2} UPDATEs & {3} DELETEs", 
                this.SELECTcount, 
                this.INSERTcount, 
                this.UPDATEcount, 
                this.DELETEcount)); 
        } 
    } 
} 

Disclaimer

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.