Formatting SQL

Question

Formatting SQL

richardmgreen1

SSChampion

Points: 10356
More actions
January 29, 2016 at 7:32 am

#323984

Hi all
This will hopefully be a quick question and not generate too much debate.....
We're trying to standardise the formatting of our SQL code (we all have slightly different views on how statements should look) and we're looking for a SQL formatter (preferably free).
Can anyone recommend one please?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125562 More actions · Answer 1

First, when it comes to agreeing to a standard, I suggest that you not argue over minor details and instead come to an agreement about functional issues that matter (like always specifying column list in INSERT statements and usage of query hints) and then focus on structural issues like line breaks, tabs, and capitalization.

I've used Poor Man's T-SQL Formatter, which provides a plugin for SSMS, VS, and Notepad++ as well as an API and web service.

http://architectshack.com/PoorMansTSqlFormatter.ashx#Download_5

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

http://www.apexsql.com/sql_tools_refactor.aspx

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 3

J Livingston SQL (1/29/2016)
http://www.apexsql.com/sql_tools_refactor.aspx

Heh... just select the "obfuscation" method and you're done. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

richardmgreen1 SSChampion Points: 10356 More actions · Answer 4

Thanks folks.

We've (just about) agreed on the functional issues.

Now we're looking at the formatting to make everything look identical and make it easy to follow.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

Jeff Moden (1/29/2016)
J Livingston SQL (1/29/2016)
http://www.apexsql.com/sql_tools_refactor.aspx
Heh... just select the "obfuscation" method and you're done. 😛

hehe....had never used it, til now.........cant see the point

original

CREATE TABLE mytable(

TranID INTEGER NOT NULL PRIMARY KEY

,CustomerID INTEGER NOT NULL

,ProdID INTEGER NOT NULL

,SalesAmount NUMERIC(5,2) NOT NULL

,TransDate VARCHAR(23) NOT NULL

);

INSERT INTO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) VALUES (142801,19246,7727,57.42,'2009-02-02 00:00:00.000');

INSERT INTO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) VALUES (142802,27567,2780,8.96,'2009-08-31 00:00:00.000');

INSERT INTO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) VALUES (142803,88421,108,62.56,'2009-10-24 00:00:00.000');

INSERT INTO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) VALUES (142804,51319,854,44.56,'2009-05-03 00:00:00.000');

INSERT INTO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) VALUES (142805,46058,4565,98.86,'2009-05-22 00:00:00.000');

INSERT INTO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) VALUES (142806,21038,3415,47.85,'2009-02-18 00:00:00.000');

INSERT INTO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) VALUES (142807,85308,8638,38.19,'2009-07-18 00:00:00.000');

INSERT INTO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) VALUES (142809,84189,3323,67.61,'2010-07-29 00:00:00.000');

INSERT INTO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) VALUES (142810,47743,1977,63.06,'2009-03-08 00:00:00.000');

INSERT INTO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) VALUES (142811,69054,6569,22.82,'2010-03-08 00:00:00.000');

Obfuscate ??

CReAte tABle mytable(TranID INTEGER nOt nUll primAry KEy,CustomerID INTEGER nOt

null,ProdID INTEGER NoT NulL,SalesAmount NUMERIC(5,2) not nUlL,TransDate

VARCHAR(23) not NULl); iNsErT inTO mytable(TranID,CustomerID,ProdID,SalesAmount,

TransDate) VALuES(142801,19246,7727,57.42,'2009-02-02 00:00:00.000'); inSERt

intO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) ValUeS(142802,

27567,2780,8.96,'2009-08-31 00:00:00.000'); iNSErt InTo mytable(TranID,

CustomerID,ProdID,SalesAmount,TransDate) vALueS(142803,88421,108,62.56,

'2009-10-24 00:00:00.000'); INsERt INto mytable(TranID,CustomerID,ProdID,

SalesAmount,TransDate) VAluES(142804,51319,854,44.56,'2009-05-03 00:00:00.000')

