In the last post, I’ve shown how you can use the Microsoft libraries to parse your SQL Server code in C#. You can use the same parser to reformat your code, but that would be a lot of work: parse the code, check all structures and reformat the code according to your needs.

But there is an easier way: the SqlScriptGenerator class. This class makes a breeze to get your unformatted code and reformat it according to your options. In this article, we will develop a WPF program that takes a SQL Code snippet typed in TextBox and reformats it using the options you give.

Create a WPF program and name it SqlReformatter. In the solution explorer, add the NuGet package Microsoft.SqlServer.TransactSql.ScriptDom. In the XAML file for the Main page, add this code:

<Window x:Class="SqlReformatter.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        mc:Ignorable="d"
        Title="MainWindow" Height="700" Width="1024">
    <Grid>
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="200"/>
            <ColumnDefinition Width="*"/>
        </Grid.ColumnDefinitions>
        <StackPanel Grid.Column="0" Margin="5" x:Name="stackChecks">
            <CheckBox Content="AlignClauseBodies" Click="OptionClick"/>
            <CheckBox Content="AlignColumnDefinitionFields" Click="OptionClick"/>
            <CheckBox Content="AlignSetClauseItem" Click="OptionClick"/>
            <CheckBox Content="AsKeywordOnOwnLine" Click="OptionClick"/>
            <CheckBox Content="IncludeSemicolons" Click="OptionClick"/>
            <CheckBox Content="IndentSetClause" Click="OptionClick"/>
            <CheckBox Content="IndentViewBody" Click="OptionClick"/>
            <CheckBox Content="MultilineInsertSourcesList" Click="OptionClick"/>
            <CheckBox Content="MultilineInsertTargetsList" Click="OptionClick"/>
            <CheckBox Content="MultilineSelectElementsList" Click="OptionClick"/>
            <CheckBox Content="MultilineSetClauseItems" Click="OptionClick"/>
            <CheckBox Content="MultilineViewColumnsList" Click="OptionClick"/>
            <CheckBox Content="MultilineWherePredicatesList" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeCloseParenthesisInMultilineList" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeFromClause" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeGroupByClause" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeHavingClause" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeJoinClause" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeOffsetClause" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeOpenParenthesisInMultilineList" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeOrderByClause" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeOutputClause" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeWhereClause" Click="OptionClick"/>
            <StackPanel Margin="0,5">
                <TextBlock Text="KeywordCasing"/>
                <ComboBox SelectionChanged="CaseChanged" x:Name="cbxCase">
                    <ComboBoxItem>LowerCase</ComboBoxItem>
                    <ComboBoxItem>UpperCase</ComboBoxItem>
                    <ComboBoxItem>PascalCase</ComboBoxItem>
                </ComboBox>
            </StackPanel>
            <StackPanel Orientation="Horizontal" Margin="0,5">
                <TextBlock Text="IndentationSize"/>
                <TextBox Width="60" Margin="5,0" TextChanged="IndentChanged" x:Name="txtIdent"/>
            </StackPanel>
        </StackPanel>
        <Button VerticalAlignment="Bottom" HorizontalAlignment="Right" Grid.Column="0"
                Margin="5" Content="Reformat" Click="ReformatSqlClick"/>
        <Grid Grid.Column="1">
            <Grid.RowDefinitions>
                <RowDefinition Height="*"/>
                <RowDefinition Height="*"/>
            </Grid.RowDefinitions>
            <TextBox x:Name="SourceBox" Margin="5" Grid.Row="0"/>
            <TextBox x:Name="DestBox" Margin="5" Grid.Row="1" IsReadOnly="True"/>
        </Grid>
    </Grid>
</Window>

As you can see, we are adding at the right a set of Checkboxes, each one with one property of the SqlScriptOptions class. When we check each box, we will change the reformatting options. To do that, we must use this code:

