Open Source code coverage tool for T-SQL, SQL Server 2008+

What is code coverage?

Code coverage is a way to see how many statements in your database code have been executed when you ran your tests(s). It is a way to see how well covered with tests a particular area is - the better covered with tests, the less likely you will miss issues when you make changes in those areas.

What is code coverage for SQL Server?

SQL Server gives us a great tracing mechanism, either profiler or extended events which details exactly which statements have been run. SQL Server doesn't do so well at telling us what statements we could possibly run in some code but the Transact Sql Script Dom that is part of the DacFx does give us the ability to break T-SQL code into statements so combining the two we have the ability to take a stored procedure such as:

create procedure abc.def

as

--select 100 from a_table but_is_commented_out_so_do_not_count_me

select 100;

select 200

select 300

begin

select 400;

end

if 1=2

begin

select 500

end

go

and we can use the scriptdom to tell us that there are 6 statements or "lines of code" in this procedure and when we run it while covering it we get the details that all of the statements apart from "select 500" are run which gives us a code coverage of 5 out of 6 statements.

Who decides what a statement is?

Partly me but mostly SQL Server itself, if you run a trace with SQL Server/sp_statement_xx you can pretty easily see that the select's are statements, the begin/end by itself is not a statement and the if is a statement - this makes sense to me, the begin/end doesn't actually do anything it is just to help you keep your code tidy, for example this is valid SQL and is only 1 statement:

begin

begin

begin

begin

select 'wowsers'

end

end

end

end


Where can this be used

This version of code coverage is a .net dll with some examples in powershell so it can be used with any build server or test framework, but it was written primarily for tSQLt and has some filtering to filter out tSQLt objects and test procedures so anything that is in a tSQLt test class will be ignored. It was also written to fit into the Redgate DLM automation suite so if you have that then I would recommend using it.

How do I use it?

This first example I will use the SQLRelease module from the DLM suite...

• 1. Grab the zip from: https://the.agilesql.club/SQLCover/download.php
• 2. In powershell dot source the "SQLCover.ps1" file
• 3. Call "Get-CoverRedgateCITest", you will need to pass in the path to the "SQLCover.dll", the connection string to the database, the path to the nuget package to test and a server and database where the code can be deployed to.
• 4. The return from Get-CoverRedgateCITest is a 2 object array, the first is the Redgate results from the tests themselves "RedGate.SQLRelease.Compare.SchemaTesting.TestResults" which can output a junit xml file. The second is a SQLCover.CoverageResult object which as well as giving the count of statements in the database and covered statements while running the tests lets you output the code coverage results as xml or in the open coverage xml format which can be converted to a pretty report using the https://github.com/danielpalme/ReportGenerator tool.

In powershell code that is:

. .\SQLCover.ps1

$results = Get-CoverRedgateCITest ".\SQLCover.dll" "server=.;initial catalog=database_name;integrated security=sspi;" ".\path\to\nuget.nupkg" "." "database_name"

Export-OpenXml $results[1] "c:\output\path\for\xml\results"

Start-ReportGenerator "c:\output\path\for\xml\results" "c:\path\to\reportgenerator.exe"


You will need to obviously provide the correct paths, a nupkg file and if you want a pretty report then reportgenerator.exe.

If you don't have SQLRelease then you can do the same thing but you must deploy the code yourself first:

• 1. Grab the zip from: https://the.agilesql.club/SQLCover/download.php
• 2. In powershell dot source the "SQLCover.ps1" file
• 3. Call "Get-CoverTSql" this returns a CoverageResult object which as well as giving the count of statements in the database and covered statements while running the tests lets you output the code coverage results as xml or in the open coverage xml format which can be converted to a pretty report using the https://github.com/danielpalme/ReportGenerator tool.

In powershell code that is:

. .\SQLCover.ps1

$result = Get-CoverTSql ".\SQLCover.dll" "server=.;initial catalog=database_name;integrated security=sspi;" "database_name" "exec tSQLt.RunAll"

Export-OpenXml $result "c:\output\path\for\xml\results"

Start-ReportGenerator "c:\output\path\for\xml\results" "c:\path\to\reportgenerator.exe"


If you wanted to call an exe, for example an nunit test runner or msbuild then instead of calling Get-CoverTsql you can call Get-CoverExe which will start the coverage, then start the exe and stop the coverage when it completes. If you want to completely control when coverage sessions are started or stopped then SQLCover.dll has a .Start() and .Stop() methods, see Get-CoverRedgateCITest for examples.

What output do you get?

You get a CoverageResult object which has two public properties:

public long StatementCount;

public long CoveredStatementCount;


These give you the ability to react to a poor coverage result or you can convert the CoverageResult to:

• 1. Basic Html report "$result.Html()"
• 2. Open Cover Xml format which can be converted to something pretty or you can parse it yourself

I would strongly suggest you grab a copy of reportgenerator so you get reports that look like:

Oooh pretty....

SSDT Dev Pack

The ssdt dev pack already has code coverage but it is only within an ssdt project and it isn't possible to generate code coverage results as part of a build process so although I will still work on that, I will migrate that over to using this at some point.

Finally.

I just wanted to say a big thanks to David Atkinson at Redgate, he sponsored the project which meant that I could focus on it and provided some great feedback and guidance along the way 🙂