; iNsErt iNtO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) VAlUeS(

142805,46058,4565,98.86,'2009-05-22 00:00:00.000'); iNsERt INto mytable(TranID,

CustomerID,ProdID,SalesAmount,TransDate) vALUES(142806,21038,3415,47.85,

'2009-02-18 00:00:00.000'); insERT iNto mytable(TranID,CustomerID,ProdID,

SalesAmount,TransDate) valUEs(142807,85308,8638,38.19,'2009-07-18 00:00:00.000')

; iNSeRt iNto mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) vaLUeS(

142809,84189,3323,67.61,'2010-07-29 00:00:00.000'); InsERT inTO mytable(TranID,

CustomerID,ProdID,SalesAmount,TransDate) vAlUEs(142810,47743,1977,63.06,

'2009-03-08 00:00:00.000'); INSerT INto mytable(TranID,CustomerID,ProdID,

SalesAmount,TransDate) valuES(142811,69054,6569,22.82,'2010-03-08 00:00:00.000')

;

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

J Livingston SQL (1/29/2016)

Jeff Moden (1/29/2016)
Obfuscate ??
CReAte tABle mytable(TranID INTEGER nOt nUll primAry KEy,CustomerID INTEGER nOt
null,ProdID INTEGER NoT NulL,SalesAmount NUMERIC(5,2) not nUlL,TransDate
VARCHAR(23) not NULl); iNsErT inTO mytable(TranID,CustomerID,ProdID,SalesAmount,
TransDate) VALuES(142801,19246,7727,57.42,'2009-02-02 00:00:00.000'); inSERt
intO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) ValUeS(142802,
27567,2780,8.96,'2009-08-31 00:00:00.000'); iNSErt InTo mytable(TranID,
CustomerID,ProdID,SalesAmount,TransDate) vALueS(142803,88421,108,62.56,
'2009-10-24 00:00:00.000'); INsERt INto mytable(TranID,CustomerID,ProdID,
SalesAmount,TransDate) VAluES(142804,51319,854,44.56,'2009-05-03 00:00:00.000')
; iNsErt iNtO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) VAlUeS(
142805,46058,4565,98.86,'2009-05-22 00:00:00.000'); iNsERt INto mytable(TranID,
CustomerID,ProdID,SalesAmount,TransDate) vALUES(142806,21038,3415,47.85,
'2009-02-18 00:00:00.000'); insERT iNto mytable(TranID,CustomerID,ProdID,
SalesAmount,TransDate) valUEs(142807,85308,8638,38.19,'2009-07-18 00:00:00.000')
; iNSeRt iNto mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) vaLUeS(
142809,84189,3323,67.61,'2010-07-29 00:00:00.000'); InsERT inTO mytable(TranID,
CustomerID,ProdID,SalesAmount,TransDate) vAlUEs(142810,47743,1977,63.06,
'2009-03-08 00:00:00.000'); INSerT INto mytable(TranID,CustomerID,ProdID,
SalesAmount,TransDate) valuES(142811,69054,6569,22.82,'2010-03-08 00:00:00.000')
;

I would have named the menu item "Convert to Ransom Note" 😛

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

To the original question, redgate SQL Prompt is worth every penny IMO. I've been using it since the original beta version and rely on it heavily. So heavily in fact that several years ago I was told it would take months to get a license through the purchasing process at a company I had just joined so I bought a license with my own money for my work machine.

Note that I am not taking anything away from other tools. If I was just starting to look for a code formatter (SQL Prompt does a lot more than formatting by the way) one of the other tools would compare very favorably but for my money it's SQL Prompt.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

http://poorsql.com/[/url] is another free tool that works pretty well. You can use the website or they have an SSMS plugin you can use. Oddly until today i never noticed the obfuscate "feature". To me the obfuscation looks more like the sql that so many people as their attempt on the forums. Just sloppy and makes my eyes hurt.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 9

http://www.ssmstoolspack.com/

not free for 2012 and above, but does include a lot of functionality beyond refactoring.......not overly expensive and has a free 60 day / 1pc trial

maybe worth a look

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 10

J Livingston SQL (1/29/2016)
Jeff Moden (1/29/2016)
J Livingston SQL (1/29/2016)
http://www.apexsql.com/sql_tools_refactor.aspx
Heh... just select the "obfuscation" method and you're done. 😛
hehe....had never used it, til now.........cant see the point