private void OptionClick(object sender, RoutedEventArgs e)
{
    if (sender is CheckBox checkBox)
    {
        var option = checkBox.Content.ToString();
        PropertyInfo pinfo = typeof(SqlScriptGeneratorOptions).GetProperty(option);
        pinfo?.SetValue(_options, checkBox.IsChecked == true);
    }
}

In this case, we are using reflection to get the property corresponding to the clicked Checkbox and we set its value according to the IsChecked value. We must also add two event handlers, for the case combobox and for the indent textbox:

private void CaseChanged(object sender, SelectionChangedEventArgs e)
{
    var selectedCase = (sender as ComboBox)?.SelectedIndex;
    if (selectedCase != null)
        _options.KeywordCasing = (KeywordCasing) selectedCase;
}

private void IndentChanged(object sender, TextChangedEventArgs e)
{
    if (int.TryParse((sender as TextBox)?.Text, out int size))
        _options.IndentationSize = size;
}

Once we have this set, we need to add the code to the click of the Reformat  button:

private void ReformatSqlClick(object sender, RoutedEventArgs e)
{
    var sqlSrc = SourceBox.Text;
    if (string.IsNullOrWhiteSpace(sqlSrc))
        return;
    var processed = ParseSql(sqlSrc);
    if (processed.errors.Any())
    {
        var sb = new StringBuilder("Errors found:");
        foreach (var error in processed.errors)
        {
            sb.AppendLine($"     Line: {error.Line}  Col: {error.Column}: {error.Message}");
        }
    }
    else
    {
        var scriptGenerator = new Sql150ScriptGenerator(_options);
        scriptGenerator.GenerateScript(processed.sqlTree, out string sqlDst);
        DestBox.Text = sqlDst;
    }
}

We parse the code in the source box, and if there are any errors, we show them in the destination box. If there are no errors, the code is reformatted according to the selected options. The ParseSql method is similar to the one shown in the last article:

private static (TSqlFragment sqlTree, IList<ParseError> errors) ParseSql(string procText)
{
    var parser = new TSql150Parser(true);
    using (var textReader = new StringReader(procText))
    {
        var sqlTree = parser.Parse(textReader, out var errors);

        return (sqlTree, errors);
    }
}

Now, we only need to initialize the UI in the beginning, so all the options are up-to-date with the SqlScriptGeneratorOptions instance:

public MainWindow()
{
    InitializeComponent();
    _options = new SqlScriptGeneratorOptions();
    txtIdent.Text = _options.IndentationSize.ToString();
    cbxCase.SelectedIndex = (int) _options.KeywordCasing;
    foreach (var child in stackChecks.Children)
    {
        if (child is CheckBox check)
        {
            var checkContent = check.Content.ToString();
            PropertyInfo pinfo = typeof(SqlScriptGeneratorOptions).GetProperty(checkContent);
            check.IsChecked = (bool?)pinfo?.GetValue(_options) == true;
        }

    }
}

This code also uses reflection to get the property values and fill the data in the boxes. When you run this program, you have something like this:

You can change any options and click the Reformat button and the code will be reformatted accordingly.

Conclusions

As you can see, with very little code you can create a SQL Reformatter to reformat your code and make sure that it agrees with your standards. This formatter has many options and can be used also to parse Sql source code.

All the source code for this article is at https://github.com/bsonnino/SqlReformatter

In the last post, I’ve shown how to parse SQL Server code with C# and get all tokens in it, showing their types. This is very nice, there is a lot you can do with that, but there is a pitfall: you don’t have the context the token was used. For example, you have an identifier, but you don’t know if it is a parameter or a variable declared in the procedure. You don’t know its type and where it is used.

Sometimes you need more information about the token and you can’t get it unless you analyze the code. Fortunately, Microsoft has already done that for us and provided a wonderful tool, so we can get the context of the tokens: you can create a new class that inherits from TSqlFragmentVisitor and override its methods, to visit the node types you want (if this doesn’t make sense to you right now, keep on reading, you’ll see it’s very simple).

