question

R Glen Cooper avatar image
R Glen Cooper asked

How can I reformat stored procs?

I have to modify some lengthy stored procs in a proprietary database, where all carriage returns and comments have been deleted.

Right now I'm manually re-entering the carriage returns in order to read the code, but this is very tedious.

Are there any tools to do this?

Thanks in advance.

stored-proceduresformatting
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

REdgate tools are generally a very good way to go.
0 Likes 0 ·
I am a fan of SQL Refactor and SQL Prompt also has some reformatting capabilities built in. If you get the SQL Toolbelt, it has both and some others as well.
0 Likes 0 ·
SQL Refactor gets my vote with SQL Prompt a close 2nd
0 Likes 0 ·
kefealo avatar image
kefealo answered

Hello,

You have to create your own style based upon the written and unwritten rules. I tested the red gate utility and others as well, but your hand, with your eyes do the code formatting to crystal clear.

I don't know what is your problem... you inherited this unstructured megamix? what do you want to do?

brush

2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Even typing yourself, when you start to get into large queries, with nested derived tables, cte's and all the rest, having a method for automatically laying out the code in a way that's appealing comes in very handy.
1 Like 1 ·
having a tool thats on all developers computers means standard formatting for everyone in a team. This leads to better support of others code
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered

I too absolutely love the Red Gate tools. However, even with their modest cost, you might not be able to afford them. Instead, try the Simple-Talk Code Prettifier.

3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I also like the Code Prettifier, but it involves more steps and has fewer customization options than SQL Refactor or SQL Prompt. It is a good option if you really don't want to buy the software though.
0 Likes 0 ·
Oh yeah, I much prefer SQL Prompt or SQL Refactor, but people need options.
0 Likes 0 ·
for a freebie its the best I know of but the fact that its disconnected from SSMS is a big slow down in the prettification process
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered

For what it's worth, there's a T-SQL parser set for compatibility levels 80, 90 and 100 that comes with the GDR release for VSTS Team Suite Database Nerd Edition. I can never remember exactly what it's called!

That's what my sql editor uses under the hood. It doesn't preserve comments, which is highly annoying (thanks for that Microsoft), but seeing as you don't have any comments, it's probably worth a look.

Another tool that includes formatting is Apex SQL Edit. There is also a free on-line prettifier here.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

And to add to the list, there is also ApexSQL Refactor

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yeah, I forgot about that - +1
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
There's also the SQL Pretty Printer, from [ http://www.dpriver.com][1] - there's a free trial / nagware version that sits inside SSMS. Not sure how well it integrates with older versions of SQL, though, and it has given me the odd flaky moment, usually when there's some bad code to deal with. But there's always CTRL+Z to get back to where you were, and there is an option to only reformat the selected code. [1]: http://www.dpriver.com
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.