I use it to show what a lot of people write code like.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

Jeff Moden (1/29/2016)
J Livingston SQL (1/29/2016)
Jeff Moden (1/29/2016)
J Livingston SQL (1/29/2016)
http://www.apexsql.com/sql_tools_refactor.aspx
Heh... just select the "obfuscation" method and you're done. 😛
hehe....had never used it, til now.........cant see the point
I use it to show what a lot of people write code like.

It is close but it needs to throw in some random extra characters that make no sense at all in the middle to make it nearly impossible to debug. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eric M Russell SSC Guru Points: 125562 More actions · Answer 12

J Livingston SQL (1/29/2016)
Jeff Moden (1/29/2016)
J Livingston SQL (1/29/2016)
http://www.apexsql.com/sql_tools_refactor.aspx
Heh... just select the "obfuscation" method and you're done. 😛
hehe....had never used it, til now.........cant see the point
...
...
CReAte tABle mytable(TranID INTEGER nOt nUll primAry KEy,CustomerID INTEGER nOt
null,ProdID INTEGER NoT NulL,SalesAmount NUMERIC(5,2) not nUlL,TransDate
VARCHAR(23) not NULl); iNsErT inTO mytable(TranID,CustomerID,ProdID,SalesAmount,
TransDate) VALuES(142801,19246,7727,57.42,'2009-02-02 00:00:00.000'); inSERt
intO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) ValUeS(142802,
27567,2780,8.96,'2009-08-31 00:00:00.000'); iNSErt InTo mytable(TranID,
CustomerID,ProdID,SalesAmount,TransDate) vALueS(142803,88421,108,62.56,
'2009-10-24 00:00:00.000'); INsERt INto mytable(TranID,CustomerID,ProdID,
SalesAmount,TransDate) VAluES(142804,51319,854,44.56,'2009-05-03 00:00:00.000')
; iNsErt iNtO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) VAlUeS(
142805,46058,4565,98.86,'2009-05-22 00:00:00.000'); iNsERt INto mytable(TranID,
CustomerID,ProdID,SalesAmount,TransDate) vALUES(142806,21038,3415,47.85,
'2009-02-18 00:00:00.000'); insERT iNto mytable(TranID,CustomerID,ProdID,
SalesAmount,TransDate) valUEs(142807,85308,8638,38.19,'2009-07-18 00:00:00.000')
; iNSeRt iNto mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) vaLUeS(
142809,84189,3323,67.61,'2010-07-29 00:00:00.000'); InsERT inTO mytable(TranID,
CustomerID,ProdID,SalesAmount,TransDate) vAlUEs(142810,47743,1977,63.06,
'2009-03-08 00:00:00.000'); INSerT INto mytable(TranID,CustomerID,ProdID,
SalesAmount,TransDate) valuES(142811,69054,6569,22.82,'2010-03-08 00:00:00.000')
;

It looks like T-SQL compression. I guess it's useful when you're reduce the size of your query cache, or squeeze as much code as possible into an 8k remote execution call. :hehe:

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Eric M Russell SSC Guru Points: 125562 More actions · Answer 13

