asked
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.
@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.
@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.
(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.
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.
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.
No sir, MSRP for SQLPrompt is $195 for standard or $295 for professional however it is included in several bundle packages. [RedGate Pricing](
http://www.red-gate.com/purchase/)
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.
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/
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.
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/