The TSqlFragmentVisitor class is a massive class that uses the Visitor Pattern to analyze the SQL tree and visit all the nodes, so you can take action on the nodes that come to your attention. If you take a look at it, you will see that there are a huge number of Visit and ExplicitVisit overrrides, one for each kind of node you can have in a SQL Server procedure. Using Visit is similar to ExplicitVisit, with the difference that with ExplicitVisit you can control if the children nodes are also visited.

To use it, you must parse the tree in the same way you did in the last article and then create a visitor instance and pass it in the Accept method. This code shows how this is done:

static void Main(string[] args)
{
    var sql = "Select * from customer";
    var parsed = ParseSql(sql);
    if (parsed.errors.Any())
        return;
    var visitor = new SelectVisitor();
    parsed.sqlTree.Accept(visitor);
    Console.ReadLine();
}

private static (TSqlFragment sqlTree, IList<ParseError> errors) ParseSql(string procText)
{
    var parser = new TSql150Parser(true);
    using (var textReader = new StringReader(procText))
    {
        var sqlTree = parser.Parse(textReader, out var errors);

        return (sqlTree, errors);
    }
}

The visitor class is something like this:

internal class SelectVisitor : TSqlFragmentVisitor
{
    public override void Visit(SelectStatement node)
    {
        Console.WriteLine($"Visiting Select: {node}");
    }

    public override void Visit(QueryExpression node)
    {
        Console.WriteLine($"Visiting QueryExpression: {node}");
    }

    public override void Visit(QuerySpecification node)
    {
        Console.WriteLine($"Visiting QuerySpecification: {node}");
    }

    public override void Visit(SelectStarExpression node)
    {
        Console.WriteLine($"Visiting SelectStarExpression: {node}");
    }
}

Running this code, you will get something like this:

As you can see, the node and its children are visited and we have no control on that. If you want to have some control, you must use the ExplicitVisit:

internal class SelectVisitor : TSqlFragmentVisitor
{
    public override void ExplicitVisit(SelectStatement node)
    {
        Console.WriteLine($"Visiting Select: {node}");
    }

    public override void Visit(QueryExpression node)
    {
        Console.WriteLine($"Visiting QueryExpression: {node}");
    }

    public override void Visit(QuerySpecification node)
    {
        Console.WriteLine($"Visiting QuerySpecification: {node}");
    }

    public override void Visit(SelectStarExpression node)
    {
        Console.WriteLine($"Visiting SelectStarExpression: {node}");
    }
}

As you can see, the child nodes are not visited. To visit them, you must call the base method, like this:

internal class SelectVisitor : TSqlFragmentVisitor
{
    public override void ExplicitVisit(SelectStatement node)
    {
        Console.WriteLine($"Visiting Select: {node}");
        base.ExplicitVisit(node);
    }

    public override void ExplicitVisit(QueryExpression node)
    {
        Console.WriteLine($"Visiting QueryExpression: {node}");
    }

    public override void ExplicitVisit(QuerySpecification node)
    {
        Console.WriteLine($"Visiting QuerySpecification: {node}");
    }

    public override void ExplicitVisit(SelectStarExpression node)
    {
        Console.WriteLine($"Visiting SelectStarExpression: {node}");
    }
}

Nice, no? With this simple code, you can write something to check if any procedure in your database has a “select *”, which can be flagged as an error. Just override the Visit for the SelectStarExpression and flag the error if it’s visited.

Generating statistics from the database procedures

With this knowledge, we can write a program that generates statistics from the database procedures. We want to know how many inserts, deletes and updates there are in the procedures and what are the tables used with these commands. We also can know how many tables are created and dropped in the procedures.

