TSQL Formatting tool?

Question

TSQL Formatting tool?

Eric Mamet

SSChampion

Points: 11728
More actions
November 10, 2016 at 10:14 am

#401020

Can someone recommend a (free?) tool to format TSQL Code.
Ideally, it should be something working in both SSMS and Visual Studio (SSDT) as we use both for our TSQL Code.
This would help us with coding standards, debugging, etc.
Thanks

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I've tried Poor Man's T-SQL Formatter[/url] and ApexSQL Refactor which gave me good results to organize messy code, but won't do everything I wanted.

They're good, but you should try them to see if they fit your needs.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

DamianC SSCrazy Eights Points: 8023 More actions · Answer 2

I've used ApexSQL Refactor too

It's pretty good although I don't like some of the formatting produced

That said, it's free and should get you coding in a consistent manner

- Damian

Eric Mamet SSChampion Points: 11728 More actions · Answer 3

Eric Mamet

SSChampion

Points: 11728

November 11, 2016 at 5:27 am

#1911881

Thanks 🙂

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

Eric Mamet (11/10/2016)
Can someone recommend a (free?) tool to format TSQL Code.
Ideally, it should be something working in both SSMS and Visual Studio (SSDT) as we use both for our TSQL Code.
This would help us with coding standards, debugging, etc.
Thanks

A bit ironic, I think. If you have coding standards, why aren't people following them?

--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)

Eric M Russell SSC Guru Points: 125560 More actions · Answer 5

Jeff Moden (11/11/2016)
Eric Mamet (11/10/2016)
Can someone recommend a (free?) tool to format TSQL Code.
Ideally, it should be something working in both SSMS and Visual Studio (SSDT) as we use both for our TSQL Code.
This would help us with coding standards, debugging, etc.
Thanks
A bit ironic, I think. If you have coding standards, why aren't people following them?

Can you tell the difference between artisanal hand-coded SQL that someone slaved over for an hour just to get all the keywords capitalized and the tabs lined up, versus SQL auto-formatted by a tool configured to match your coding standards? 🙂

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

Eric Mamet SSChampion Points: 11728 More actions · Answer 6

Very good question!

The honest answer is a mixture of the followings

1) our coding standards is a document of too many pages (IMHO) so it becomes difficult to follow (I wrote most of it but under certain directives...)

2) I don't think it goes into much details about formatting

3) poor discipline

I can't really do much about 1 and 3 above and solving 2 would make 1 even worse...

But I reckon we can improve quite a bit by adopting a tool for formatting

I am also thinking of using SSMS templates.

Cheers

Eric

Eric M Russell SSC Guru Points: 125560 More actions · Answer 7

Also, you can google "online t-sql formatter" and try out the features of various implementations with downloading and installing anything.

http://poorsql.com/

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

Eric Mamet SSChampion Points: 11728 More actions · Answer 8

Someone at works tells me he has experience of http://www.dpriver.com/pp/sqlformat.htm and likes it.

Since we don't have much time for experimentation, we might give that one a shot first.

I like the fact that there is an add-in for both Visual Studio and SSMS...

Thanks a lot for your help

Much appreciated

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 9

Eric M Russell (11/11/2016)
Jeff Moden (11/11/2016)
Eric Mamet (11/10/2016)
Can someone recommend a (free?) tool to format TSQL Code.
Ideally, it should be something working in both SSMS and Visual Studio (SSDT) as we use both for our TSQL Code.
This would help us with coding standards, debugging, etc.
Thanks
A bit ironic, I think. If you have coding standards, why aren't people following them?
Can you tell the difference between artisanal hand-coded SQL that someone slaved over for an hour just to get all the keywords capitalized and the tabs lined up, versus SQL auto-formatted by a tool configured to match your coding standards? 🙂

Just my humble opinion... If someone has to slave over such a simple thing, they may not have been at it long enough or the standard is bad. If the standard is good, it should come naturally.

I've also found that I can, indeed, frequently tell the difference between someone that used a formatting tool and someone that did not even when both are formatted identically.

--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)

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 10

Jeff Moden (11/11/2016)
Eric M Russell (11/11/2016)
Jeff Moden (11/11/2016)
Eric Mamet (11/10/2016)
Can someone recommend a (free?) tool to format TSQL Code.
Ideally, it should be something working in both SSMS and Visual Studio (SSDT) as we use both for our TSQL Code.
This would help us with coding standards, debugging, etc.
Thanks
A bit ironic, I think. If you have coding standards, why aren't people following them?
Can you tell the difference between artisanal hand-coded SQL that someone slaved over for an hour just to get all the keywords capitalized and the tabs lined up, versus SQL auto-formatted by a tool configured to match your coding standards? 🙂
Just my humble opinion... If someone has to slave over such a simple thing, they may not have been at it long enough or the standard is bad. If the standard is good, it should come naturally.

+1, I find a strong correlation between consistent code formatting and standard compliance, gives a kind of a first impression when reviewing code.

😎

I've also found that I can, indeed, frequently tell the difference between someone that used a formatting tool and someone that did not even when both are formatted identically.

When writing a well formatted code, which is easy with column editing, spaces rather than tabs etc., one has an added opportunity to review and assert the code at the same time, I discover most of my coding errors during the actual writing rather than testing of the code.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 11

Eirikur Eiriksson (11/12/2016)
I've also found that I can, indeed, frequently tell the difference between someone that used a formatting tool and someone that did not even when both are formatted identically.
When writing a well formatted code, which is easy with column editing, spaces rather than tabs etc., one has an added opportunity to review and assert the code at the same time, I discover most of my coding errors during the actual writing rather than testing of the code.

+1 Billion. That's exactly what I'm talking about. I've reviewed volumes of code that contains obvious errors and has no chance of performance but is pretty as hell because someone used a tool to format their junk. The person that does their own formatting may take a bit longer but they also typically produce error free code simply because of what you stated.

I've also found it to be the nature of artisans of code to also test their code whereas people who "just write code" do not. It's an attitude/pride thing that goes a long way to preventing the need for rework.

--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)

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 12

Have used the tool for years. Strange name I will give you that but it works great and has every option you can imagine.

http://www.dpriver.com/pp/sqlformat.htm

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

JustMarie SSCertifiable Points: 7771 More actions · Answer 13

I'm a fan of Notepad++ and the Poor Man's T-SQL Formatter. While I could spend the time to make pretty code I'd much rather take my messy working code and let the formatter do the work. I don't want to spend the time formatting it when there's a free tool that does it for me.

Eric Mamet SSChampion Points: 11728 More actions · Answer 14

Jeffery Williams (11/13/2016)
Have used the tool for years. Strange name I will give you that but it works great and has every option you can imagine.
http://www.dpriver.com/pp/sqlformat.htm

This is the one my colleague recommended and we are trying it