< Blog

Query Formatting Tools

By Garth Jones

As many of you know I answer a lot of ConfigMgr query-related questions. One of my pet peeves is seeing poorly formatted queries because they are difficult to read!

For example, take this unformatted WQL query. What is it doing?

Query Formatting Tools-Unformatted Query 

When I see queries similar to this one, I usually do one of the following:

· Manually format the query, so that I can read it.

· Scan the query to try and pick out the important parts.

· Skip the question entirely. When I have time to manually format the query I’ll come back to it, but unfortunately, that could be months from when the question was first posted.

Recently I learned from Mickey Stuewe @SQLMickey about online tools that will format a query for you. What a great idea!

After a quick search, I found Redgate’s @redgate online tool, but after a quick test I discovered that it doesn’t always work with WQL queries. Next, I found a second online tool from SQL Pretty Printer that handles both SQL and WQL, but I’m not a fan of how the SQL Pretty Printer tool handles subqueries.

Despite the SQL Pretty Printer’s shortcomings, I decided to use it in order to demonstrate how the query in the above example would look. Notice how much easier it is to read! Sure it’s not perfect, but it’s a lot easier to see that there’s a subquery in the formatted version.

Formatted (WQL)

Query Formatting Tools-Formatted Query

I’d recommend using the SQL Pretty Printer tool for WQL queries and the Redgate tool for SQL queries. This will help others understand your queries without having to fix them up first. Hopefully this will also mean that you will get an answer faster too!





Query Formatting Tools