For that, create a new console application and name it DataStats Add the NuGet package Microsoft.SqlServer.TransactSql.ScriptDom and add this code to Program.cs:

     static void Main(string[] args)
     {
         using (var con = new SqlConnection("Server=.;Database=WideWorldImporters;Trusted_Connection=True;"))
         {
             con.Open();
             var procTexts = GetStoredProcedures(con)
                 .Select(n => new { ProcName = n, ProcText = GetProcText(con, n) })
                 .ToList();
             var procTrees = procTexts.Select(p =>
             {
                 var processed = ParseSql(p.ProcText);
                 var visitor = new StatsVisitor();
                 if (!processed.errors.Any())
                     processed.sqlTree.Accept(visitor);
                 return new { p.ProcName, processed.sqlTree, processed.errors, visitor };
             }).ToList();
             foreach (var procTree in procTrees)
             {
                 Console.WriteLine(procTree.ProcName);
                 if (procTree.errors.Any())
                 {
                     Console.WriteLine("   Errors found:");
                     foreach (var error in procTree.errors)
                     {
                         Console.WriteLine($"     Line: {error.Line}  Col: {error.Column}: {error.Message}");
                     }
                 }
                 else
                 {
                     var visitor = procTree.visitor;
                     Console.WriteLine($"  Inserts: {visitor.Inserts}");
                     foreach (var table in visitor.InsertTables)
                     {
                         Console.WriteLine($"      {table}");
                     }

                     Console.WriteLine($"  Updates: {visitor.Updates}");
                     foreach (var table in visitor.UpdateTables)
                     {
                         Console.WriteLine($"      {table}");
                     }

                     Console.WriteLine($"  Deletes: {visitor.Deletes}");
                     foreach (var table in visitor.DeleteTables)
                     {
                         Console.WriteLine($"      {table}");
                     }

                     Console.WriteLine($"  Creates: {visitor.Creates}");
                     foreach (var table in visitor.CreateTables)
                     {
                         Console.WriteLine($"      {table}");
                     }

                     Console.WriteLine($"  Drops: {visitor.Drops}");
                     foreach (var table in visitor.DropTables)
                     {
                         Console.WriteLine($"      {table}");
                     }
                 }
             }
         }

         Console.ReadLine();
     }

     private static List<string> GetStoredProcedures(SqlConnection con)
     {
         using (SqlCommand sqlCommand = new SqlCommand(
             "select s.name+'.'+p.name as name from sys.procedures p " + 
             "inner join sys.schemas s on p.schema_id = s.schema_id order by name",
             con))
         {
             using (DataTable procs = new DataTable())
             {
                 procs.Load(sqlCommand.ExecuteReader());
                 return procs.Rows.OfType<DataRow>().Select(r => r.Field<String>("name")).ToList();
             }
         }
     }

     private static string GetProcText(SqlConnection con, string procName)
     {
         using (SqlCommand sqlCommand = new SqlCommand("sys.sp_helpText", con)
         {
             CommandType = CommandType.StoredProcedure
         })
         {
             sqlCommand.Parameters.AddWithValue("@objname", procName);
             using (var proc = new DataTable())
             {
                 try
                 {
                     proc.Load(sqlCommand.ExecuteReader());
                     return string.Join("", proc.Rows.OfType<DataRow>().Select(r => r.Field<string>("Text")));
                 }
                 catch (SqlException)
                 {
                     return null;
                 }
             }
         }
     }

     private static (TSqlFragment sqlTree, IList<ParseError> errors) ParseSql(string procText)
     {
         var parser = new TSql150Parser(true);
         using (var textReader = new StringReader(procText))
         {
             var sqlTree = parser.Parse(textReader, out var errors);

             return (sqlTree, errors);
         }
     }
 }

This code is very similar to the one in the last post, but it will create a visitor and will call the Accept method to visit its nodes. The visitor’s code is:

internal class StatsVisitor : TSqlFragmentVisitor
{
    public int Inserts { get; private set; }
    public int Updates { get; private set; }
    public int Deletes { get; private set; }
    public int Creates { get; private set; }
    public int Drops { get; private set; }
    public List<string> InsertTables { get; }
    public List<string> UpdateTables { get; }
    public List<string> DeleteTables { get; }
    public List<string> CreateTables { get; }
    public List<string> DropTables { get; }

    public StatsVisitor()
    {
        InsertTables = new List<string>();
        UpdateTables = new List<string>();
        DeleteTables = new List<string>();
        CreateTables = new List<string>();
        DropTables = new List<string>();
    }