J Livingston SQL (1/29/2016)
Jeff Moden (1/29/2016)
J Livingston SQL (1/29/2016)
http://www.apexsql.com/sql_tools_refactor.aspx
Heh... just select the "obfuscation" method and you're done. 😛
hehe....had never used it, til now.........cant see the point
...
...
CReAte tABle mytable(TranID INTEGER nOt nUll primAry KEy,CustomerID INTEGER nOt
null,ProdID INTEGER NoT NulL,SalesAmount NUMERIC(5,2) not nUlL,TransDate
VARCHAR(23) not NULl); iNsErT inTO mytable(TranID,CustomerID,ProdID,SalesAmount,
TransDate) VALuES(142801,19246,7727,57.42,'2009-02-02 00:00:00.000'); inSERt
intO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) ValUeS(142802,
27567,2780,8.96,'2009-08-31 00:00:00.000'); iNSErt InTo mytable(TranID,
CustomerID,ProdID,SalesAmount,TransDate) vALueS(142803,88421,108,62.56,
'2009-10-24 00:00:00.000'); INsERt INto mytable(TranID,CustomerID,ProdID,
SalesAmount,TransDate) VAluES(142804,51319,854,44.56,'2009-05-03 00:00:00.000')
; iNsErt iNtO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) VAlUeS(
142805,46058,4565,98.86,'2009-05-22 00:00:00.000'); iNsERt INto mytable(TranID,
CustomerID,ProdID,SalesAmount,TransDate) vALUES(142806,21038,3415,47.85,
'2009-02-18 00:00:00.000'); insERT iNto mytable(TranID,CustomerID,ProdID,
SalesAmount,TransDate) valUEs(142807,85308,8638,38.19,'2009-07-18 00:00:00.000')
; iNSeRt iNto mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) vaLUeS(
142809,84189,3323,67.61,'2010-07-29 00:00:00.000'); InsERT inTO mytable(TranID,
CustomerID,ProdID,SalesAmount,TransDate) vAlUEs(142810,47743,1977,63.06,
'2009-03-08 00:00:00.000'); INSerT INto mytable(TranID,CustomerID,ProdID,
SalesAmount,TransDate) valuES(142811,69054,6569,22.82,'2010-03-08 00:00:00.000')
;

It looks like T-SQL compression. I guess it's useful when you're trying to reduce the size of your query cache, or squeeze as much code as possible into an 8k remote execution call. :hehe:

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 14

Eric M Russell (1/29/2016)
J Livingston SQL (1/29/2016)
Jeff Moden (1/29/2016)
J Livingston SQL (1/29/2016)
http://www.apexsql.com/sql_tools_refactor.aspx
Heh... just select the "obfuscation" method and you're done. 😛
hehe....had never used it, til now.........cant see the point
...
...
CReAte tABle mytable(TranID INTEGER nOt nUll primAry KEy,CustomerID INTEGER nOt
null,ProdID INTEGER NoT NulL,SalesAmount NUMERIC(5,2) not nUlL,TransDate
VARCHAR(23) not NULl); iNsErT inTO mytable(TranID,CustomerID,ProdID,SalesAmount,
TransDate) VALuES(142801,19246,7727,57.42,'2009-02-02 00:00:00.000'); inSERt
intO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) ValUeS(142802,
27567,2780,8.96,'2009-08-31 00:00:00.000'); iNSErt InTo mytable(TranID,
CustomerID,ProdID,SalesAmount,TransDate) vALueS(142803,88421,108,62.56,
'2009-10-24 00:00:00.000'); INsERt INto mytable(TranID,CustomerID,ProdID,
SalesAmount,TransDate) VAluES(142804,51319,854,44.56,'2009-05-03 00:00:00.000')
; iNsErt iNtO mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) VAlUeS(
142805,46058,4565,98.86,'2009-05-22 00:00:00.000'); iNsERt INto mytable(TranID,
CustomerID,ProdID,SalesAmount,TransDate) vALUES(142806,21038,3415,47.85,
'2009-02-18 00:00:00.000'); insERT iNto mytable(TranID,CustomerID,ProdID,
SalesAmount,TransDate) valUEs(142807,85308,8638,38.19,'2009-07-18 00:00:00.000')
; iNSeRt iNto mytable(TranID,CustomerID,ProdID,SalesAmount,TransDate) vaLUeS(
142809,84189,3323,67.61,'2010-07-29 00:00:00.000'); InsERT inTO mytable(TranID,
CustomerID,ProdID,SalesAmount,TransDate) vAlUEs(142810,47743,1977,63.06,
'2009-03-08 00:00:00.000'); INSerT INto mytable(TranID,CustomerID,ProdID,
SalesAmount,TransDate) valuES(142811,69054,6569,22.82,'2010-03-08 00:00:00.000')
;
It looks like T-SQL compression. I guess it's useful when you're trying to reduce the size of your query cache, or squeeze as much code as possible into an 8k remote execution call. :hehe:

OMG, I'm using this from now on. I will become the most hated data guy on the planet. This will become my final form. Thanks SQLServerCentral.com!