Two free tools to format SQL queries
Tags: sql, productivity, showdev, beginners
Published: 2020-10-19 14:05:04

_I originally published an extended version of this post on [my blog](https://canro91.github.io/2020/09/30/FormatSQL/)._

Do you need to format your SQL queries? Are you doing it by hand? Stop! There is a better way!

Instead of formatting SQL queries to follow code conventions by hand, you can use online tools or plugins inside Visual Studio, SQL Server Management Studio or any other editors. These are two free tools you can use to format your SQL queries and store procedures. Inside Notepad++, you can use **Poor Man's T-SQL Formatter**. And, **ApexSQL Refactor** for Visual Studio and SQL Server Management Studio (SSMS).

## Before

Before using Poor Man's T-SQL Formatter and ApexSQL Refactor, I spent too much time formatting queries by hand. It means making keywords uppercase, aligning columns and arranging spaces.

I tried to use "Find and Replace" inside an editor. But, it only worked for making keywords uppercase. Sometimes I ended up messing with variables, parameters or any other thing inside my queries. 

<figure>
<img src="https://source.unsplash.com/-1_RZL8BGBM/800x400" alt="Macro typewriter ribbon" />

<figcaption><span>Photo by <a href="https://unsplash.com/@kellysikkema?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Kelly Sikkema</a> on <a href="https://unsplash.com/?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></span></figcaption>
</figure>

Things were worse with long store procedures. I changed two lines and I ended up formatting thousand of lines. _"Once you touch it, you're the owner"_.

## A sample query from StackOverflow

Let's format the query to [find StackOverflow posts with many "thank you" answers](https://data.stackexchange.com/stackoverflow/query/886/posts-with-many-thank-you-answers).

```sql
select
   ParentId as [Post Link],
   count(id)
from posts
where posttypeid = 2 and len(body) <= 200
  and (body like '%hank%')
group by parentid
having count(id) > 1
order by count(id) desc;
```

After formatting the query to follow [Simon Holywell SQL Style Guide](https://www.sqlstyle.guide/), it should like this

```sql
SELECT ParentId AS [Post Link]
     , COUNT(id)
  FROM posts
 WHERE posttypeid = 2
   AND LEN(body) <= 200
   AND (body LIKE '%hank%')
 GROUP BY parentid
HAVING COUNT(id) > 1
 ORDER BY COUNT(id) DESC;
```

Let's see how these two tools format our sample query.

### Poor Man's T-SQL Formatter

[Poor Man's T-SQL Formatter](https://github.com/TaoK/PoorMansTSqlFormatter) is a free and open source .NET and JavaScript library to format your SQL queries. It's available for Notepad++, Visual Studio, SQL Server Management Studio and others. You can try its formatting options [online](http://poorsql.com/) too.

This is how Poor Man's T-SQL format our sample query.

<figure>
<img src="https://canro91.github.io/assets/posts/2020-09-30-FormatSQL/PoorManTSQL.PNG" width="600px" alt="Sample query formatted by Poor Man's T-SQL inside Notepad++" />

<figcaption>Sample query formatted by Poor Man's T-SQL inside Notepad++</figcaption>
</figure>

It doesn't make function names uppercase. It indents `AND`'s clauses. But, it's a good starting point. Sometimes, it need a bit of help if the query has single-line comments in it.

### ApexSQL Refactor

[ApexSQL Refactor](https://www.apexsql.com/sql-tools-refactor.aspx) is a free query formatter for Visual Studio and SQL Server Management Studio. It has over 160 formatting options. You can create your own formattig profiles and preview them. It comes with four built-in profiles. Also, you can try it [online](https://sql-formatter.online/options/formatting).

This is how ApexSQL Refactor format our sample query.

<figure>
<img src="https://canro91.github.io/assets/posts/2020-09-30-FormatSQL/ApexSQL.PNG" width="600px" alt="Sample query formatted by ApexSQL Refactor inside Visual Studio" />

<figcaption>Sample query formatted by ApexSQL Refactor inside Visual Studio</figcaption>
</figure>

It isn't perfect either. It indents `AND` clauses too. I couldn't find an option to change it. But, there is an option to indent `ON` in `SELECT` statements with `JOIN`, but it affects `ON` for index creation too.

Voilà! Please, save you some time formatting your SQL queries with any of these two free tools. For other alternatives, check [this SQLShack collection of formatter tools](https://www.sqlshack.com/sql-formatter-tools/).

We can take a step further and call a formatter inside a Git hook to [automatically format SQL files](https://canro91.github.io/2023/09/18/FormatSqlFilesOnCommit/) when committing our files. I did it with Poor Man’s T-SQL formatter.

***

_Hey, there! I'm Cesar, a software engineer and lifelong learner. Visit my [Gumroad page](https://imcsarag.gumroad.com) to download my ebooks and check my courses._

_Happy SQL time!_