    public override void Visit(InsertStatement node)
    {
        Inserts++;
        InsertTables.Add((node.InsertSpecification.Target as NamedTableReference)?.
            SchemaObject.BaseIdentifier.Value);
    }

    public override void Visit(UpdateStatement node)
    {
        Updates++;
        UpdateTables.Add((node.UpdateSpecification.Target as NamedTableReference)?.
            SchemaObject.BaseIdentifier.Value);
    }

    public override void Visit(DeleteStatement node)
    {
        Deletes++;
        UpdateTables.Add((node.DeleteSpecification.Target as NamedTableReference)?.
            SchemaObject.BaseIdentifier.Value);
    }

    public override void Visit(CreateTableStatement node)
    {
        Creates++;
        CreateTables.Add(node.SchemaObjectName.BaseIdentifier.Value);
    }

    public override void Visit(DropTableStatement node)
    {
        Drops++;
        DropTables.AddRange(node.Objects.Select(o => o.BaseIdentifier.Value));
    }

}

When there is an Insert, Delete, Update, Create Table or Drop Table, the corresponding method will be called and the properties referring to the node will be updated. At the end you will have the statistics for all the procedures in the database:

There is just a minor glitch in this code: as you can see in the Inserts, there is a blank line. That is due to the fact that the insert target isn’t a table, but a variable. This code solves the issue:

public override void Visit(InsertStatement node)
{
    Inserts++;
    var targetName = (node.InsertSpecification.Target as NamedTableReference)?.
        SchemaObject.BaseIdentifier.Value ??
      (node.InsertSpecification.Target as VariableTableReference)?.Variable.Name;
    InsertTables.Add(targetName);
}

Conclusions

As you can see, Microsoft has put a lot of work in this parser, you can have a lot of power when working with SQL Server code in C# with this parser. The options are endless: check quality of the code, refactor code, make statistics, and so on. In the next post we will see a simple way to reformat your SQL Server code, so you can ensure that the code follows your standards. See you then.

The code for this article is in https://github.com/bsonnino/SqlParsing

When you are using SQL Server stored procedures, you get a lot of flexibility in your code – changing the code doesn’t need a recompile, deploying the new or updated procedures is as easy as sending and running text scripts, and so on. I won’t enter on the pros and cons of keeping code in the database, as this will open a very long discussion, with no winners.

In this article, I will focus on one aspect of the SQL Server code, the lack of rigid rules for the code. It all starts with being case insensitive, something like “SELECT * from customer” is the same of “select * FROM customer”. It’s not a great deal, but when you have a lot of stored procedures and many people maintaining it, you may need to enforce some standards. In this article I will show you how to use C# to parse SQL Server code and enforce some coding standards in your SQL Server procedures.

Retrieving the stored procedures from the database

The first step for processing the stored procedures is to retrieve them from the database. For this article, we will be using the WorldWideImporters sample database, that can be downloaded from https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0.

To get the names of all procedures in the database, you must use a command like this one:

select s.name+'.'+p.name as name from sys.procedures p 
inner join sys.schemas s on p.schema_id = s.schema_id 
order by name

Once you have the names of all the procedures in the database, you can get their code with the procedure sys.sp_helpText, passing the procedure name as a parameter. This code will get all procedures from the database and store them in a list:

static void Main()
{
	using (var con = new SqlConnection("Server=.;Database=WideWorldImporters;Trusted_Connection=True;"))
	{
		con.Open();
		var procTexts = GetStoredProcedures(con).Select(n => new {ProcName = n, ProcText = GetProcText(con, n)}).ToList();
	}
}

private static List<string> GetStoredProcedures(SqlConnection con)
{
	using (SqlCommand sqlCommand = new SqlCommand("select s.name+'.'+p.name as name from sys.procedures p " +
	  "inner join sys.schemas s on p.schema_id = s.schema_id order by name", con))
	{
		using (DataTable procs = new DataTable())
		{
			procs.Load(sqlCommand.ExecuteReader());
			return procs.Rows.OfType<DataRow>().Select(r => r.Field<String>("name")).ToList();
		}
	}
}

