SQL Beautifier

Background

As a Database Administrator get so see a bit of SQL Statements.

Avenues

The SQL gets to the DBA through various avenues, inclusive :-

  1. Developers
    • Submitted to review and execute against Production Database
  2. Monitoring Tools

In quite a few cases, the first thing one has to do is space things out a bit.

 

SQL

Original

Here is our original SQL.


SELECT            dbo.lms_emp_aff_x.aff_netid,            dbo.lms_emp_aff_x.aff_loc,            dbo.lms_emp_aff_x.aff_emp_unique_loc_id,            'A',            @currdate,            ' '        FROM dbo.lms_emp_aff_x        WHERE dbo.lms_emp_aff_x.aff_loc = @loc_passed AND dbo.lms_emp_aff_x.aff_emp_unique_loc_id NOT IN            (              SELECT location_uid.lu_unique_loc_id              FROM location_uid              WHERE location_uid.lu_loc = @loc_passed AND location_uid.lu_record_type = 'A'           )

SQL Beautifiers

Outline

  1. EverSQL
    • EverSQL Query Formatter
      Link
  2. Free Formatter
  3. Poor SQL
  4. Devart
    • SQL Formatter for SQL Server
      Link
  5. Dan’s Tools
    • sql beautifier
      Link

Choices

EverSQL

Revised

Image

eversql.revised.

Code

SELECT
  dbo.lms_emp_aff_x.aff_netid,
  dbo.lms_emp_aff_x.aff_loc,
  dbo.lms_emp_aff_x.aff_emp_unique_loc_id,
  'A',
  @currdate,
  ' '
FROM
  dbo.lms_emp_aff_x
WHERE
  dbo.lms_emp_aff_x.aff_loc = @loc_passed
  AND dbo.lms_emp_aff_x.aff_emp_unique_loc_id NOT IN (
    SELECT
      location_uid.lu_unique_loc_id
    FROM
      location_uid
    WHERE
      location_uid.lu_loc = @loc_passed
      AND location_uid.lu_record_type = 'A'
  )

Free Formatter

Revised
Image

freeFormatter.com.20190202.0850PM

Code

SELECT
   dbo.lms_emp_aff_x.aff_netid,
   dbo.lms_emp_aff_x.aff_loc,
   dbo.lms_emp_aff_x.aff_emp_unique_loc_id,
   'A',
   @currdate,
   ' '
FROM
   dbo.lms_emp_aff_x
WHERE
   dbo.lms_emp_aff_x.aff_loc = @loc_passed
   AND dbo.lms_emp_aff_x.aff_emp_unique_loc_id NOT IN
   (
      SELECT
         location_uid.lu_unique_loc_id
      FROM
         location_uid
      WHERE
         location_uid.lu_loc = @loc_passed
         AND location_uid.lu_record_type = 'A'
   )

Poor SQL

Revised
Image

poorsql.20190131.0808am

Code

SELECT dbo.lms_emp_aff_x.aff_netid
	,dbo.lms_emp_aff_x.aff_loc
	,dbo.lms_emp_aff_x.aff_emp_unique_loc_id
	,'A'
	,@currdate
	,' '
FROM dbo.lms_emp_aff_x
WHERE dbo.lms_emp_aff_x.aff_loc = @loc_passed
	AND dbo.lms_emp_aff_x.aff_emp_unique_loc_id NOT IN (
		SELECT location_uid.lu_unique_loc_id
		FROM location_uid
		WHERE location_uid.lu_loc = @loc_passed
			AND location_uid.lu_record_type = 'A'
		)

Devart / SQL Formatter for SQL Server

Revised
Image

Code

SELECT
  dbo.lms_emp_aff_x.aff_netid,
  dbo.lms_emp_aff_x.aff_loc,
  dbo.lms_emp_aff_x.aff_emp_unique_loc_id,
  'A',
  @currdate,
  ' '
FROM dbo.lms_emp_aff_x
WHERE dbo.lms_emp_aff_x.aff_loc = @loc_passed
AND dbo.lms_emp_aff_x.aff_emp_unique_loc_id NOT IN (SELECT
  location_uid.lu_unique_loc_id
FROM location_uid
WHERE location_uid.lu_loc = @loc_passed
AND location_uid.lu_record_type = 'A')

Dan’s Tools – cleancss

Revised
Image

sqlbeautifier.20190131.0818am

Code
SELECT dbo.lms_emp_aff_x.aff_netid, dbo.lms_emp_aff_x.aff_loc, dbo.lms_emp_aff_x.aff_emp_unique_loc_id, 'A', @currdate, ' '
FROM dbo.lms_emp_aff_x WHERE dbo.lms_emp_aff_x.aff_loc = @loc_passed AND dbo.lms_emp_aff_x.aff_emp_unique_loc_id NOT IN(SELECT location_uid.lu_unique_loc_id FROM location_uid WHERE location_uid.lu_loc = @loc_passed AND location_uid.lu_record_type = 'A')

Summary

 

In our current short list, here are losers and winners.

Losers :-

  1. Dan’s Tool
    • Clean CSS
      • Not SQL focus

Winners :-

  1. EverSQL
    • EverSQL Query Formatter
  2. Free Formatter
    • SQL
  3. Poor SQL

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s