I know I’m the tool man (with Xtras.Net) but I can’t seem to find a T-SQL code formatter and wondered if anyone else knows about one?
I’m got someone inhouse who writes his SELECT, INSERT, and UPDATE statements in stored procs as if his monitor were infinitely wide, and only ever uses the tab or enter key if forced to do so. His SQL code has no whitespace, and his statements never line up; whenever I have to touch anything he does I have to spend 15 to 30 minutes reformatting his code. He also won’t comment his code telling me "I’m in development on it which is why I don’t format or comment; I’ll clean it up later when its finished" which of course never happens. I’m pretty much a standard’s zealot yet no matter what I do, I can’t get him to change. I feel like the pious Baptist minister whose teenage daughter drinks, smokes, listens to rock music, and is having sex with the entire football team!
We just had a big blow up for the nth time about it and his response to me was "Then adopt my style, I like it better, I can’t read your code when you format it." (If you want to see how I format T-SQL, you can read some of read my prior posts.) Jeesh I’m frustrated. Do any of you have to deal with this situation? Is it me? Any thoughts about how to get him to feel like there is value to working with standards everyone on a team can agree on?
Does anyone know of a T-SQL code formatting tool and standards management tool I can run on an entire SQL Server database?
There’s a number of tools out there that can handle reformatting SQL but many require purchase.
One handy free online Java-based tool that I have come across is at http://www.sqlformatter.de/
You can use it to do what you want with little trouble.
Thanks. I don’t mind purchase at all, but don’t want to but a full toolset when one screwdriver will do! I’m actually most interested in something that I could point at my SQL Server database and have it run the formatting on it. Of course it would need to be flawless in execution. :)
Not sure about the formatting one, but for "standards management" there is of course the SQL Server Best Practice Analyser as a free download from MS…
Without trying to sound like a jerk, I’d have to say, he works for you (he does, right?) and it is part of his job to format the T-SQL code in the way that you and your company have adopted. It’s not up to him to decide how he is going to work and tell you to adjust… Of course, this doesn’t help with the existing code so the need for a tool is still there, but it would stop the insanity!
>> Without trying to sound like a jerk, I’d have to say, he works for you (he does, right?) and it is part of his job to format the T-SQL code in the way that you and your company have adopted. It’s not up to him to decide how he is going to work and tell you to adjust… Of course, this doesn’t help with the existing code so the need for a tool is still there, but it would stop the insanity!
I agree completely. But after working with him for 5+ years, I can’t get him to change. I can either fire him or put up with it. But I don’t want to fire him because otherwise he’s a great employee.
Actually, it does make me feel totally inadequate as a manager… There, I just bared one of my failings to the world! :-(
The response to his statement "I’m in development on it which is why I don’t format or comment; I’ll clean it up later when its finished" should probably be "If you don’t have time to do it right, when will you have time to do it over?"
And when it comes to formatting and comments, which are intended to assist others in working with your code, skipping those steps is doing it wrong. Comment the code as you write it (and remember to explain what the code is *supposed to do* so even if we know what it’s doing, we can understand if what it’s actually doing is *correct*).
Always write your code so that it’s easily understandable and maintainable by others. Obscure code might buy you some small measure of job security, but it’ll hold you back as well. You can’t be promoted if you’re the only person who can understand a mission-critical application. And why would they promote you when the code you write is so hard to maintain anyway?
His quote "Then adopt my style, I like it better, I can’t read your code when you format it." confuses me to the point where I can’t think of a direct response to it. How can someone actually *like* unformatted code? How can they read it when they refer back to it? How can they seriously expect others to prefer it? And what could possibly make unformatted code easier for him to understand than formatted code?
Of course, I’m likely biased, as I was converted over to zealotry during my time working for Mike at Xtras :)
Hi Eric! You of course are preaching back to the choir that indoctrinated you way back when! Hope you are doing well!
BTW, you know who I’m talking about, though I won’t name him. Of course, you couldn’t get him to provide you the data in the format you needed it from his object on that one project, could you? So I’m sure you know how I feel. :(
Here’s another SQL formatter I just ran across: http://software.topcoder.com/catalog/c_component.jsp?comp=10029208&ver=1
It’s a Java component that could be easily embedded in a little custom app for your purposes.
Hey,
Can you gess how do I figure out whether my boss has messed my stored procedures or not? Maybe, she is your employee’s sister.
Good one Luciano! :-)
This is the best formatter
http://orafaq.com/utilities/sqlformatter.htm
Have you checked red-gate’s SQL Refactor:
http://redgate.com/products/SQL_Refactor/index.htm
You can check: http://www.ubitsoft.com/products/sqlenlight/
Try out http://www.tsqltidy.com (It uses standard Microsoft objects for most of the formatting)
Here is my command line T-Sql Formatter (not free): http://www.equinoxbase.com/ttsf. Unlike many others, actually it formats T-Sql , not just Sql.
Hi @Marton,
Thanks for the comment. Unfortunately (for me) you are about 5 years too late. I haven’t used TSQL since 2005; MySQL all the way for me now…
-Mike
Right…
this is the good one
http://sqlserverlearner.com/online-tools/tsql-code-formatter
does good sql formatting
You could also try my PowerShell script based formatter http://blogs.msdn.com/b/arvindsh/archive/2013/02/28/t-sql-formatter-a-powershell-script.aspx
Free online SQL formatter http://www.sql-format.com/ powered by Devart’s SQL Complete , my favorite thing about it is that it allows loading code directly from your computer, and that means no more boring copy-paste.
Another good online tsql code formatter and beautifier:
http://sqlserverlearner.com/online-tools/tsql-code-formatter