private static string GetProcText(SqlConnection con, string procName)
{
	using (SqlCommand sqlCommand = new SqlCommand("sys.sp_helpText", con)
	{
		CommandType = CommandType.StoredProcedure
	})
	{
		sqlCommand.Parameters.AddWithValue("@objname", procName);
		using (var proc = new DataTable())
		{
			try
			{
				proc.Load(sqlCommand.ExecuteReader());
				return string.Join("", proc.Rows.OfType<DataRow>().Select(r => r.Field<string>("Text")));
			}
			catch (SqlException)
			{
				return null;
			}
		}
	}
}

This code will open a connection to the database, get all procedure names and then get their text, returning a list of an anonymous class with two members: ProcName and ProcText. You can then process all the procedures in the database.

Parsing the procedures

The next step is to parse the procedures to split into the containing parts. This is not an easy task to make from scratch. The SQL Server language is not easy and evolves with each version of SQL Server. There are many ways to do the same thing, so writing a parser is not something I would recommend. Fortunately, Microsoft has already written a parser for us, and it’s made available via NuGet package. To use it, just add the Microsoft.SqlServer.TransactSql.ScriptDom package to your app. When you add the NuGet package to the app you will see it’s not an official package: this dll is part of a larger package, Microsoft.SqlServer.DacFx.x64 and it’s there only to make the number of dlls added smaller.

Once you add the package, you can use this code to parse the code of the procedure:

private static (TSqlFragment sqlTree, IList<ParseError> errors) ParseSql(string procText)
{
	var parser = new TSql150Parser(true);
	using (var textReader = new StringReader(procText))
	{
		var sqlTree = parser.Parse(textReader, out var errors);

		return (sqlTree, errors);
	}
}

This method uses a new feature of C# 7.0, ValueTuples. If you are using .NET version 4.7 or higher, you don’t need to add anything to use it. If you are using an older version, you must add the System.ValueTuple NuGet package to your project. At the beginning, we instantiate a TSql150Parser class. Depending on the version of SQL server that you want to parse, you can choose a different parser class. Then, we call the Parse method, that will parse the code, return any errors in the errors variable and return a TSqlFragment with the parsed data. Just with this simple code, you already have many benefits: you can parse the procedure for many versions of SQL Server, check if the procedure has errors in it and parse the procedure into its components. The returned tree is of type TSqlScript, that has some interesting properties:

  • Batches – gets the batches in the procedure, so you can process them individually
  • FirstTokenIndex – shows where the fragment begins – for the script, it usually starts at 0, but when you are working with other kinds of fragments, that may not be the case
  • FragmentLength – size of the fragment, in bytes
  • LastTokenIndex – shows where the fragment ends
  • ScriptTokenStream – the list of parsed tokens
  • StartColumn – start column of the fragment
  • StartLine – start line of the fragment
  • Offset – offset in bytes from the start

As you can see, there is already a lot of information there. When you take a look at the ScriptTokenStream, you can see a lot more there:

The SQL code is parsed into tokens and every token is categorized. With this code, you can group the tokens and have brief statistics of your code:

static void Main()
{
	using (var con = new SqlConnection("Server=.;Database=WideWorldImporters;Trusted_Connection=True;"))
	{
		con.Open();
		var procTexts = GetStoredProcedures(con).Select(n => 
		  new { ProcName = n, ProcText = GetProcText(con, n) }).ToList();
		var procTrees = procTexts.Select(p =>
		{
			var processed = ParseSql(p.ProcText);
			return new { p.ProcName, processed.sqlTree, processed.errors };
		});
		var procStats = procTrees
		  .SelectMany(p => p.sqlTree.ScriptTokenStream)
		  .GroupBy(t => t.TokenType)
		  .Select(g => new { Key = g.Key, Count = g.Count()})
		  .OrderByDescending(g => g.Count);
	}
}

