Use ScriptDom to Parse SQL Script

Microsoft provides a ScriptDom library that you can use to parse T-SQL script. It can be used with any CLR languages to quickly write your own SQL parser. This blog uses c# in its examples.

Installation
First, you need to make sure .NET framework 4.0 or newer version is installed. Then make sure you have ScriptDom installed. After the SQL server 2012 full installation, it is found on my PC in folder

C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.TransactSql.ScriptDom.resources\v4.0_12.0.0.0_zh-Hans_89845dcd8080cc91\Microsoft.SqlServer.TransactSql.ScriptDom.Resources.dll

If you can’find it, follow the link here to download and install it.

Tutorials to Follow
Then you can follow some very good tutorials and documents:

Example Code
The follow example is a c# program that parses the passed in sql script file and prints out the update and delete statements that don’t have where clauses. Remember to add the ScriptDom.dll as its reference.

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

namespace sqlValidator
{
    class Program
    {
        static void Main(string[] args)
        {
            if (args.Length != 1 || String.Compare(args[0].ToLower(), "-h") == 0 || String.Compare(args[0].ToLower(), "-help") == 0)
            {
                Console.WriteLine("Usage: sqlValidator sqlScriptFilePath");
                return;
            }
            IList<ParseError> errors = null;

            TextReader reader = new StreamReader(@args[0]);

            TSql110Parser parser = new TSql110Parser(true);
            TSqlFragment tree = parser.Parse(reader, out errors);

            foreach (ParseError err in errors)
            {
                Console.WriteLine(err.Message);
            }

            Console.WriteLine("Validation starting");
            tree.Accept(new myVisitor());
            Console.WriteLine("Validation completed");
            reader.Dispose();
        }
    }

    class myVisitor : TSqlFragmentVisitor
    {
        public override void ExplicitVisit(UpdateStatement node)
        {
            if (node.UpdateSpecification.WhereClause == null) {
                Console.WriteLine("Missing where clause in the update statement at startLine " + node.StartLine + " and startColumn " + node.StartColumn);
            }
            base.Visit(node);
        }

        public override void ExplicitVisit(DeleteStatement node)
        {
            if (node.DeleteSpecification.WhereClause == null)
            {
                Console.WriteLine("Missing where clause in the delete statement at startLine " + node.StartLine + " and startColumn " + node.StartColumn);
            }
            base.Visit(node);
        }

        public override void ExplicitVisit(DropTableStatement node)
        {
            Console.WriteLine("Found a drop table statement at startLine " + node.StartLine + " and startColumn " + node.StartColumn);
            base.Visit(node);
        }

    }
}
Advertisements
This entry was posted in Database, Uncategorized and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s