Friday, April 30, 2021

The quest to beautify broken and/or incomplete SQL

If you like me readability of code is extremely important regardless of what language it is in. I was try to find a way to beautify SQL, but ran into a problem. If the SQL is broken, impartial or has incorrect syntax it won't beautify.  The very popular, free and open source  MySQL Workbench is an example. Many online sites fail to produce the correct result as well. Doing a quick Google search, i took the 1st result and got Instant SQL Formatter (dpriver.com) which does not work. Many site are not language aware and format poorly, such as https://www.freeformatter.com/sql-formatter.html, number 2 on results page.

Therefore I built my own tool, called Plain SQL Formatter.  

Below SQL has missing SELECT and misspell  FROM statement

1
schemas.name AS SchemaName, tables.name AS TableName, columns.name AS ColumnName, types.name AS DataTypeName, columns.max_length, columns.precision, columns.scale, columns.is_nullable FRO M sys.tables INNER JOIN sys.columns ON tables.object_id = columns.object_id INNER JOIN sys.types ON types.user_type_id = columns.user_type_id INNER JOIN sys.schemas ON schemas.schema_id = tables.schema_id WHERE tables.is_ms_shipped = 0;

In MySQL Workbench, you cannot beautify because the SQL has errors.
To beautify choose Edit->Format->Beautify Query












SSMS works but is 635Mb, and does it install local sql server as well? 

Or Plain SQL Formatter, is only 10Mb, and works with any SQL. 












You can get Plain SQL Formatter is not built-into myClipboard PlainText PowerTool, and there are 20+ such PowerTools. It's a boon to the developer, you be blown away at the list of 120 plus functions.

Top 7 Functions for Programmer

  1. Remove comments from 50 languages. The most popular in last 10 yrs.
  2. Convert words into double quoted or single quoted array "" array
  3. Pre/post smart append entered text, preserves spacing when adding 
  4. //Mon 11-Jan-21 9:25pm  MetadataConsulting.ca <- a timestamp custom comment signature
  5. Split & Join lines, on enter delimiter ■   (square represents entering input)
  6. Flip terms 'if (a!=b)'->'if (b!=a)' !!! 
  7. Pick a range [-3,5-7,9-] of lines ■ (square represents entering input)

Top 5 Functions for Information Worker

  1. Excelerator a program that check's excel formulas for formula and syntactical mistakes!!!
  2. Escape characters for Excel Formula!
  3. Dedup lines - remove all duplicate lines 
  4. NEW - Diff lines - get different between lines, using last 2 clips
  5. Thousands place format add and remove ie 1000000.00 to 1,000,000.00.00





No comments:

Post a Comment