With this code, we can see something like this in the database:

WhiteSpace              15693
Identifier               3571
Variable                 1976
Plus                     1667
UnicodeStringLiteral     1192
Dot                      1085
Semicolon                1051
Comma                     957
LeftParenthesis           937
RightParenthesis          937

We can see the variable names used with this code:

static void Main()
{
	using (var con = new SqlConnection("Server=.;Database=WideWorldImporters;Trusted_Connection=True;"))
	{
		con.Open();
		var procTexts = GetStoredProcedures(con).Select(n => 
		  new { ProcName = n, ProcText = GetProcText(con, n) }).ToList();
		var procTrees = procTexts.Select(p =>
		{
			var processed = ParseSql(p.ProcText);
			return new { p.ProcName, processed.sqlTree, processed.errors };
		});
		var procVariables = procTrees
		  .SelectMany(p => p.sqlTree.ScriptTokenStream)
		  .Where(t => t.TokenType == TSqlTokenType.Variable)
		  .GroupBy(t => t.Text)
		  .Select(g => g.Key)
		  .OrderBy(t => t);
	}
}

If you run this code, you will see that, although there are 1976 tokens of type Variable, there are only 76 different names used.One thing that called me my attention is that there are two similar variables used, @Crlf and @CrLf. We can use this parsing to know in which procedures these variables are used. Note that this is different than a text search: a text search could find the variables inside a comment or in a string. In this case, we will only find the real SQL variables:

static void Main()
{
	using (var con = new SqlConnection("Server=.;Database=WideWorldImporters;Trusted_Connection=True;"))
	{
		con.Open();
		var procTexts = GetStoredProcedures(con).Select(n => new { ProcName = n, ProcText = GetProcText(con, n) }).ToList();
		var procTrees = procTexts.Select(p =>
		{
			var processed = ParseSql(p.ProcText);
			return new { p.ProcName, processed.sqlTree, processed.errors };
		});
		Func<TSqlParserToken, bool> queryVariables = t => t.TokenType == TSqlTokenType.Variable
			  && (t.Text == "@CrLf" || t.Text == "@Crlf");
		var variablesFound = procTrees
		  .Where(p => p.sqlTree.ScriptTokenStream.Any(queryVariables))
		   .Select(t => new { t.ProcName, FoundVariables = t.sqlTree.ScriptTokenStream.Where(queryVariables)});
	}
}

If you run this code, you will see that most of the variables is written as @CrLf, while some are written as @Crlf. We will change all variables to @CrLf with this code:

static void Main()
{
	using (var con = new SqlConnection("Server=.;Database=WideWorldImporters;Trusted_Connection=True;"))
	{
		con.Open();
		var procTexts = GetStoredProcedures(con).Select(n => new { ProcName = n, ProcText = GetProcText(con, n) }).ToList();
		var procTrees = procTexts.Select(p =>
		{
			var processed = ParseSql(p.ProcText);
			return new { p.ProcName, processed.sqlTree, processed.errors };
		});
		var variablesFound = procTrees
		  .Where(p => p.sqlTree.ScriptTokenStream.Any(queryVariables))
		   .Select(t => new { t.ProcName, FoundVariables = t.sqlTree.ScriptTokenStream.Where(queryVariables)});
		foreach (var procName in variablesFound.Select(p => p.ProcName))
		{
			var sqlTree = procTrees.First(p => p.ProcName == procName).sqlTree;
			foreach (var token in sqlTree.ScriptTokenStream.Where(t => t.Text == "@Crlf"))
			{
				token.Text = "@CrLf";
			}
			var changedSql = string.Join("", sqlTree.ScriptTokenStream.Select(t => t.Text).ToArray());
		}

	}
}

ChangedSql has the changed SQL, that can be applied to the database, making the variable names uniform.

Conclusions

As you can see, you can do a lot with this parser, like have statistics, change variable names and so on. But still we haven’t scratched the surface of what can be done with the parser. We will see more in the next article. See you then!