Change formatting of SSMS generated code
-
Hi all,
not exactly the most pressing query ever, but after all these years I've finally cracked.
Is there any way to alter the munged up monstrosity that is the SSMS generated code from the likes of create alter script, select rows etc? Particularly;
1) putting brackets round every object - I don't use stupid names, so that's just plain annoying
2) sticking commas before lines, rather than in the correct place - that's just plain wrongNever having found a way of correcting such abominations myself, I'm not sure it's possible, but if it is, I can't think of a better place to find the person who knows.
cheers
AndrewI'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them. -
There are tons of mostly free online resources for this. Some are better than the others when it comes to complex stuff. Please see the following Google Search.
https://www.google.com/search?q=sql+code+formatter--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) - andrew gothard - Friday, May 18, 2018 4:30 AM
p.s. I, too, used to believe that commas at the beginning of the line were an abomination. I changed my mind several years ago because of all the same reasons that I put AND and OR at the beginning of the lines and the reasons why I use "Alias = Expression" instead of "Expression AS Alias" and more. But, I'm not trying to convince you because I was also unconvincible until I finally saw the utility in it. And, no... the reason that people frequently give (easy to comment out the last line) is a bit lame because trailing commas make it easier to comment out the first line and I do one or the other about the same amount of time.
Most of the good formatters will let you put your commas where you want (I believe the bought and paid for SQL Prompt does these types of things quite well although they don't support the "river" format that I use and so I don't use it).
As for the bloody square brackets, I totally agree. They're as ugly as a yard full of unopened dandelion heads and, in most cases, just about as useful especially because, like you, I don't use whacko names with spaces, dashes, and other atrocities that require it.
The key for me when it comes to how others format stuff is consistency in the basic structure and some closely followed standard for capitalization. All low or upper case is as ugly as a fart sack full of broken antlers. I also have a personal extreme dislike for underscores and usually won't use them myself but won't heap indignities on others that do unless they make the lettering all upper or lower case... like Microsoft frequently does. Most code reformatters won't change that junk. Of course, people who've made the mistake of making their entire server case sensitive love that stuff.
"Make it work, make it fast, make it pretty... and it ain't done 'til it's pretty". 😉
--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) - Jeff Moden - Friday, May 18, 2018 5:40 AM
"River" format? Never heard of it, but I have seen the code you post so not sure what you mean. I feel that your code is well formatted and quite readable. I may not follow it myself, but as you say we just need to be consistent.
Now, about using square brackets. I have found myself actually using them a lot. I don't have control of the names used in the databases I have to support where I work, and I have run into stupid people that have done stupid things in production databases. For instance, an index with a name that included something close to this: <index_name, sysname, ind_test>. It blew up a procedure I wrote to create a training cut of a database from a restored copy of a production database. Because of $4!t like this and the fact that I also tend to write quite a bit of dynamic SQL (this procedure uses dynamic SQL extensively) that I have started putting square brackets around everything. I also make extensive use of sp_executesql.
Because of this, I also setup SQL Prompt to bracket everything. Sometimes you just have to go to the dark side even in T-SQL.
- Lynn Pettis - Friday, May 18, 2018 7:37 AM
Most people have never heard of the "River" format because it's what I call my personal style for formatting code. Basically, there's almost never anything but spaces in character #9. Keywords are right justified on character #8 and everything else is left justified starting at column 10 with the obvious exceptions of nested queries, etc. If you print my code out and look at it at an angle from the foot of the paper, you'll see a clear "river" of nothing but spaces at character 9.
And thank you for your comments on readability.
--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) -
Jeff, you've never steered me wrong in my years of SQL'ing. It'd be an honor to meet you in person some day. In the meantime, I'd LOVE to see a brief article or something just listing all your own personal preferences for formatting, along with some whys and wherefores.
"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura - autoexcrement - Friday, May 18, 2018 11:56 AM
Thank you for the incredibly kind words. What's your hometown? I ask because I'm trying to hit several SQL Saturdays this year. Columbus, Pittsburgh, Indianapolis, and just about anything else within a 5 or 6 hour drive. I'm also going to try to hit two user groups in Chicago and I'm a regular for both Detroit and Lansing, Michigan. Just to try to add another couple of checkmarks to my bucket list, I'm also going to try for Denver this year. None of these are actually easy for me because I have no sponsors and, like many others, I suffer from that no-so-rare disease of Mefundsalow. 😀
As for my personal formatting preferences, you can probably glean them just by looking at the code I post. I post the same way as I write code for work, presentations, and most of the articles I've written. I'd write an article about how and why but similar articles written in the past by others, even though clearly labeled as just personal preferences, have turned into major flame wars.
--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) -
I'm in the San Francisco area, but planning to travel to Michigan next year. Maybe I can catch you at an event!
"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura -
SQL Prompt has some very useful options for handling brackets (remove them unless doing so causes an issue) and aliasing:
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - autoexcrement - Friday, May 18, 2018 11:34 PM
If not, PM me and we'll find a way to meet when you're in Michigan. The chances of me getting to California in the next decade are extremely low.
I am, however, going to submit to present at PASS 2019. If I'm selected to present, which means I don't have to pay to attend (I'd otherwise have to pay for myself and between that, the flight, the hotel, and sustenance, can't afford to do so in these later years), I'll bite the bullet on the other expenses to attend the 2019 PASS Summit.
--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) -
SQL Prompt offers lots of formatting choices, but it's a paid tool.
There are options when scripting in SSMS. If you look under options, in the SQL Server Object Explorer section, there is a scripting section. Limited set of things, but there are a few.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply