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.
asked Jan 07, 2011 at 02:09 PM in Default
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.
answered Jan 07, 2011 at 02:21 PM
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 ....
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.
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.
answered Jan 07, 2011 at 04:27 PM
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/)? 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
There is no such functionality in SSMS, (there is something that does exactly this (sort selected lines) in EditPad Pro, 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.