I’ve been meaning to get a series of blog posts started on this topic. A twitter conversation from yesterday finally pushed me to it. Last year, I was tasked with finding a linting tool for the large t-sql code base we have at work. I looked into several tools – tsqllint, Sonarqube and several others. We ran into similar issues on all of them. Someone else defining rules for us didn’t work.
One tool called it wrong to use more than 3 tables in a query – we had several with 6-7, maybe even more. Another started to point out lack of indexes on temp tables as a problem (the rule was designed for table scripts but worked the same on code). Getting new rules that we wanted – such as not having unnaming primary keys on temp tables (Querystore doesn’t like them) or having our naming standards enforced meant extra work on someone else’s code. Our rules were custom to our environment. There were generic best practices for sure, such as finding the SELECT * or NOLOCK hints, but those were a small subset of what we needed. I then started looking for a tool with which I could make a custom linter. That’s when I discovered ScriptDOM, which has been around for a really long time with few people knowing or using it. It took me some time to understand how to put this to use. But after I figured it out it was really easy. Now I have a fairly robust, custom linter in place written in PowerShell and integrated well into our Azure DevOps Build process. It is easy to use and it is owned by us.
What is ScriptDOM and what can it do??
ScriptDOM is a .net framework library that now comes with Microsoft DacFx package and can be downloaded from here. All you need to work with it is this one dll called Microsoft.SqlServer.TransactSql.ScriptDom.dll. Its primary uses are as below:
1 Parse t-sql code for syntax errors.
2 Find patterns in t-sql code.
3 Format t-sql code.
4 Fix what it finds (well, somewhat).
ScriptDOM has been around for a long time – since SQL Server 2008, when it used to be part of .NET framework. From SQL Server 2012, it is now shipped with SQL Server and is part of the DacFx package.
Why bother?
Most places use text searches of some sort to search code for patterns. We are all familiar with using sys.sql_modules to find text inside code or use DMV queries to find what we need to find in table or other object definitions. There are many situations for which this may not be adequate. The text search that sys.sql_modules (or any other text search tool provides will include comments and embedded words. DMV queries can only be used after the object is created – there may be situations where you want to find problems with code creating the objects before it is actually created. The main scenarios you may find scriptdom a better choice over text searches/regex/various other methods:
1 Large code base
2 Need to find issues pre rollout
3 Nuanced search conditions
4 Findings that are accurate and provide you with exact location of what you’re looking for.
5 Do all of this asynchronously – without connecting to a SQL Server.
My goal is to teach you how to achieve all this. It is less about telling you what I think are t-sql best practices/anti patterns and so on. There are many blog posts for that and you can and should learn to identify/define your own too. From next blog post I plan to write in detail on how to put this incredibly powerful tool to use. Stay tuned!