Counting logical lines of T-Sql code in a DacPac

There are two general ways of counting lines of code, the first is basically just count the lines in each file that make up a project, the second is to count the logical statements as specified by the language.

Counting lines of T-SQL code is typically quite hard as, personally, I am interested in things like stored procedures and functions and their statements as a metric for lines of code rather than including columns and tables as, for me, they are separate.
I thought this would be a great example of how to use the DacFx API and the T-Sql script dom together to create a tool that I, and hopefully the other person who has visited this blog, would find useful!

What this tool does is count the logical lines of code in any stored procedure, function (scalar or table-valued) and dml triggers, if anyone thinks of other items of code then it is open source so feel free to check it out, add it in and submit a pull request, it is really trivial to add other types of code.

To count the number of statements, I use the T-Sql visitor class to break the each code file into statements.

Feel free to download the tool from:

https://raw.githubusercontent.com/GoEddie/DacFxApi/master/Download/CountLinesOfSqlCodeInDacPac.zip

The source code is in my DacFx Api github repro:

https://github.com/GoEddie/DacFxApi

How does this work?

The first step is to open the dacpac file:

var model = new TSqlModel(fileName, DacSchemaModelStorageType.File);

I then need to query the model for the Objects of the specific type I am looking for:

model.GetObjects(DacQueryScopes.Default, Procedure.TypeClass)
model.GetObjects(DacQueryScopes.Default, ScalarFunction.TypeClass)

Each of these return an IEnumerable<TSqlObject> which I pass to a helper function to query for the name and the script:

Name = codeUnit.Name.ToString()
var script = “”;
if (codeUnit.TryGetScript(out script))

return script;

Once I have the names and contents of all the scripts I am interested in, I need to parse the scripts and break them into statements. This is made really simple using the TSqlFragmentVisitor class.

The way the TSqlFragmentVisitor class works is that you create a class that inherits from it:

public class SqlVisitor : TSqlFragmentVisitor
{
}

There are then a set of methods you can override which are called everytime a statement of that type is encountered, so for example if you use:

public override void Visit(TSqlStatement node)
{
}

This will be called for every statement, it is what I use in this tool as I don’t care what type of statement it is, as long as it is a statement.

There are other override-able methods such as:

public override void ExplicitVisit(SelectStatement node)

This is only called for select statements so it really is powerful and so simple to use!

Once I have the statement count, I tally up the lines of code for all code units and I end up with the total lines of logical code for just code units in a dacpac file!

This is obviously really basic, if you have an example where the lines of code is incorrect, send it to me and I will fix the tool or fix it yourself and send a pull request on github!

Happy DaxFx + TransactSql ScriptDom’ing!

 

p.s. to see how many lines in each code unit, use the /detailed switch!

Advertisements