Last updated on October 31st, 2022 at 09:19 am
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! In this article, we will take about a few of my favorite Query Formatting Tools.
For example, take this unformatted WQL query. What is it doing?
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.
Query Formatting Tools
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)
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!
WQL/SQL
https://www.dpriver.com/pp/sqlformat.htm
SQL
Don’t forget that you can subscribe to my RRS feed to stay on top of the latest trips and tricks. If you have any questions, please feel free to touch base @Garthmj.