x

Sort Lines of Code in Management Studio?

Not an earth-shaking question, but I often want to sort text (sometimes code) in SSMS. Is there an add-in for this anyone knows of? Thanks.
more ▼

asked Jan 07 '11 at 02:09 PM in Default

Larry Leonard gravatar image

Larry Leonard
11 1 1 1

how do your lines of code become unsorted? :)
Jan 07 '11 at 02:11 PM Kev Riley ♦♦

(See, this is why I don't post in forums any more. It's my first day on this forum. You're obviously not a troll, as you've been here for a while. I'll assume you were trying to be funny. I guess I'm just not in the mood; it's also possible that I've lost my sense of humor, or I just don't have the patience for people who try to get attention by being cute, rather than by helping people.)

Here's an example. Say you have two versions of a loooong table definition, which have many columns, indexes, triggers, constraints, etc.

Trying to compare these two in Red Gate's SQL Compare is very time-consuming. If I could sort each file, I could easily compare them side-by-side and spot the differences.

Yes, I could save them both to text files and use Beyond Compare, but even BC can't align files of this complexity enough to make the effort worthwhile. Unless the files were - you guessed it - sorted first.

The long-term solution is to convert this to the "Data Dude" (MS Visual Studio 2008 Database Edition GDR 2) but that's a huge undertaking.
Jan 07 '11 at 02:51 PM Larry Leonard
@Larry, I really hope you just had a bad day today. Most of us on this board like to have fun and yes we can goof off a bit especially on a Friday. I am sure that @Kev Riley was not posting trying to get attention but rather intending that we might need a bit more information in understanding in what manner would you need to sort T-SQL code in SSMS. Your response to @Kev Riley's attempt at some Friday afternoon humor gave us that needed information. I am sure over the weekend or possibly early next week someone else may be able to provide more assistance in how you might be able to accomplish this. Sounds like you have tried just about everything though.
Jan 07 '11 at 04:20 PM Tim
@Larry Leonard - I have to say, my friends here are being a lot less harsh with you than I would. The first part of your comment was unnecessary and bordering on rude. The people who are here regularly are here to help people - out of their own volition and in their free time. The fact that someone asks you for the information that you should have posted in the first place in a humorous way is a good thing. On other Q+A sites your question would have simply been closed.
Jan 08 '11 at 07:09 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

5 answers: sort oldest
If you are talking about formatting your code based on a type style, there are several products out there. SQL PROMPT from RedGate comes to mind first, Idera has something similar, as well as other vendors. I have used SQL Prompt in a trial, can't wait to get the funds from the boss to make the official purchase.
more ▼

answered Jan 07 '11 at 02:21 PM

Tim gravatar image

Tim
35.5k 32 40 138

Actually, SqlInForm.com does the best job of pretty-printing that I can find (which isn't saying much), and SQL Prompt is very useful for a lot of things, but I find its formatting too limited.

No, I actually want to sort the line in alphabetical order.

SQLPrompt is free, isn't it??

Thanks.
Jan 07 '11 at 02:54 PM Larry Leonard
No sir, MSRP for SQLPrompt is $195 for standard or $295 for professional however it is included in several bundle packages. RedGate Pricing
Jan 07 '11 at 04:25 PM Tim
(comments are locked)
10|1200 characters needed characters left
If you want to sort them in alphabetical order, why not just copy and paste into excel, or something like it. Excel would happily sort the lines alphabetically without caring that they were meant to be code or not. You could even have both versions side by side in different columns if that helps.
more ▼

answered Jan 07 '11 at 04:27 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

(comments are locked)
10|1200 characters needed characters left

I think I understand what you mean by sorting code - you want the DDL to be re-factored to conform to a chosen order so that a CREATE TABLE statement would have column names created in alphabetical order? Thus allowing you to spot where one command has different columns from the other ...

I dont know of any products that do this as part of their main purpose (we have a system in house that does this as part of its development process but it is actually an HR system by design). The only way I could think of doing this currently would be to create the object and then generate the create script using syscolumns, sorted in alpha order, etc etc. It would be a heck of a job to roll your own.

Are you aware that you can select blocks of code rather than whole lines? Using Alt+Shift and dragging the mouse over an area in the text editor. This might let you select specific sections of code to reformat them in another application as others have suggested.

Have you tried WinMerge ([http://winmerge.org/][1])? Its a free app that does file and directory comparison very well. Maybe its something you could try out

On a side note. I know Kev well and I am sure he wasn't trying to be smart, I think he was thinking about TSQL in terms of line-numbers etc - as I was when I first read your question. Its only after reading more of the discussion that I fully appreciated what your are asking. Kev didnt have the benefit of that extra content when he saw your question. Of all the forums I have used this is one of the most friendly and approachable, please keep coming back, I am sure you will get a lot from the content we have here.

[Edit] OK, its been a few hours since I added my answer and I have been off and done other things and then come back to SQL ... If you re-factor code to construct objects alphabetically, when they originally were not then you will have a bunch of scripts that don't match your production database. What use are these scripts? Can you explain your methods please as I feel I may be missing something

[1]: http://winmerge.org/
more ▼

answered Jan 08 '11 at 03:20 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 73 77 107

Nice tip on the Alt+Shift.
Jan 08 '11 at 05:51 AM sqlnubi
(comments are locked)
10|1200 characters needed characters left

Larry, first of all, welcome - it's always good to see new people here.

Second, if you think my comment was 'cute for attention' then you are wrong. Your question left a lot to be desired - can SSMS sort text? I could have answered 'No it doesn't' - but that wouldn't have been of any use to anyone. It's now clear that you want to compare code, hence the need for sorting - but we had no idea of that until you commented again.

So anyway on to the question....

I can't really believe you can compare, visually, a long chunk of code faster than Red Gate SQL Compare or any other compare app. I regularly use SQL Compare on databases that have thousands of objects - the DDL for all those would be much more than the largest single table definition you could produce - and sometimes it takes a few minutes, but nothing more. And the time taken is by the scripting not the compare - how time consuming are you finding SQL Compare. You are aware you can limit the objects to compare, as to avoid scripting the whole database?

How does 'Data Dude' help? It's comparison engine will be very similar to SQL Compare.

Finally, how does sorting even help with the compare :

here's 2 example chunks of ddl from a table, sorted alphabetically, quick glance all seems well, but wait the 3rd field down is has a different object name ....

 accountid int                          accountid int
 field1 bit                             field1 bit
 field2 char(1)                         fie1d2 char(1)
 productkey varchar(30)                 productkey varchar(30)
 updatedtime datetime                   updatedtime datetime

Again would you really spot that in a visual check of thousands of lines?

You might not have the patience for people who ask you 'why?', but I think you will find we have a lot of patience here and will ask you lots of questions, so that we can find out what you really want, and ultimately give you an answer that is helpful. Sometimes we do that in a light-hearted way, it brightens the place up a bit.
more ▼

answered Jan 08 '11 at 03:30 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

+1, I love that you all have so much patience.
Jan 08 '11 at 05:45 AM Tim
(comments are locked)
10|1200 characters needed characters left

There is no such functionality in SSMS, (there is something that does exactly this (sort selected lines) in [EditPad Pro][1], which I like to use) but you can view the columns of your table in alphabetical order just by using the Object Explorer Details tab. (hit F7, or use the view -> Object Explorer details menu, and then clicking on the NAME header for the list of columns of the table you want to inspect. As far as I know, you can only have one Object explorer Details running in an instance of SSMS so you'd have to run two instances of SSMS and then compare the two side-by-side.

[1]: http://www.editpadpro.com/
more ▼

answered Jan 09 '11 at 02:06 PM

Phil Factor gravatar image

Phil Factor
3.8k 8 9 16

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x123
x10

asked: Jan 07 '11 at 02:09 PM

Seen: 2122 times

Last Updated: Jan 07 '11 at 02:09 PM